レッスン 5 : ユーザー定義関数に渡すレポート パラメータの追加

更新 : 2006 年 12 月 12 日

レポート パラメータを入力パラメータにバインドして、データセット クエリ内のユーザー定義関数 (ufn) やストアド プロシージャに渡すことができます。ユーザー定義関数は、テーブルまたはスカラ値 (単一の結果) を返すことができます。データセット クエリは、定義によると、ユーザー定義関数またはストアド プロシージャから単一の結果を取得するものです。ストアド プロシージャと比較した場合のユーザー定義関数の長所は、Transact-SQL SELECT ステートメント内に直接記述できる点です。したがって、クエリ結果セット内で、関数の結果と他の列を組み合わせることができます。

ユーザー定義関数にもストアド プロシージャにも、複数値をとるパラメータ (配列) を直接指定することはできません。複数値の配列をストアド プロシージャに渡すには、Visual Basic の JOIN 関数を使用して、すべての値を連結した文字列を作成します。さらに、複数の選択肢が任意の区切り文字で区切られた文字列パラメータを受け入れるユーザー定義関数を記述します。

ユーザー定義関数 ufnGetContactInformation は、Person.Contact テーブルのデータベース識別子を受け取り、ContactID、Name、Phone、EmailAddress、JobTitle、ContactType の値を返します。このレポートでは、定義済みのフィールド サブセットの中から、表示するフィールドをユーザーが選択できるようにするためのパラメータを作成します。

このレッスンでは、"Contact Information" という新しいレポートを作成し、再販業者の連絡先担当者を表すデータベース識別子を受け入れるパラメータを設定します。また、ユーザー定義関数 ufnGetContactInformation を AdventureWorks データベースから呼び出すクエリを作成し、このユーザー定義関数の入力パラメータ @ContactID にレポート パラメータをバインドします。

さらに、このレポートを Resellers Worldwide レポートのサブレポートとして使用できるように、背景色とテキスト色を設定するパラメータを作成し、レポートの前後の空白を削除し、余白やレポート幅を設定します。このレポートには、複数値をとるパラメータを作成して、メイン レポート側から連絡先情報として表示するフィールドを指定できるようにします。

Advanced Parameters Tutorial レポート サーバー プロジェクトを開くには

1. SQL Server Business Intelligence Development Studio で、Advanced Parameters Tutorial レポート サーバー プロジェクトをまだ開いてなければ開きます。

ユーザー定義関数から新しいレポートを作成するには

1. ソリューション エクスプローラで、[レポート] フォルダを右クリックし、[追加]、[新しい項目] の順にクリックします。[新しい項目の追加] ダイアログ ボックスが表示されます。

2. [テンプレート] ペインで [レポート] を選択します。

3. [ファイル名] ボックスに「Contact Information.rdl」と入力します。

4. [追加] をクリックします。レポートが [データ] ビューに表示されます。

5. [データセット] ボックスの一覧で、[<新しいデータセット>] を選択します。[データセット] ダイアログ ボックスが表示されます。

6. [クエリ] タブで、[名前] ボックスに「ContactInformation」と入力します。

7. [データソース] が [Resellers] になっていることを確認します。

8. [コマンドの種類] が [Text] になっていることを確認します。

9. [クエリ文字列] ボックスに、次のクエリを貼り付けます。

   SELECT udf.ContactID, udf.FirstName + N' ' + udf.LastName AS Name,
      c.Phone, c.EmailAddress, udf.JobTitle, udf.ContactType
      FROM ufnGetContactInformation(@ContactID) udf
      JOIN Person.Contact c ON udf.ContactID = c.ContactID
   

   このクエリでは、@ContactID というパラメータを使用して、データベース識別子をユーザー定義関数 ufnGetContactInformation に渡します。このユーザー定義関数は、入力パラメータとして ContactID を受け取ります。

10. ツール バーの [実行] ([!]) をクリックします。[クエリ パラメータの定義] ダイアログ ボックスが表示されます。

11. [パラメータ値] 列で、@ContactID の値として「1」を入力します。結果セットには、ContactID で表される店舗担当者の連絡先情報が表示されます。結果セットで返される列は、ユーザー定義関数で定義されています。

12. (省略可) クエリ パラメータ @ContactID がレポート パラメータ ContactID にバインドされていることを確認します。そのためには、レポート ツール バーの [選択したデータセットの編集] ([…]) をクリックし、[パラメータ] タブをクリックします。パラメータ @ContactID の値は、=Parameters!ContactID.Value に設定されています。

次の手順では、レポート パラメータ ContactID の既定値を作成します。すべてのパラメータに既定値を指定しておくと、[プレビュー] をクリックしたとき、レポートが自動的に実行されるようになります。

ContactID の既定値を追加するには

