Lesson 4: Adding a Boolean Parameter to Control the Initial Drilldown State
New: 17 July 2006
Drilldown reports can be expanded or collapsed to show or hide summary or detail data. A drilldown report displays a toggle (+) for any summary group, row, or column for which the Visibility properties (Hidden and ToggleItem) are set. You set the Visibility properties on groups, rows, and headers to achieve this effect. The Resellers Worldwide report you created in Lesson 1 includes a drilldown action on the table group header. The report opens by default with the detail data collapsed. Only the table group header summaries appear until you expand the toggles to see the detail rows.
In this lesson, you will define a Boolean report parameter called ShowSalesDetails that allows report readers to set the initial drilldown state of the report. You will set the Hidden element of the Visibility property on the table detail row of the Order Date and Sales Order Number columns using this parameter. When report readers set ShowSalesDetails to true, they see the expanded report with all detail data when the report opens.
To open the ResellersWorldwide report
In SQL Server Business Intelligence Development Studio, open the Advanced Parameters Tutorial report server project if it is not already open.
In Solution Explorer, double-click on the Resellers Worldwide report. The report opens in Layout view.
To add a Boolean parameter
On the Report menu, click Report Parameters. The Report Parameters dialog box opens.
Click Add. A report parameter with default values is created.
In the Properties section, in the Name text box, type ShowSalesDetails.
From the Data type drop-down, select Boolean.
In the Prompt textbox, type Show Sales Details?.
Verify that all check boxes are unselected.
In the Available values section, select Non-queried. Leave the values blank.
In the Default values section, select Non-queried. In the text box, type
=False.Click OK.
(Optional) Click the Preview tab. Verify that the parameter appears with radio buttons to select True or False on the report toolbar.
To set table detail row visibility to use a Boolean parameter
Click the Layout tab to change to Layout view.
Click inside the table to show the table handles.
Right-click on the bottom detail row handle.
From the shortcut menu, select Edit Group. The Grouping and Sorting Properties dialog box opens.
Verify that the name of the group is table1_Details_Group.
Click the Visibility tab.
Under Initial Visibility, click Expression. Paste the following expression in the textbox.
=Not(Parameters!ShowSalesDetails.Value)When ShowSalesDetails is true (you want to see all the data), the Hidden property needs to be False (don't hide the data).
Verify that Visibility can be toggled by another report item is selected, and that Store appears in the Report Item drop-down list.
Click OK.
Click Preview. To verify that the parameter works as expected, select territory group North America, change the value of the ShowSalesDetails parameter and click View Report.
You now need to set the initial state of the toggle image (+ or -) to correspond to the expanded or collapsed settings.
To set the initial toggle setting to the correct collapsed or expanded image
Click the Layout tab to change to Layout view.
Click inside the table to show the table handles.
Click in the Store group header text box. The value of the Store text box begins with
=Fields!Store.Value.From the Properties window, scroll to the InitialToggleState property. The default display value is Collapsed. InitialToggleState, however, is a Boolean property, so you need to set the property to an expression that evaluates to True or False.
Click in the text box, and from the drop-down list, select <Expression>. Replace the text Collapsed with the following expression:
=Parameters!ShowSalesDetails.ValueThe toggle image (+ or -) indicates the action that will happen when you click on the toggle. When the toggle shows a plus sign (+), the information is collapsed and will expand when you click on it. When the toggle shows a minus sign (-), the information is expanded and will collapse when you click on it. So + is used for the collapsed state (false) and - used for the expanded state (true). The expanded state shows all the sales order details, so you can use the actual parameter value for ShowSalesDetails to be the value for the InitialToggleState.
Click Preview. To verify that the parameter works as expected, change the value of the ShowSalesDetails parameter and click View Report.
You can use the ShowSalesDetails parameter to control visibility for more than one report item. In the next procedure, you will set the visibility of the Order Date and Sales Order Number columns to the same ShowSalesDetails parameter.
(Optional) To set table column visibility to use a Boolean parameter
Click the Layout tab to change to Layout view.
Click inside the table to show the table handles.
Click the Order Date column header.
Hold down the CTRL key and click the Sales Order Number column header. Two columns are selected.
From the Properties window, scroll to the Visibility property.
Click the plus sign (+) to expand the Visibility property. The properties Hidden and ToggleItem appear.
From the Hidden property drop-down list, select Expression. The Edit Expression dialog box opens.
Paste the following expression in the expression pane:
=Not Parameters!ShowSalesDetails.ValueWhen ShowSalesDetails is true (you want to see all the data), the Hidden property needs to be False (don't hide the data).
Click OK.
Click Preview. The report opens showing just the columns for Store and Total Due.
Next Steps
You have successfully created a Boolean parameter that controls the visibility of table detail rows and two table columns. In the next lesson, you will add a subreport that displays reseller contact information. See Lesson 5: Adding A Report Parameter to Pass to a User-Defined Function.
See Also
Other Resources
Working with Parameters in Reporting Services
Hiding Report Items