式の例 (レポート ビルダ 2.0)
レポートでは、内容と外観を制御するために式をよく使用します。式は Microsoft Visual Basic で記述し、組み込み関数、カスタム コード、グローバル変数、およびユーザー定義変数を使用することができます。式エディタと、含めることができる参照の種類の詳細については、「式の使用 (レポート ビルダ 2.0)」と「式を追加する方法 (レポート ビルダ 2.0)」を参照してください。
このトピックでは、レポート内で一般的なタスクに使用できる式の例を示します。
Visual Basic の関数 Visual Basic の日付関数、文字列関数、変換関数、および条件関数の例
レポートの関数 集計および組み込みのレポート関数の例
レポート データの表示方法 レポートの外観を変更する例
プロパティ 形式または表示を制御するために、レポート アイテムのプロパティを設定する例
パラメータ 式の中でパラメータを使用する例
カスタム コード 埋め込まれたカスタム コードの例
関数
レポート内の多くの式には、関数が含まれています。これらの関数を使用して、データの書式を設定し、ロジックを適用し、レポートのメタデータにアクセスできます。MicrosoftVisual Basic ランタイム ライブラリの関数や、System..::..Convert 名前空間および System..::..Math 名前空間の関数を使用する式を記述できます。また、他のアセンブリまたはカスタム コードの関数への参照も追加できます。System.Text.RegularExpressions など、Microsoft.NET Framework のクラスを使用することもできます。Visual Basic の関数の詳細については、msdn.microsoft.com の「Visual Basic ランタイム ライブラリ」を参照してください。
Visual Basic の関数
Visual Basic の関数を使用して、テキスト ボックスに表示されるデータや、レポートのパラメータ、プロパティ、または他の領域に使用されるデータを操作できます。ここでは、このような関数のうち、いくつかの例を紹介します。Visual Basic の関数の詳細については、Visual Basic のドキュメントを参照してください。
日付関数
Today 関数は現在の日付を返します。この式は、レポートに日付を表示するテキスト ボックス、または現在の日付に基づいてデータをフィルタ処理するパラメータに使用できます。
=Today()DateAdd 関数は、1 つのパラメータに基づいて日付の範囲を指定する場合に役立ちます。次の式では、StartDate という名前のパラメータで指定した日付の 6 か月後の日付が返されます。
=DateAdd(DateInterval.Month, 6, Parameters!StartDate.Value)Year 関数は、特定の日付の年を表示します。この関数を使用して、日付をグループ化したり、一連の日付のラベルとして年を表示したりできます。次の式では、指定した販売注文日グループの年が返されます。また、Month 関数および他の関数を使用して、日付を操作することもできます。詳細については、Visual Basic のドキュメントを参照してください。
=Year(Fields!OrderDate.Value)
文字列関数
連結演算子および Visual Basic の定数を使用して、複数のフィールドを組み合わせます。次の式では、2 つのフィールドが、同じテキスト ボックス内の別々の行に返されます。
=Fields!FirstName.Value & vbCrLf & Fields!LastName.ValueFormat 関数を使用して、文字列内の日付と数値の書式を設定します。次の式では、StartDate パラメータおよび EndDate パラメータの値が長い日付形式で表示されます。
=Format(Parameters!StartDate.Value, "D") & " through " & Format(Parameters!EndDate.Value, "D")日付または数値のみを格納するテキスト ボックスに書式を適用する場合は、テキスト ボックス内で Format 関数を使用するのではなく、テキスト ボックスの Format プロパティを使用する必要があります。
Right、Len、InStr の各関数は、サブストリングを返す場合に役立ちます。たとえば、DOMAIN\username の文字列からユーザー名だけを返します。次の式では、User というパラメータで取得できる文字列のうち、円記号 (\) より右側の部分のみが返されます。
=Right(Parameters!User.Value, Len(Parameters!User.Value) - InStr(Parameters!User.Value, "\"))次の式では、Visual Basic の関数の代わりに .NET Framework の System..::..String クラスのメンバを使用しています。返される値は、上記の式と同じです。
=Parameters!User.Value.Substring(Parameters!User.Value.IndexOf("\")+1, Parameters!User.Value.Length-Parameters!User.Value.IndexOf("\")-1)複数の値を持つパラメータから選択した値を表示します。次の例では、Join 関数を使用して、パラメータ MySelection から選択した複数の値を連結して単一の文字列にし、その文字列をレポート アイテム内のテキスト ボックスの値を表す式として設定できるようにしています。
= Join(Parameters!MySelection.Value).NET Framework の RegularExpressions に含まれている一連の Regex 関数は、電話番号の書式を設定するなど、既存の文字列の書式を変更する場合に役立ちます。次の式では、フィールドに含まれる、"nnn-nnn-nnnn" 形式の 10 桁の電話番号を、Replace 関数を使用して "(nnn) nnn-nnnn" 形式に変更しています。
=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")
変換関数
Visual Basic の関数を使用して、フィールドのデータ型を別のデータ型に変換することができます。変換関数を使用すると、フィールドの既定のデータ型を、計算やテキストの連結に必要なデータ型に変換できます。
次の式では、フィルタ式の [値] フィールドの Transact-SQL money データ型と比較するために、定数 500 を Decimal 型に変換します。
=CDec(500)次の式では、複数の値を持つパラメータ MySelection で選択された値の数が表示されます。
=CStr(Parameters!MySelection.Count)
決定関数
Iif 関数では、式が True かどうかによって、2 つの値のいずれかが返されます。次の式では、Iif 関数を使用して、LineTotal の値が 100 を超える場合にブール値 True が返されます。それ以外の場合は、False が返されます。
=IIF(Fields!LineTotal.Value > 100, True, False)複数の IIF 関数 ("入れ子になった IIF" とも呼ばれます) を使用すると、PctComplete の値に応じて 3 つの値のいずれかを返すことができます。次の式をテキスト ボックスの塗りつぶしの色に使用すると、テキスト ボックスの値に応じて背景色を変更できます。
=IIF(Fields!PctComplete.Value >= 10, "Green", IIF(Fields!PctComplete.Value >= 1, "Blue", "Red"))背景は、値が 10 以上の場合は緑色、1 ～ 9 の場合は青色、1 未満の場合は赤色になります。
同じ機能を実現するには、Switch 関数を使用するという方法もあります。Switch 関数は、検証する条件が 3 つ以上ある場合に役立ちます。Switch 関数は、True に評価される一連の式のうちの最初の式に関連付けられた値を返します。
=Switch(Fields!PctComplete.Value >= 10, "Green", Fields!PctComplete.Value >= 1, "Blue", Fields!PctComplete.Value = 1, "Yellow", Fields!PctComplete.Value <= 0, "Red",)背景は、値が 10 以上の場合は緑色、1 ～ 9 の場合は青色、1 の場合は黄色、0 以下の場合は赤色になります。
ImportantDate フィールドの値を検証し、経過日数が 1 週間を超えている場合は "Red"、それ以外の場合は "Blue" を返します。この式を使用して、レポート アイテムに含まれるテキスト ボックスの Color プロパティを制御できます。
=IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>7,"Red","Blue")PhoneNumber フィールドの値を検証し、値が null (Visual Basic では Nothing) の場合は "No Value" を返し、それ以外の場合は電話番号の値を返します。この式を使用して、レポート アイテムに含まれるテキスト ボックスの値を制御できます。
=IIF(Fields!PhoneNumber.Value Is Nothing,"No Value",Fields!PhoneNumber.Value)Department フィールドの値を検証し、サブレポート名または null (Visual Basic では Nothing) を返します。この式は、条件付きドリルスルー サブレポートで使用できます。
=IIF(Fields!Department.Value = "Development", "EmployeeReport", Nothing)フィールド値が NULL かどうかを検証します。この式を使用すると、画像レポート アイテムの Hidden プロパティを制御できます。次の例では、LargePhoto というフィールドの値が NULL でない場合にのみ、このフィールドで指定された画像が表示されます。
=IIF(IsNothing(Fields!LargePhoto.Value),True,False)
レポートの関数
Reporting Services にはこの他にも、レポート内のデータの操作に使用できるレポート関数があります。ここでは、このような関数のうち 2 つの例を紹介します。レポートの関数の詳細については、「式での組み込みのレポート関数と集計関数の使用 (レポート ビルダ 2.0)」を参照してください。
Sum
Sum 関数を使用すると、グループまたはデータ領域内の値を合計できます。この関数は、グループのヘッダーまたはフッターで役立ちます。次の式では、Order グループまたは Order データ領域内のデータの合計が表示されます。
=Sum(Fields!LineTotal.Value, "Order")Sum 関数は、条件付き集計計算にも使用できます。たとえば、データセットに State という名前のフィールドが存在し、Not Started、Started、Finished という値を設定できる場合、次の式をグループ ヘッダーで使用すると、値 Finished の合計のみが計算されます。
=Sum(IIF(Fields!State.Value = "Finished", 1, 0))
RowNumber
RowNumber 関数をデータ領域内のテキスト ボックスで使用すると、式が表示されるテキスト ボックスの各インスタンスの行数が表示されます。この関数は、テーブル内の行数を数える場合に役立ちます。また、行数に基づいた改ページの指定など、より複雑なタスクにも役立ちます。詳細については、このトピックの「改ページ」を参照してください。
RowNumber に指定したスコープによって、どの時点で番号の再設定を開始するかが制御されます。この関数に Nothing キーワードを指定することで、最も外側のデータ領域内の最初の行からカウントが開始されます。入れ子になったデータ領域内でカウントを開始するには、データ領域の名前を使用します。グループ内でカウントを開始するには、グループの名前を使用します。
=RowNumber(Nothing)
レポート データの表示方法
式を使用して、レポートにデータを表示する方法を操作できます。たとえば、2 つのフィールドの値を 1 つのテキスト ボックスに表示したり、レポートに関する情報を表示したり、レポートに改ページを挿入する方法に影響を与えたりすることができます。
ページ ヘッダーとページ フッター
レポートをデザインする場合、必要に応じてレポート名とページ番号をレポート フッターに表示できます。この操作を行うには、以下の式を使用できます。
次の式では、レポート名、およびレポートが実行された時刻が返されます。この式は、レポート フッターまたはレポート本文のテキスト ボックスで使用できます。時間の書式は、.NET Framework の短い日付用の書式設定の文字列を使用して設定されます。
=Globals.ReportName & ", dated " & Format(Globals.ExecutionTime, "d")次の式では、レポートのページ番号および全ページ数が返されます。この式は、レポートのフッター内のテキスト ボックスで使用できます。
=Globals.PageNumber & " of " & Globals.TotalPages
以下の例では、ディレクトリの一覧の内容と同様に、ページ ヘッダーにページの最初と最後の値を表示する方法について説明します。この例では、LastName という名前のテキスト ボックスを含むデータ領域を想定しています。
次の式は、ページ ヘッダーの左側にあるテキスト ボックスで使用されます。この式では、そのページの LastName テキスト ボックスの最初の値が返されます。
=First(ReportItems("LastName").Value)次の式は、ページ ヘッダーの右側にあるテキスト ボックスで使用されます。この式では、そのページの LastName テキスト ボックスの最後の値が返されます。
=Last(ReportItems("LastName").Value)
次の例では、合計ページ数の表示方法について説明します。この例では、Cost という名前のテキスト ボックスを含むデータ領域を想定しています。
次の式は、ページ ヘッダーまたはページ フッターで使用されます。この式では、Cost テキスト ボックスの値の合計がそのページに返されます。
=Sum(ReportItems("Cost").Value)
.gif "注意") 注意 |
|---|
ページ ヘッダーまたはページ フッターでは、1 つの式につき 1 つのレポート アイテムしか参照できません。また、ページ ヘッダーまたはページ フッターの式では、テキスト ボックスの名前を参照することはできますが、テキスト ボックス内の実際のデータ式は参照できません。 |
改ページ
レポートによっては、グループやレポート アイテムに改ページを設定せずに、またはグループやレポート アイテムの改ページに追加する形で、指定した行数の最後に改ページを挿入したい場合があります。この操作を行うには、必要なグループや詳細レコードを含むグループを作成し、そのグループに改ページを追加した後、指定された行数でグループ化を行うグループ式を追加します。
次の式をグループ式で使用すると、25 行ごとに数値が割り当てられます。グループに改ページが定義されている場合は、この式の結果として、25 行ごとに改ページが行われます。
=Ceiling(RowNumber(Nothing)/25)ユーザーが 1 ページあたりの行数の値を設定できるようにするには、RowsPerPage という名前のパラメータを作成し、次の式で示すように、そのパラメータに基づいてグループ式を作成します。
=Ceiling(RowNumber(Nothing)/Parameters!RowsPerPage.Value)グループに改ページを設定する方法の詳細については、「改ページを追加する方法 (レポート ビルダ 2.0)」を参照してください。
プロパティ
式は、テキスト ボックスにデータを表示するためだけに使用するものではありません。レポート アイテムにプロパティを適用する方法を変更する場合にも使用できます。レポート アイテムのスタイル情報を変更したり、情報の表示を変更することができます。
書式設定
テキスト ボックスの Color プロパティで次の式を使用すると、Profit フィールドの値に応じてテキストの色が変更されます。
=Iif(Fields!Profit.Value < 0, "Red", "Black")また、Visual Basic のオブジェクト変数 Me を使用することもできます。この変数は、テキスト ボックスの値を参照するもう 1 つの手段です。
=Iif(Me.Value < 0, "Red", "Black")
データ領域にあるレポート アイテムの BackgroundColor プロパティで次の式を使用すると、各行の背景色に薄緑色と白が交互に設定されます。
=Iif(RowNumber(Nothing) Mod 2, "PaleGreen", "White")特定のスコープに対して式を使用する場合は、次のように、集計関数に対してデータセットを指定する必要があります。
=Iif(RowNumber("Employees") Mod 2, "PaleGreen", "White")
| 注意 |
|---|
使用できる色は、.NET Framework の KnownColor 列挙体の色です。 |
表示
レポート アイテムの表示プロパティを使用して、レポート内のアイテムの表示/非表示を切り替えることができます。テーブルなどのデータ領域では、最初に、式の値に基づいて詳細行を非表示にできます。
グループ内の詳細行の初期表示に次の式を使用すると、PctQuota フィールドで 90% を超えるすべての売上の詳細行が表示されます。
=Iif(Fields!PctQuota.Value>.9, False, True)テーブルの Hidden プロパティに次の式を設定すると、テーブルの行数が 12 行を超えた場合にのみテーブルが表示されます。
=IIF(CountRows()>12,true,false)列の Hidden プロパティに次の式を設定すると、データ ソースからデータが取得された後にレポート データセットにフィールドが存在する場合にのみ列が表示されます。
=IIF(Fields!Column_1.IsMissing, true, false)
URL
レポート データを使用して URL をカスタマイズできます。また、テキスト ボックスに対するアクションとして URL を追加するかどうかを、条件付きで制御することもできます。
次の式をテキスト ボックスに対するアクションとして使用すると、URL パラメータとしてデータセット フィールド EmployeeID を指定する、カスタマイズされた URL が生成されます。
="http://adventure-works/MyInfo?ID=" & Fields!EmployeeID.Value詳細については、「URL へのハイパーリンクを追加する方法 (レポート ビルダ 2.0)」を参照してください。
次の式では、テキスト ボックス内に URL を追加するかどうかを、条件付きで制御します。この式では、アクティブ URL をレポートに含めるかどうかをユーザーが決定できるように、IncludeURLs という名前のパラメータを使用しています。この式は、テキスト ボックスに対するアクションとして設定されます。パラメータを False に設定してからレポートを表示すると、ハイパーリンクなしでレポートを Microsoft Excel にエクスポートできます。
=IIF(Parameters!IncludeURLs.Value,"http://adventure-works.com/productcatalog",Nothing)
レポート データ
式を使用して、レポートで使用するデータを操作できます。パラメータおよび他のレポート情報を参照できます。レポートのデータを取得するために使用されるクエリを変更することもできます。
パラメータ
パラメータの式を使用して、パラメータの既定値を変更できます。たとえば、パラメータを使用して、レポートの実行に使用されるユーザー ID に基づき、特定のユーザーに合わせてデータをフィルタ処理することができます。
パラメータの既定値として次の式を使用すると、レポートを実行するユーザーのユーザー ID が収集されます。
=User!UserIDレポートのクエリ パラメータ、フィルタ式、テキスト ボックスなどの領域でパラメータを参照するには、Parameters グローバル コレクションを使用します。この例では、パラメータの名前が Department であることを想定しています。
=Parameters!Department.Valueパラメータはレポート内に作成できますが、非表示に設定されます。レポート サーバーでレポートが実行される際、パラメータがツール バーに表示されないため、レポートを表示するユーザーは既定値を変更できません。既定値に設定された非表示パラメータは、カスタム定数として使用できます。この値は、フィールド式など、あらゆる式で使用できます。次の式では、ParameterField という名前を持つパラメータの既定値で指定されたフィールドを特定しています。
=Fields(Parameters!ParameterField.Value).Value
カスタム コード
レポート内では、カスタム コードを使用できます。カスタム コードは、レポート内に埋め込むか、レポートで使用されるカスタム アセンブリに格納します。カスタム コードの詳細については、「式でのカスタム コード参照の使用 (レポート ビルダ 2.0)」を参照してください。
次の例では、埋め込みコードの ToUSD というメソッドが呼び出され、StandardCost フィールドの値が米ドル単位に変換されます。
=Code.ToUSD(Fields!StandardCost.Value)次の例は、カスタムの定数および変数を定義する方法を示しています。
[Visual Basic]
Public Const MyNote = "Authored by Bob" Public Const NCopies As Int32 = 2 Public Dim MyVersion As String = "123.456" Public Dim MyDoubleVersion As Double = 123.456式エディタの [定数] ビューには組み込み定数のみ表示され、カスタムの定数および変数は表示されませんが、次の例に示すように、任意の式から参照を追加できます。これらは、Variant 型として扱われます。
[Visual Basic]
=Code.MyNote =Code.NCopies =Code.MyVersion =Code.MyDoubleVersion
実行時のフィールド内のテキストの置換
次の例では、FixSpelling という埋め込みメソッドが呼び出され、SubCategory.Value 内の "Bike" というテキストがすべて "Bicycle" に置き換えられます。
=Code.FixSpelling(Fields!SubCategory.Value)
レポート定義に埋め込んで使用する次のコードは、FixSpelling メソッドの実装を示しています。この例では、.NET Framework の System.Text..::..StringBuilder クラスを参照する方法を示しています。詳細については、「レポートにコードを追加する方法 (レポート ビルダ 2.0)」を参照してください。
Public Function FixSpelling(ByVal s As String) As String
Dim strBuilder As New System.Text.StringBuilder(s)
If s.Contains("Bike") Then
strBuilder.Replace("Bike", "Bicycle")
Return strBuilder.ToString()
Else : Return s
End If
End Function
実行時の NULL 値または 0 の非表示
式内の一部の値は、レポート処理時に NULL または未定義と評価されることがあります。その結果、ランタイム エラーが発生して、評価した式ではなく "#Error" がテキスト ボックスに表示される場合があります。IIF 関数は特にこの動作の影響を受けます。これは、If-Then-Else ステートメントとは異なり、IIF ステートメントの各部分 (関数呼び出しを含む) が、true であるか false であるかを検証するルーチンに渡される前に評価されるためです。=IIF(Fields!Sales.Value is NOTHING, 0, Fields!Sales.Value) ステートメントを実行すると、Fields!Sales.Value が NOTHING の場合は、表示されたレポートに " #Error" と表示されます。
この状況を回避するには、次のいずれかの方法を使用します。
フィールド B の値が 0 または未定義の場合は、分子に 0、分母に 1 を設定します。それ以外の場合は、分子にフィールド A の値、分母にフィールド B の値を設定します。
=IIF(Field!B.Value=0, 0, Field!A.Value / IIF(Field!B.Value =0, 1, Field!B.Value))カスタム コード関数を使用して、式の値を返します。次の例では、現在の値と前の値の差がパーセンテージで返されます。これを使用すると、任意の連続する 2 つの値の差を計算できます。また、最初の比較のエッジ ケース (前の値が存在しない場合) と、前の値または現在の値が null (Visual Basic では Nothing) であるケースを処理できます。
Public Function GetDeltaPercentage(ByVal PreviousValue, ByVal CurrentValue) As Object If IsNothing(PreviousValue) OR IsNothing(CurrentValue) Then Return Nothing Else if PreviousValue = 0 OR CurrentValue = 0 Then Return Nothing Else Return (CurrentValue - PreviousValue) / CurrentValue End If End Function次の式では、このカスタム コードをテキスト ボックスから呼び出す方法を示しています。
=Code.GetDeltaPercentage(Previous(Sum(Fields!Sales.Value),"ColumnGroupByYear"), Sum(Fields!Sales.Value))これによってランタイム例外が回避されるため、テキスト ボックスの Color プロパティで =IIF(Me.Value < 0, "red", "black") のような式を使用し、その値に基づき、条件に応じてテキストを表示することができます。