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

[アーティクル]
12/15/2008

更新 : 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 レポートサーバープロジェクトを開くには

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

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

ソリューションエクスプローラで、[レポート] フォルダを右クリックし、[追加]、[新しい項目] の順にクリックします。[新しい項目の追加] ダイアログボックスが表示されます。
[テンプレート] ペインで [レポート] を選択します。
[ファイル名] ボックスに「Contact Information.rdl」と入力します。
[追加] をクリックします。レポートが [データ] ビューに表示されます。
[データセット] ボックスの一覧で、[<新しいデータセット>] を選択します。[データセット] ダイアログボックスが表示されます。
[クエリ] タブで、[名前] ボックスに「ContactInformation」と入力します。
[データソース] が [Resellers] になっていることを確認します。
[コマンドの種類] が [Text] になっていることを確認します。
[クエリ文字列] ボックスに、次のクエリを貼り付けます。
```
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 を受け取ります。
ツールバーの [実行] ([!]) をクリックします。[クエリパラメータの定義] ダイアログボックスが表示されます。
[パラメータ値] 列で、@ContactID の値として「1」を入力します。結果セットには、ContactID で表される店舗担当者の連絡先情報が表示されます。結果セットで返される列は、ユーザー定義関数で定義されています。
(省略可) クエリパラメータ @ContactID がレポートパラメータ ContactID にバインドされていることを確認します。そのためには、レポートツールバーの [選択したデータセットの編集] ([…]) をクリックし、[パラメータ] タブをクリックします。パラメータ @ContactID の値は、=Parameters!ContactID.Value に設定されています。

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

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

[レポート] メニューの [レポートパラメータ] をクリックします。[レポートパラメータ] ダイアログボックスが開きます。このダイアログボックスの [パラメータ] ペインでは ContactID が選択されています。
[プロパティ] セクションの [既定値] で [クエリなし] を選択し、「1」と入力します。
[OK] をクリックします。

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

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

[レイアウト] ビューで、[レポート] メニューの [レポートパラメータ] をクリックします。[レポートパラメータ] ダイアログボックスが表示されます。
[追加] をクリックします。新しいパラメータが既定値で生成されます。
[プロパティ] セクションの [名前] ボックスに「FieldsToDisplay」と入力し、データ型が String であることを確認します。
[表示名] に「表示する連絡先情報を選択してください : 」と入力します。
[複数値] チェックボックスをオンにします。
[空白の値を許可] チェックボックスがオンになっていることを確認します。
[使用できる値] セクションで [クエリなし] を選択し、次の表を参照して値を入力します。

ラベル	値
Phone	Phone
Email	EmailAddress
None	<空白>

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

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

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

[レイアウト] タブをクリックします。レポートが [レイアウト] ビューに表示されます。
ツールボックスからデザイン画面へ [テーブル] レポートアイテムをドラッグします。
列見出しを右クリックし、[列の削除] をクリックします。これで 2 つの列が残ります。
詳細行の 1 つ目のテキストボックスに次の式を貼り付けます。
```
=Fields!Name.Value & vbCrLf & "[ " & Fields!JobTitle.Value & " ]"
```

詳細行の 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 になる可能性のあるデータセットフィールドをこの式で使用することはできません。

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

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

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

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

次の 3 つの手順では、レポートの幅を所定のサイズに調整します。このサイズは、次のレッスンでサブレポートレポートアイテムをメインレポートに追加するときに必要になります。
テーブルを選択します。[プロパティ] ウィンドウで、テーブル幅が [3] であることを確認します。この値を調整して、連絡先情報を表示できる最小の幅にします。

サブレポートに定義したテーブル幅によって、メインレポート内でサブレポートがどのように表示されるかが決まります。サブレポートを固定のサイズに設定するには、高さと幅を必要な値に設定します。テキストボックスの CanGrow プロパティと CanShrink プロパティを設定し、内容に応じて高さが伸縮するようにすることもできます。CanGrow と CanShrink は幅には適用されません。現時点では、内容に応じてテキストボックスの幅を自動調整することはできません。
1 つ目の列ヘッダーテキストボックスを選択します。[プロパティ] ウィンドウの [Width] までスクロールして、「1.5」と入力します。または、既定の測定単位を使用して、テーブル全体の幅の半分に相当する値を入力します。
2 つ目の列ヘッダーテキストボックスを選択します。[プロパティ] ウィンドウの [Width] までスクロールし、「1.5」と入力します。または、既定の測定単位を使用して、テーブル幅の半分に相当する値を入力します。
テーブルを選択します。方向キーを使用して、テーブルをレポートページの一番上まで移動し、テーブルの外枠をレポートの端に揃えます。
レポートの端と背景 (白地に網点) の境界上にマウスカーソルを合わせ、マウスカーソルが両方向の矢印に変わったらレポートの端をテーブルの外枠近くまでドラッグし、レポートを表示したときに余白ができないようにします。
同様に、レポートの下端をテーブル下部の外枠近くまでドラッグし、レポート下部に余白ができないようにします。

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

[レイアウト] ビューで、[レポート] メニューの [レポートパラメータ] をクリックします。[レポートパラメータ] ダイアログボックスが表示されます。
[追加] をクリックします。新しいパラメータが既定値で生成されます。
[プロパティ] セクションの [名前] ボックスに「BackgroundColor」と入力し、データ型が String であることを確認します。
[非表示] チェックボックスをオンにします。これで、[表示名] ボックスが無効になります。
[空白の値を許可] チェックボックスをオフにします。
[使用できる値] セクションで、[クエリなし] が選択されていることを確認します。値テーブルは空のままにしておきます。
[既定値] セクションで [クエリなし] をクリックし、「Azure」と入力します。

これで、メインレポートからサブレポートに渡されたパラメータ値がサブレポートで使用されるようになります。メインレポート側で特定のパラメータに値を指定しなかった場合は、サブレポートのパラメータの既定値が使用されます。
手順 1. ～ 6. に従って、FontColor という新しいパラメータを作成します。
[既定値] セクションで [クエリなし] をクリックし、「SteelBlue」と入力します。
[OK] をクリックします。
[レイアウト] ビューで、テーブル詳細行を右クリックします。[プロパティ] ウィンドウの [BackgroundColor] までスクロールし、値として次の式を設定します。
```
=Parameters!BackgroundColor.Value
```
[プロパティ] ウィンドウの [Color] までスクロールし、値として次の式を設定します。
```
=Parameters!FontColor.Value
```
[プレビュー] をクリックします。テーブル行の背景色とフォント色が、パラメータ BackgroundColor および FontColor の値どおりになっていることを確認してください。

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

[レイアウト] タブをクリックします。
[レポート] メニューの [レポートのプロパティ] をクリックします。[レポートのプロパティ] ダイアログボックスが表示されます。
[説明] ボックスに「連絡先情報を表示するサブレポート」と入力します。
[OK] をクリックします。

次の手順

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

変更履歴

リリース	履歴
2006 年 12 月 12 日	変更内容 : コード例を `udf.ContactID = c.ContactID` に修正しました。

参照

その他の技術情報

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

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

次の方法で共有

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

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

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

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

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

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

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

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

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

次の手順

変更履歴

参照

その他の技術情報

ヘルプおよび情報

その他のリソース

次の方法で共有

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

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

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

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

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

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

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

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

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

次の手順

変更履歴

参照

その他の技術情報

ヘルプおよび情報

その他のリソース

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

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

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

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

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