Lesson 5: Adding A Report Parameter to Pass to a User-Defined Function

Article
12/03/2008

New: 17 July 2006

Report parameters can be bound to input parameters passed to a user-defined function (ufn) or a stored procedure in a dataset query. User-defined functions can return a table or a scalar (single result) value. A dataset query, by definition, retrieves one result from a user-defined function or stored procedure. One advantage of a user-defined function over a stored procedure is that you can include it directly in a Transact-SQL SELECT statement, thereby combining the function result with other columns in the query result set.

User-defined functions and stored procedures do not directly accept multivalue parameters, which are arrays. To pass a multivalue array to a stored procedure, you can use the Visual Basic function JOIN to create a concatenated string of all values and write the user-defined function to accept a string parameter with multiple selections separated by a delimiter of your choice.

The user-defined function ufnGetContactInformation accepts a database identifier for the Person.Contact table and retrieves values for ContactID, Name, Phone, EmailAddress, JobTitle, and ContactType. In this report, you will create a parameter to allow the user to choose a predefined subset of fields to display.

In this lesson, you will create a new report called "Contact Information" with a parameter that accepts a database identifier for the person listed as the contact for a reseller store. You will create a query that calls the user-defined function ufnGetContactInformation from the AdventureWorks database, and bind the report parameter to the user-defined function's input parameter @ContactID.

You will prepare this report to be used as a subreport in the Resellers Worldwide report, creating parameters for background and text color to use, remove white space around the report, set margins, and set the report width. You will create a multivalue parameter for this report that allows the main report to specify which fields to display for the contact information.

To open the Advanced Parameters Tutorial report server project

In SQL Server Business Intelligence Development Studio, open the Advanced Parameters Tutorial report server project if it is not open already.

To create a new report from a user-defined function

In Solution Explorer, right-click the Reports folder. Choose Add, and then New Item. The Add New Item dialog box opens.
In the Templates pane, select Report.
In the Name text box, type Contact Information.rdl.
Click Add. The report opens in Data view.
From the Dataset drop-down list, select <New Dataset>. The Dataset dialog box opens.
On the Query tab, in the Name text box, type ContactInformation.
Verify that the Data source is Resellers.
Verify that the Command type is Text.
Paste the following query into the Query string pane.
```
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
```
This query uses a parameter called @ContactID to pass a database identifier to the user-defined function called ufnGetContactInformation, which accepts ContactID as an input parameter.
Click Run (!) on the toolbar. The Define Query Parameters dialog box opens.
In the Parameter Value column, for @ContactID, type 1. The result set shows the contact information for the store contact identified by the ContactID. The columns returned in the result set are defined in the user function.
(Optional) Verify the query parameter @ContactID is bound to the report parameter ContactID. From the report toolbar, click Edit Selected Dataset(…) and then the Parameters tab. The parameter @ContactID is set to the value =Parameters!ContactID.Value.

In the next procedure, you will create a default value for the report parameter ContactID. By providing a default value for every parameter, the report will automatically process when you click Preview.

To add a default for ContactID

From the Report menu, click Report Parameters. The Report Parameters dialog box opens with ContactID selected in the Parameters pane.
In the Properties section, in the Default values section, select Non-queried. Type 1.
Click OK.

In the next procedure, you will create a multivalue string parameter with which to select the contact information that you want to display. You will create a non-queried available values list for Phone, Email, or None.

To create the report parameter FieldsToDisplay

In Layout view, from the Report menu, choose Report Parameters. The Report Parameters dialog box opens.
Click Add. A new parameter with default values is created.
In the Properties section, in the Name text box, type FieldsToDisplay. Verify that the data type is String.
In Prompt, type Select contact information to display:.
Select the Multi-value option.
Verify that the Allow blank value option is selected.
In the Available values section, select Non-queried. Use the following table to enter values.

Label	Value
Phone	Phone
Email	EmailAddress
None	<Blank>

If a parameter has available values, you must provide an available value that is <Blank> to select the Allow blank value option.

In the next procedure, you will create the layout for this report. Because the report will be used in a main report as a subreport, the layout is simplified: one textbox to show the contact name and job title and one textbox to conditionally show e-mail and phone information.

To create the report layout for this information

Click the Layout tab. The report opens in Layout view.
From the Toolbox, drag a table report item to the design surface.
Right-click a column heading, and select Delete Columns. Two columns remain.
Paste the following expression in the first text box in the detail row:
```
=Fields!Name.Value & vbCrLf & "[ " & Fields!JobTitle.Value & " ]"
```
Paste the following expression in the second textbox in the detail row:
```
=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,""))
```
This expression simply looks in the values selected for the multivalued parameter FieldsToDisplay. If NONE is selected (only one parameter, and the label is None), the expression evaluates to a blank. If EmailAddress is present, part of the expression evaluates to "EmailAddress". If Phone is present, part of the expression evaluates to "Phone". These two values are concatenated with a carriage return. The entire evaluated expression appears in the contents of the second box.

