One last detail remains for you to set up: You need to make sure the toggle image correctly displays as a plus sign (+) when the report details are collapsed or a minus sign () when the report details are expanded. You can use the same expression you used to control grouping visibility to control the initial view of the toggle image. The toggle-image property exists on the field it refers to; in this case, the SalesPerson and OrderNumber fields contain the property. To set the InitialToggle-State property, right-click the SalesPerson field, select Properties, then click Advanced to open the Advanced Properties window. Next, select the Visibility tab that Figure 1 shows to use an expression to make sure the toggle image matches the user selection. Notice that initially, the visibility section of the tab is set to Visible. This is because I previously set the entire row—not just the SalesPerson and OrderNumber fields—to be hidden. I'm using the individual field's Visibility property setting to control only the initial appearance of the toggle image.
After selecting the proper settings on the Visibility tab, you can render the report in preview mode. As Figure 2 shows, users now have full control over the expandability of the report items, and the toggle image shows as a character, indicating that the report details for these items are expanded.
Dynamically Group Report Data
My next question came while I was researching Reporting Services' ability to dynamically alter the content or layout of a report based on user-selected values from parameters. What I really needed was the ability to dynamically control how report data is grouped. For example, in the reports I was rewriting, I had approximately five data groupings that users frequently needed to see. I didn't want to manually configure every report to include all five groupings on every report because such a task would be tedious and time-consuming. The answer was to use parameter values that I could associate with the names of the fields the users would need to group on. Users could then select parameter values such as Territory or Region, and Reporting Services would dynamically regroup the report based on their selections.
To see how to dynamically regroup a report by using a parameter value, go back to the Territory Sales Drilldown report. The sample query for the unmodified report contains one grouping, Name, from the SalesTerritory table. The report doesn't include the Group field, which is a higher-level regional breakdown that includes such values as Pacific and North America. On the Data tab for this report in the Visual Studio .NET report designer, select the Group field from the SalesTerritory table to add it as an available field within the report. When you click Refresh Fields on the Data tab toolbar, the new field will be available to the report. Next, open the Report Parameters property window by selecting Report Parameters from the Report drop-down menu. Add a report parameter called Grouping, as Figure 3 shows, and set the parameter's data type to be String. Next, set the available values of the fields to non-queried, and assign the Label and Value of each available selection. In this case, set the Labels, which is what users will see in the parameter drop-down list, to be Territory and Region. Next, set the Values to be Name and Group, which is what the parameter will use in the background to match the field name. Finally, set the default non-queried value to Name; this setting is important because it ensures that the report will automatically render when users execute it. If you don't set the default value, users will see a blank page until they choose a parameter value and click View Report. Non-queried values are those that you set up manually; you don't rely on a database query to populate their values.
Now that you've defined the report parameter, you can modify the report to add the dynamic-grouping functionality. You need to modify four locations on the report: the Territory text box, the Territory label, the Sales_Territory grouping, and the report title text box. To modify the Territory text box, select the Layout tab, and replace the current expression
=Fields!Name.Value
with an expression that will evaluate to the field name the parameter provides, such as
=Fields(Parameters!Grouping.Value).Value
Do the same for the Sales_Territory group in the Grouping and Sorting property window. You can access the grouping and sorting properties in several ways, but the easiest way is to select the entire table by right-clicking the left uppermost corner of the table and selecting Properties. From the Table Properties window, click the Groups tab, highlight the Sales_Territory group, and select Edit.
To make the Territory label and report title dynamically change when the parameter changes, from the Layout tab, select the TerritoryLabel cell and replace the label Territory with the expression =Parameters!Grouping.Label. Next, replace the report title Territory Sales with =Parameters!Grouping.Label & " "& "Sales". Because parameters contain two values, one that's displayed to the user for input and another that will be the data value, associating the report labels with the label values will maintain a uniform appearance between the parameter and the new, dynamically regrouped report, as Figure 4 shows.
Prev. page
1
[2]
3
next page 