1. [レポート] メニューの [レポート パラメータ] をクリックします。[レポート パラメータ] ダイアログ ボックスが開きます。このダイアログ ボックスの [パラメータ] ペインでは ContactID が選択されています。

2. [プロパティ] セクションの [既定値] で [クエリなし] を選択し、「1」と入力します。

3. [OK] をクリックします。

次の手順では、複数値をとる文字列パラメータを作成し、表示する連絡先情報を選択できるようにします。パラメータの値は Phone、Email、None として、クエリなしの可能な値の一覧を作成します。

レポート パラメータ FieldsToDisplay を作成するには

1. [レイアウト] ビューで、[レポート] メニューの [レポート パラメータ] をクリックします。[レポート パラメータ] ダイアログ ボックスが表示されます。

2. [追加] をクリックします。新しいパラメータが既定値で生成されます。

3. [プロパティ] セクションの [名前] ボックスに「FieldsToDisplay」と入力し、データ型が String であることを確認します。

4. [表示名] に「表示する連絡先情報を選択してください : 」と入力します。

5. [複数値] チェック ボックスをオンにします。

6. [空白の値を許可] チェック ボックスがオンになっていることを確認します。

7. [使用できる値] セクションで [クエリなし] を選択し、次の表を参照して値を入力します。

パラメータに選択可能な値を用意する場合、[空白の値を許可] オプションを選択するには、可能な値として <空白> を含める必要があります。

次の手順では、このレポートのレイアウトを作成します。このレポートはメイン レポート内のサブレポートとして使用されるので、簡潔なレイアウトにします。1 つのテキスト ボックスに連絡先担当者名と役職名を表示し、もう 1 つのテキスト ボックスに、ユーザーの選択に応じて電子メール アドレスと電話番号を表示します。

この情報のレポート レイアウトを作成するには

1. [レイアウト] タブをクリックします。レポートが [レイアウト] ビューに表示されます。

2. ツールボックスからデザイン画面へ [テーブル] レポート アイテムをドラッグします。

3. 列見出しを右クリックし、[列の削除] をクリックします。これで 2 つの列が残ります。

4. 詳細行の 1 つ目のテキスト ボックスに次の式を貼り付けます。

   =Fields!Name.Value & vbCrLf & "[ " & Fields!JobTitle.Value & " ]"
   

5. 詳細行の 2 つ目のテキスト ボックスに次の式を貼り付けます。

   =IIF((Parameters!FieldsToDisplay.Count=1) AND 
      (InStr("None",Parameters!FieldsToDisplay.Label(
             Parameters!FieldsToDisplay.Count-1))>0),"",
       IIF(InStr(Join(Parameters!FieldsToDisplay.Value,","),
          "EmailAddress")>0,Fields!EmailAddress.Value,"") + 
   vbCrLf + IIF(InStr(Join(Parameters!FieldsToDisplay.Value,","),
          "Phone")>0,Fields!Phone.Value,""))
   

   これは単に、複数値パラメータ FieldsToDisplay に対して選択された値を調べるための式です。パラメータにラベル None だけが選択された場合、この式は空白として評価されます。値に EmailAdress が含まれている場合は式の一部が "EmailAddress" と評価され、同様に Phone が含まれている場合は式の一部が "Phone" と評価されます。この 2 つの値がキャリッジ リターンをはさんで連結され、式全体を評価した結果が 2 つ目のボックスの内容として表示されます。

   この例では、次の処理がポイントになります。

   • 複数値パラメータに選択された値の個数を求める : Parameters!FieldsToDisplay.Count
   • 複数値パラメータで、配列中の最後のラベルが特定の文字列 (この場合は "None") であるかどうかを確認する : InStr("None",Parameters!FieldsToDisplay.Label(Parameters!FieldsToDisplay.Count-1))>0
   • 複数値パラメータに値が含まれているかどうかという条件に応じて文字列を返す (複数値パラメータ FieldsToDisplay に選択されたすべての値の連結文字列を評価、EmailAddress が含まれていればデータセットから EmailAddress の特定値を返し、含まれていなければ空白を返す) : IIF(InStr(Join(Parameters!FieldsToDisplay.Value,","),"EmailAddress")>0,Fields!EmailAddress.Value,"")

   メモ :
   Visual Basic の関数 IIF では渡された関数パラメータ値がすべて評価されるので、値が Null になる可能性のあるデータセット フィールドをこの式で使用することはできません。

6. (省略可) [プレビュー] をクリックし、2 つのパラメータの値を変更して結果を確認します。

次の手順では、メイン レポート内に表示されるサブレポートとしての体裁を整えるため、ヘッダーやフッターを非表示にし、テーブル幅の設定と余白の削除を行い、フォントや背景色をメイン レポートに合わせて設定します。また、パラメータを追加し、背景色やフォント色をメイン レポートからパラメータとして渡せるようにします。

余白を削除し、レポート サイズを設定するには