This example illustrates the following points:
- Finding the number of values selected in a multivalued parameter: Parameters!FieldsToDisplay.Count
- For a multivalued parameter, testing the last label in the array for a specific string (in this case "None"): InStr("None",Parameters!FieldsToDisplay.Label(Parameters!FieldsToDisplay.Count-1))>0
- Conditionally returning a string based on whether a value is found in a multivalued parameter (this looks in the concatenated string of all selected values for the multivalued parameter FieldsToDisplay; if EmailAddress is found, it returns the specific value of EmailAddress from the dataset, and if not found, it returns a blank): IIF(InStr(Join(Parameters!FieldsToDisplay.Value,","),"EmailAddress")>0,Fields!EmailAddress.Value,"")
Note

The Visual Basic function IIF evaluates all function parameter values passed to it, so you cannot use this expression with dataset fields that can be Null.
(Optional) Click Preview. Change parameter values for the two parameters and see the results.

In the next procedure, you will format this report to be used as a subreport so it displays well within a main report by turning off the header and footer, setting the table width, removing extra whitespace, and setting the font and background to match the main report. You will also provide parameters so that color background and font color can be passed as parameters from the main report.

To remove white space and set report size

Click inside the table to display the table handles.
Right-click the detail row handle. From the shortcut menu, select Table Header and Table Footer and set them to Off. The table shows just a detail row.

In the next three steps, you need to adjust the report width to a known size. You will need this size when you add a subreport report item to the main report in the next lesson.
Select the table. In the Properties window, verify that the table width is 3. Adjust this value to the minimum needed to see the contact information.

The table width you define for the subreport determines how the subreport looks when it appears in the main report. To set a fixed size for the subreport, set the height and width to the values you want. You can also set CanGrow and CanShrink properties of a text box to allow the text box to grow and shrink vertically to accommodate the text content. CanGrow and CanShrink do not apply to the horizontal width. You cannot currently set a text box to automatically adjust its width based on the text.
Select the first column header text box. In the Properties window, scroll to Width and enter 1.5 or, using the default measurement, whatever value you need to set the width to half the table.
Select the second column header text box. In the Properties window, scroll to Width and enter 1.5 or, using the default measurement, whatever value you need using the default measurement to set the width to half the table.
Select the table. Using the arrow keys, move the table to the top of the report page and align the table edge with the report edge.
With the mouse, hover over the edge of the report surface (the dotted white background). When the mouse changes to a double-arrow, grab the edge of the report surface and move it close to the edge of the table. This removes whitespace when the report is rendered.
Similarly, remove whitespace from the bottom of the report page by moving the report surface close to the bottom of the table.

To add parameters to set colors

In Layout view, from the Report menu, choose Report Parameters. The Report Parameters dialog box opens.
Click Add. A new parameter with default values is created.
In the Properties section, in the Name text box, type BackgroundColor. Verify that the data type is String.
Select the Hidden option. The Prompt text box is disabled.
Clear the Allow blank value option.
In the Available values section, verify that Non-queried is selected. Leave the value table empty.
In the Default values section, click Non-queried and type Azure.

Parameter values passed from the main report to a subreport will be used in the subreport. If the main report does not specify a value for a particular parameter, the subreport parameter default is used.
Create a new parameter called FontColor by following steps 1 through 6.
In the Default values section, click Non-queried and type SteelBlue.
Click OK.
In Layout view, right-click the table detail row. In the Properties window, scroll to BackgroundColor. Set the value to the following expression:
```
=Parameters!BackgroundColor.Value
```
In the Properties window, scroll to Color. Set the value to the following expression:
```
=Parameters!FontColor.Value
```
Click Preview. Verify the background of the table row and the font color are set to the parameter values for BackgroundColor and FontColor.

To add a report description

Click the Layout tab.
From the Report menu, select Report Properties. The Report Properties dialog box opens.
In the Description text box, type the following text: Subreport to show contact information.
Click OK.

Next Steps

You have successfully created a report to display reseller store contact information. You have formatted the report to be used as a subreport by setting its width, removing extra white space, and creating parameters to control the appearance and content. In the next lesson, you will add this report to the Resellers Worldwide report as a subreport. See Lesson 6: Adding a Subreport with Parameters.

Change History

Release	History
12 December 2006	Changed content: Corrected code example to `udf.ContactID = c.ContactID`.

Share via