Lesson 6: Adding a Subreport with Parameters
New: 17 July 2006
Subreports provide a way to embed reports in a main report. Unlike drillthrough reports that open a separate report in the same window, a subreport appears as a report item embedded in the main report. Although any report can be a subreport, typically you need to format a report to be used as a subreport to improve the way it appears in the main report.
In this lesson, you will add the ContactInformation report as a subreport to the Resellers Worldwide report. You will learn how to add the subreport to a table and set its properties in the main report, including binding main report parameters to the subreport parameters.
To open the Advanced Parmeters 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.
In Solution Explorer, double-click on the Resellers Worldwide report. The report opens in Layout view.
In the next procedure, you will add a multivalue parameter ContactDisplayFields to this main report. When you set parameter properties on the subreport, you will bind the main report multivalue parameter ContactDisplayFields to the subreport multivalue parameter FieldsToDisplay.
To add multivalue parameter ContactDisplayFields
In Layout view, click the design surface. The Report and Format menu items appear.
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 ContactDisplayFields. Verify that the data type is String.
In the Prompt text box, enter Information to display in the subreport:.
Select the Allow blank values option.
Select the Multivalue option.
In the Available values section, verify that Non-queried is selected and use the following table to set available values:
Label Value Phone
Phone
Email
EmailAddress
None
<Blank>
In the Default values section, select Non-queried and paste the following expression in the Value text box: EmailAddress.
Click OK.
Click Preview.
Because you provided an available values list, the Select All value has been added automatically to the drop-down list for the ContactDisplayFields parameter.
In the next procedure, you will add a multivalue parameter called Colors. In this tutorial, you will set two values for the multivalue parameter Colors. When you set parameter properties for the subreport, you will bind a single value from this multivalue parameter to each of the subreport parameters BackgroundColor and FontColor.
To add multivalue parameter 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 Colors. Verify that the data type is String.
Replace the existing prompt with the following text: Choose background and font colors:.
Select the Multi-value option.
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, select Non-queried and paste the following two values in the Value text boxes:
#C9DEF6andSlateBlue.You must set two values for this parameter. The first value in the array will be passed to the subreport as the background color. The second value will be passed to the subreport as the font color.
In this example, the background color value is the hex RGB color code
#C9DEF6, the font color is the Web Color SlateBlue.Click OK.
Click Preview.
Because no set of available values was defined for the Colors parameter, no Select All value appears in its drop-down list.
In the next procedure, you will prepare a place in the table to add the subreport report item. You will add a second row to the table group header. You will merge columns so there are only two text boxes in that row, one wide enough to display the subreport. Then you will set the visibility for that header row so that you get the expected behavior for the parameter value ShowSalesDetails: making this detail row of contact information visibility when the parameter is true.
To add a new row to the table group header and merge its text boxes
Click in the table. The table handles appear.
Right-click the group header row. The contents of the text box begin with
=Fields!Store.Value. Select Insert Row Below. A second header row appears.With the row still selected, from the report toolbar font size drop-down list, select 8.
To minimize whitespace around a subreport, minimize the row that will hold the subreport. The subreport report item will expand as needed but will not shrink.
Select text boxes from columns 2, 3, and 4. Right-click and choose Merge Cells. There are now two text boxes in the new header row.
With the new table row selected, in the Properties window, scroll to Visibility and expand the node. Set Hidden to
=Not Parameters!ShowSalesDetails.Value.
To add a subreport to a main report
From the Solution Explorer, click the report ContactInformation.rdl and drag it to second text box in the header row, next to the Contact Information text box.
Right-click in the gray subreport area and choose Properties. The Subreport Properties dialog box opens.
Click the Parameters tab, and then click in the first Parameter Name text box. The drop-down list appears with the four parameters defined for the ContactInformation. Use the following table to set the subreport parameters.
Parameter Name Parameter Value ContactID
=Fields!StoreContactID.ValueFieldsToDisplay
=Parameters!ContactDisplayFields.ValueBackgroundColor
=Parameters!Colors.Value(0)FontColors
=Parameters!Colors.Value(1)Click OK.
Click on the row handle for the new group header row. In the Properties window, scroll to Height and set the value to 0.01 or the minimum value you can set for row height to eliminate extra whitespace.
The Height property for a subreport report item in the main report sets the minimum height. The subreport report item expands to accommodate the report to which it is bound.
Click in the subreport gray area. In the Properties window, scroll to Width and set the value to 3 or whatever width you need to match the subreport definition width.
To eliminate unwanted white space, the width of the subreport element should be less than or equal to the width of the subreport as defined in its definition. The subreport will grow in the main report as needed, but it does not shrink.
Preview the report. Select different values for FieldsToDisplay and verify the subreport displays your selections.
Because there are now eight parameters defined, the report toolbar is getting a bit crowded. To hide the toolbar in preview mode, click the Show or Hide Parameter Area button on the report toolbar.
In the next procedure, you will set the Colors parameter to be Internal. Internal parameters can be used as variables internal to the report; you cannot set their values through the report toolbar or in any other way.
To set a report parameter as internal
From the Report menu, choose Report Parameters. The Report Parameters dialog box opens.
From the Parameters list, select Colors.
In the Properties section, select the Internal option.
Setting a parameter value to Internal means this parameter only appears in the report definition. When you publish the report to the server, you will not be able to manage this parameter from the report server. The report toolbar will not show a prompt for this parameter, and if you use this report as a subreport, the parameter would not appear in the list of subreport parameters from a main report.
Click OK.
(Optional) Click Preview. No prompt shows on the toolbar for the Colors parameter.
In Lesson 8, after the report is published to the report server, you will hide many of the parameters of the Resellers Worldwide report to present a less cluttered look.
Next Steps
In this lesson, you successfully added an additional row to the table group header, merged cells, and added a subreport with parameters to a cell. You set visibility on this row based on the report parameter ShowSalesDetails value. In the next lesson, you will add a parameter to the existing AdventureWorks sample report "Territory Sales Drilldown". See Lesson 7: Creating a Drillthrough Report.
See Also
Other Resources
Working with Parameters in Reporting Services
Working with Report Items