Lesson 5: Adding a Parameter to Control Row Visibility
New: 17 July 2006
Boolean parameters can be true or false. Boolean parameters are frequently used in an expression for a property that expects a true or false value.
In this lesson, you will add a Boolean parameter called ShowSalesOrderDetails to the existing Sales Order report that controls whether to display sales detail information or just subtotals. When ShowSalesOrderDetails is true, you will see all the table detail rows and all the table columns when you view the report. If you set ShowSalesOrderDetails to false, only the name of the salespersons and their subtotals appear in the report.
To modify this report, you will set the Hidden element of the Visibility property on the table detail rows and on the columns for Order Date and Sales Order Number.
Add a Boolean report parameter
From the Report menu, click Report Parameters. The Report Parameters dialog box opens.
Click Add. A new report parameter is added to the Parameters pane.
On the Properties section, in the Name field, type ShowSalesOrderDetails.
In the Data type field, click the drop-down arrow and select Boolean.
In the Prompt field, type in a prompt. For example, Show Sales Orders Details?.
In the Available values section, verify Non-queried is selected. Available values are ignored for Boolean parameters.
In the Default values section, select Non-queried.
Click in the text box and type the following expression:
=true(Optional) Click Preview and see the new ShowSalesOrderDetails parameter with radio buttons on the toolbar.
Add an expression to the visibility property of the table detail row
In Layout view, click in the table data region. The table handles appear.
Right-click the detail row handle and select Properties. The Properties window shows properties for this table row.
Find the Visibility property and expand the node. The Hidden field appears with the default value false.
Click in the value field, and then click the drop-down arrow. Select <Expression>. The Edit Expression dialog box opens.
In the expression builder pane, replace the value false with the equal sign (=). This character begins every expression that is not a constant.
In the pane below the expression builder, select Parameters. The list of report parameters appears on the opposite pane.
Double-click the ShowSalesOrderDetails parameter. The expression for this parameter value is added to the expression builder pane.
When the parameter ShowSalesOrderDetails is true, the Hidden property needs to be false. Add the Not operator to the expression.
The value should now be the following:
=Not Parameters!ShowSalesOrderDetails.ValueClick OK.
Click Preview and test the parameter ShowSalesOrderDetails by changing the value and clicking View Report to see the effect.
Notice that the two columns Order Date and Sales Order Number continue to appear even when the detail rows do not appear. The next optional step shows you how to set the visibility for these columns.
Add an expression to the visibility property of table columns
In Layout view, click in the table data region. The table handles appear.
Click the column headers for Order Date and Sales Order Number. The Properties window shows properties for the table column headers.
Find the Visibility property and expand the node. The Hidden field appears with the default value false.
Copy the following expression:
=Not Parameters!ShowSalesOrderDetails.ValueRight-click in the Hidden field value. Select Paste.
Click outside the Properties window.
Click Preview and set ShowSalesOrderDetails to false. The Order Date and Sales Order Number columns are hidden.
In the next optional step, you can move the group sum from the group footer to the group header, and then choose not to display the group header. This minimizes the amount of space needed to render the report.
(Optional) Move the group subtotal from the group footer to the group header
In Layout view, click in the table data region. The table handles appear.
In the group footer row, copy the group sum expression from the last text box in the row. Paste the expression in the group header row. The first text box under the Total Due column header should now contain the following expression:
=Sum(Fields!TotalDue.Value).With a specific table group scope, whether you a place group total in the group header or the group footer has no effect on the actual total value.
In the Properties window for the group header text box with the total due expression, scroll to the Format property, and type C0. This sets the currency format.
Right-click in the table group header handle, and choose Edit Group. The Grouping and Sorting dialog box opens.
Clear the Include group footer checkbox.
Click OK.
Click Preview, set ShowSalesOrderDetails to false, and click View Report. Only the sales person name and the total due fields appear.
Next Steps
In this lesson, you successfully added a Boolean report parameter to the Sales Orders report. You set the visibility of a table data region detail row and two column headers to evaluate to an expression that contains the parameter value. In the next lesson, you will learn to pass a parameter to a drillthrough report. See Lesson 6: Adding a Parameter to Pass to a Drillthrough Report.
See Also
Other Resources
Working with Parameters in Reporting Services