第 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 的新報表，其中所包含的參數可接受列為轉售商店連絡人之人員的資料庫識別碼。您將建立查詢，從 AdventureWorks 資料庫呼叫使用者自訂函數 ufnGetContactInformation，並繫結報表參數至使用者自訂函數的輸入參數 @ContactID。

您將準備此報表作為 Resellers Worldwide 報表的子報表，以便建立參數供背景和文字色彩使用、移除報表周圍的空白、設定邊界，以及設定報表寬度。您將為此報表建立多重值參數，允許主報表指定要顯示的連絡資訊欄位。

開啟 Advanced Parameters Tutorial 報表伺服器專案

在 SQL Server Business Intelligence Development Studio 中，開啟 Advanced Parameters Tutorial 報表伺服器專案 (如果尚未開啟的話)。

從使用者自訂函數建立新報表

在 [方案總管] 中，以滑鼠右鍵按一下 [報表] 資料夾。選擇 [加入]，然後選取 [新增項目]。[加入新項目] 對話方塊隨即開啟。
在 [範本] 窗格中，選取 [報表]。
在 [名稱] 文字方塊中，輸入 Contact Information.rdl。
按一下 [加入]。報表隨即在 [資料] 檢視中開啟。
從 [資料集] 下拉式清單中，選取 [<新增資料集>]。[資料集] 對話方塊隨即開啟。
在 [查詢] 索引標籤的 [名稱] 文字方塊中，輸入 ContactInformation。
確認 [資料來源] 是 [轉售商]。
確認 [命令類型] 是 [文字]。

將下列查詢貼入 [查詢字串] 窗格中。

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。
按一下 [確定]。

在下一個程序中，您將建立多重值字串參數，再使用此參數選取您要顯示的連絡資訊。您將針對 Phone、Email 或 None 建立非查詢的可用值清單。

建立報表參數 FieldsToDisplay

在 [配置] 檢視中，從 [報表] 功能表選擇 [報表參數]。[報表參數] 對話方塊隨即開啟。
按一下 [加入]。隨即建立含有預設值的新參數。
在 [屬性] 區段的 [名稱] 文字方塊中，輸入 FieldsToDisplay。確認資料類型是 String。
在 [提示] 中，輸入**「選取要顯示的連絡人資訊：」**。
選取 [多重值] 選項。
確認已選取 [允許空白值] 選項。
在 [可用的值] 區段中選取 [非查詢的]。請使用下表來輸入值。

標籤	值
電話	Phone
Email	EmailAddress
None	<空白>

如果參數有可用的值，您必須提供 <空白> 的可用值，以選取 [允許空白值] 選項。

在下一個程序中，您將建立此報表的配置。由於此報表將作為主報表中的子報表，因此配置已加以簡化：一個文字方塊用來顯示連絡人姓名和職稱，一個文字方塊則會條件式地顯示電子郵件和電話資訊。

建立此資訊的報表配置