1. テーブル内をクリックしてテーブル ハンドルを表示します。

2. 詳細行のハンドルを右クリックし、ショートカット メニューの [テーブル ヘッダー] をクリックして非表示にします。[テーブル フッター] も同様に設定します。これで、テーブルが詳細行だけになりました。

   次の 3 つの手順では、レポートの幅を所定のサイズに調整します。このサイズは、次のレッスンでサブレポート レポート アイテムをメイン レポートに追加するときに必要になります。

3. テーブルを選択します。[プロパティ] ウィンドウで、テーブル幅が [3] であることを確認します。この値を調整して、連絡先情報を表示できる最小の幅にします。

   サブレポートに定義したテーブル幅によって、メイン レポート内でサブレポートがどのように表示されるかが決まります。サブレポートを固定のサイズに設定するには、高さと幅を必要な値に設定します。テキスト ボックスの CanGrow プロパティと CanShrink プロパティを設定し、内容に応じて高さが伸縮するようにすることもできます。CanGrow と CanShrink は幅には適用されません。現時点では、内容に応じてテキスト ボックスの幅を自動調整することはできません。

4. 1 つ目の列ヘッダー テキスト ボックスを選択します。[プロパティ] ウィンドウの [Width] までスクロールして、「1.5」と入力します。または、既定の測定単位を使用して、テーブル全体の幅の半分に相当する値を入力します。

5. 2 つ目の列ヘッダー テキスト ボックスを選択します。[プロパティ] ウィンドウの [Width] までスクロールし、「1.5」と入力します。または、既定の測定単位を使用して、テーブル幅の半分に相当する値を入力します。

6. テーブルを選択します。方向キーを使用して、テーブルをレポート ページの一番上まで移動し、テーブルの外枠をレポートの端に揃えます。

7. レポートの端と背景 (白地に網点) の境界上にマウス カーソルを合わせ、マウス カーソルが両方向の矢印に変わったらレポートの端をテーブルの外枠近くまでドラッグし、レポートを表示したときに余白ができないようにします。

8. 同様に、レポートの下端をテーブル下部の外枠近くまでドラッグし、レポート下部に余白ができないようにします。

色を設定するパラメータを追加するには

1. [レイアウト] ビューで、[レポート] メニューの [レポート パラメータ] をクリックします。[レポート パラメータ] ダイアログ ボックスが表示されます。

2. [追加] をクリックします。新しいパラメータが既定値で生成されます。

3. [プロパティ] セクションの [名前] ボックスに「BackgroundColor」と入力し、データ型が String であることを確認します。

4. [非表示] チェック ボックスをオンにします。これで、[表示名] ボックスが無効になります。

5. [空白の値を許可] チェック ボックスをオフにします。

6. [使用できる値] セクションで、[クエリなし] が選択されていることを確認します。値テーブルは空のままにしておきます。

7. [既定値] セクションで [クエリなし] をクリックし、「Azure」と入力します。

   これで、メイン レポートからサブレポートに渡されたパラメータ値がサブレポートで使用されるようになります。メイン レポート側で特定のパラメータに値を指定しなかった場合は、サブレポートのパラメータの既定値が使用されます。

8. 手順 1. ～ 6. に従って、FontColor という新しいパラメータを作成します。

9. [既定値] セクションで [クエリなし] をクリックし、「SteelBlue」と入力します。

10. [OK] をクリックします。

11. [レイアウト] ビューで、テーブル詳細行を右クリックします。[プロパティ] ウィンドウの [BackgroundColor] までスクロールし、値として次の式を設定します。

    =Parameters!BackgroundColor.Value
    

12. [プロパティ] ウィンドウの [Color] までスクロールし、値として次の式を設定します。

    =Parameters!FontColor.Value
    

13. [プレビュー] をクリックします。テーブル行の背景色とフォント色が、パラメータ BackgroundColor および FontColor の値どおりになっていることを確認してください。

レポートの説明を追加するには

1. [レイアウト] タブをクリックします。

2. [レポート] メニューの [レポートのプロパティ] をクリックします。[レポートのプロパティ] ダイアログ ボックスが表示されます。

3. [説明] ボックスに「連絡先情報を表示するサブレポート」と入力します。

4. [OK] をクリックします。

次の手順

ここでは、再販業者の連絡先情報を表示するレポートを作成し、サブレポートとして使用できるよう、幅の設定と余白の削除を行い、外観や内容を制御するパラメータを作成しました。次のレッスンでは、このレポートをサブレポートとして Resellers Worldwide レポートに追加します。「レッスン 6 : パラメータを含むサブレポートの追加」を参照してください。

変更履歴

参照

その他の技術情報

Reporting Services でのパラメータを使用した作業
ユーザー定義関数の基礎

ヘルプおよび情報

SQL Server 2005 の参考資料の入手