第 5 课：添加报表参数以传递给用户定义函数

2008-12-15

更新日期： 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 报表的子报表，创建参数以供背景和文本颜色使用，删除报表周围的空白，设置边距，并设置报表宽度。您将为此报表创建一个多值参数，该参数允许主报表指定用于显示联系信息的字段。

打开“高级参数教程”报表服务器项目

在 SQL Server Business Intelligence Development Studio 中，打开高级参数教程报表服务器项目（如果此项目尚未打开）。

从用户定义函数创建新报表

在解决方案资源管理器中，右键单击**“报表”文件夹。选择“添加”，再选择“新建项”。系统将打开“添加新项”**对话框。
在**“模板”窗格中，选择“报表”**。
在**“名称”**文本框中，键入 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。
单击**“确定”**。

在下一步中，您将学习如何创建多值字符串参数，以选择您要显示的联系信息。您将为“电话”、“电子邮件”，或“无”创建无查询可用值列表。

创建报表参数 FieldsToDisplay

在“布局”视图中，从**“报表”菜单中选择“报表参数”。将打开“报表参数”**对话框。
单击**“添加”**。将创建一个具有默认值的新参数。
在**“属性”部分的“名称”**文本框中，键入 FieldsToDisplay。确保该数据类型是 String。
在**“提示”**中，键入“选择要显示的联系信息：”。
选择**“多值”**选项。
确保选中**“允许空白值”**选项。
在**“可用值”部分，选择“无查询”**。使用下表输入值。

标签	值
电话	Phone
电子邮件	EmailAddress
无	<空白>

如果参数有可用值，则必须提供一个 <空白> 可用参数，以选择**“允许空白值”**选项。

在下一步中，您将学习如何为此报表创建布局。因为此报表将用作报表的子报表，所以布局将简化：一个文本框显示联系人的姓名和职务，另一个文本框有条件地显示电子邮件和电话信息。

为此信息创建报表布局

单击**“布局”**选项卡。系统将在“布局”视图中打开此报表。
在“工具箱”中，将表报表项拖动到设计图面。
右键单击列标题，然后选择**“删除列”**。剩下两列。

将下列表达式粘贴到详细信息行的第一个文本框：

=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（只有一个参数，标签为“无”），则表达式的计算结果为空白。如果存在 EmailAddress，则部分表达式的计算结果为“EmailAddress”。如果存在 Phone，则部分表达式的计算结果为“Phone”。用回车符将这两个值串联在一起。计算出的整个表达式将显示在第二个框的内容中。

此例说明了下列几点：
- 查找多值参数中选定值的数目：Parameters!FieldsToDisplay.Count
- 针对多值参数，检查数组的最后一个标签中是否存在特定字符串（本例中为“无”）：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 对传递给它的所有函数参数值求值，因此当数据集字段可能为空时不能使用此表达式。
（可选）单击**“预览”**。更改两个参数的参数值，并查看结果。

注意：
Visual Basic 函数 IIF 对传递给它的所有函数参数值求值，因此当数据集字段可能为空时不能使用此表达式。

在下一步中，您将学习如何对将要作为子报表使用的报表进行格式设置，以使其能够很好地在主报表中显示，您将通过关闭表头和表尾、设置表宽度、删除多余的空白区域、设置字体和背景来与主报表的格式相符。您还将提供参数，从而使颜色背景和字体颜色能够作为来自主报表的参数进行传递。

删除空白并设置报表大小

在表内单击以显示表控点。
右键单击底部详细信息行控点。在快捷菜单中，选择**“表格表头”和“表格表尾”，并将它们设置为“关闭”**。表格将只显示一个详细信息行。

在随后的三个步骤中，您必须将报表宽度调整为已知大小。当您在下节课中将子报表报表项添加到主报表时，需要用到此大小。
选择表。在“属性”窗口中，确保表宽度为 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` 的代码示例。

通过