按一下 [配置] 索引標籤。報表隨即在 [配置] 檢視中開啟。
從 [工具箱] 拖曳資料表報表項目至設計介面。
以滑鼠右鍵按一下資料行標題，然後選取 [刪除資料行]。兩個資料行會保留不變。
將下列運算式貼入詳細資料列中的第一個文字方塊：
```
=Fields!Name.Value & vbCrLf & "[ " & Fields!JobTitle.Value & " ]"
```
將下列運算式貼入詳細資料列中的第二個文字方塊：
```
=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 (只有一個參數，並且標籤為 None)，運算式會評估為空白。如果出現的是 EmailAddress，部分運算式會評估為 "EmailAddress"。如果出現的是 Phone，部分運算式會評估為 "Phone"。這兩個值是以歸位字元串連在一起。運算式的整個評估結果會出現在第二個方塊的內容中。

此範例說明下列要點：
- 計算多重值參數中選取的值數目：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 的資料集欄位使用。
(選擇性) 按一下 [預覽]。變更兩個參數的參數值，然後查看結果。

附註：
Visual Basic 函數 IIF 會評估傳遞給它的所有函數參數值，因此，您不能將這個運算式搭配可為 Null 的資料集欄位使用。

在下一個程序中，您將關閉頁首和頁尾、設定資料表寬度、移除多餘的空白，並且將字型和背景設定成符合主報表，藉此將此報表格式化成子報表，如此，報表就能在主報表內清楚顯示。您也將提供參數，以便將背景色彩和字型色彩作為參數從主報表傳遞。

移除空白並設定報表大小

按一下資料表內部，以顯示資料表控制代碼。
以滑鼠右鍵按一下詳細資料列控制代碼。從快速鍵功能表中，選取 [資料表頁首] 和 [資料表頁尾]，然後將這兩項設定為 [關閉]。資料表只會顯示詳細資料列。

在接下來的三個步驟中，您需要將報表寬度調整為已知大小。在下一課，當您將子報表的報表項目加入主報表時，需要用到此大小。
選取資料表。在 [屬性] 視窗中，確認資料表寬度是 3。將此值調整成所需的最小值來查看連絡資訊。

為子報表定義的資料表寬度，決定子報表顯示在主報表中的外觀。若要為子報表設定固定大小，請將高度和寬度設定為您所要的值。您也可以設定文字方塊的 CanGrow 和 CanShrink 屬性，讓文字方塊垂直地增長或縮減，以容納文字內容。CanGrow 和 CanShrink 不適用於水平寬度。目前您無法將文字方塊設定成根據文字自動調整寬度。
選取第一個資料行標頭文字方塊。在 [屬性] 視窗中，捲動到 Width，然後輸入 1.5 或使用預設量值，或者以所需的任何值將寬度設為資料表的一半。
選取第二個資料行標頭文字方塊。在 [屬性] 視窗中，捲動到 Width，然後輸入 1.5 或使用預設量值，或者使用預設量值以所需的任何值將寬度設為資料表的一半。
選取資料表。使用方向鍵，將資料表移到報表頁面的頂端，並將資料表邊緣與報表邊緣對齊。
讓滑鼠停留在報表表面的邊緣上方 (白點背景)。當滑鼠變成雙箭頭時，抓住報表表面的邊緣，將報表移到接近資料表邊緣處。如此，即可在報表轉譯時移除空白。
同樣地，將報表表面移到接近資料表的底端，即可移除報表頁面底端的空白。

加入參數，以設定色彩

在 [配置] 檢視中，從 [報表] 功能表選擇 [報表參數]。[報表參數] 對話方塊隨即開啟。
按一下 [加入]。隨即建立含有預設值的新參數。
在 [屬性] 區段的 [名稱] 文字方塊中，輸入 BackgroundColor。確認資料類型是 String。
選取 [隱藏] 選項。[提示] 文字方塊隨即停用。
清除 [允許空白值] 選項。
在 [可用的值] 區段中，確認已選取 [非查詢的]。將值資料表保留為空白。
在 [預設值] 區段中，按一下 [非查詢的]，然後輸入 Azure。

從主報表傳遞到子報表的參數值將用在子報表中。如果主報表沒有為特定參數指定值，則會使用子報表的參數預設值。
遵循步驟 1 到 6 建立名為 FontColor 的新參數。
在 [預設值] 區段中，按一下 [非查詢的]，然後輸入 SteelBlue。
按一下 [確定]。
在 [配置] 檢視中，以滑鼠右鍵按一下資料表詳細資料列。在 [屬性] 視窗中捲動至 BackgroundColor。將值設定至下列運算式：
```
=Parameters!BackgroundColor.Value
```
在 [屬性] 視窗中捲動至 Color。將值設定至下列運算式：
```
=Parameters!FontColor.Value
```
按一下 [預覽]。確認資料表資料列的背景和字型色彩已設定為 BackgroundColor 和 FontColor 的參數值。

加入報表描述

按一下 [配置] 索引標籤。
從 [報表] 功能表中，選取 [報表屬性]。[報表屬性] 對話方塊隨即開啟。
在 [描述] 文字方塊中，輸入下列文字：「顯示連絡資訊的子報表」。
按一下 [確定]。

後續的步驟

您已順利建立報表，以顯示轉售商店連絡人資訊。您已設定報表的寬度、移除多餘的空白，並且建立參數來控制外觀和內容，藉此將報表格式化，以當做子報表使用。在下一課，您會將此報表當做子報表加入至 Resellers Worldwide 報表。請參閱＜第 6 課：加入含有參數的子報表＞。

變更歷程記錄

版本	歷程記錄
2006 年 12 月 12 日	變更的內容：更正 `udf.ContactID = c.ContactID` 的程式碼範例。

共用方式為