MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters - Page 7
May 16, 2005
Modify the Foodmart Sales Report Layout to Meet Business Requirements
As we have mentioned, the FoodMart Sales report that we have cloned as AdHoc_TopBottomFoodmart Sales.rdl contains a prompt to allow users to filter by Product Family. We will make general layout changes in the report, while also eliminating the existing prompt, before adding the parameters that form the core of our focus in this lesson.
1. Right-click AdHoc_TopBottomFoodmart Sales.rdl in the Reports folder.
2. Select Open, as depicted in Illustration 26, from the context menu that appears.
Click for larger image
AdHoc_TopBottomFoodmart Sales.rdl opens and appears on the Layout tab of the Report Designer, as shown in Illustration 27.
3. Click the Preview tab to execute the report.
AdHoc_TopBottomFoodmart Sales.rdl executes briefly, and then returns data. We see, atop the Preview tab, the Product Family parameter selector, as depicted (expanded) in Illustration 28.
4. Select Non-Consumable in the Product Family selector.
5. Click the View Report button to execute the report.
The report appears, with different row items and values, as shown in Illustration 13. The differing results are due to its being filtered, via the parameter selection, on the Non-Consumable Product Family.
Each of the three Product Family selections gives us a results set filtered for its members. The Marketing team has asked for a "consolidated view," where all products appear on a single report. Elimination of the parameter in place will be a simple way to get started toward this first objective, and to pave the way for the new parameters that we will construct next.
6. Click the Layout tab to return to the Layout view.
7. Select Report -> Report Parameters from the main menu atop the Report Designer, as depicted in Illustration 30.
The Report Parameters dialog, where we define parameters for the report, appears, as shown in Illustration 31.
We will get plenty of exposure to this dialog later in our session, and throughout the MSSQL Server 2000 Reporting Services series in general. Our objective at this point is to remove the existing parameter, and to consolidate the Product Families thereby.
8. In the Parameters list, on the left side of the dialog, click the single entry, ProductFamily, to select it.
9. Click the Remove button underneath the Parameters list.
The ProductFamily parameter is removed from the list.
10. Click OK to accept our removal of the parameter.
The now empty Report Parameters dialog closes, and we are returned to the Layout view of the report. We now have only to delete a reference to the parameter we have removed, which we can access via the Properties dialog for the matrix.
11. Click at some point within the label textbox of the report (containing the label Foodmart Sales), to make the row and column headers of the matrix data region visible.
12. Right-click the upper left corner of the matrix. (If the headers disappear as you touch them with the cursor, you should still see a faint outline of the matrix.)
13. Select Properties from the context menu that appears, as depicted in Illustration 32.
The Matrix Properties dialog opens, defaulted to the General tab.
14. Click the Filters tab.
15. Click the Value field of the single occupied row to select it.
16. Click the Delete button to delete the reference to the parameter, as indicated in Illustration 33.