MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts - Page 5
July 27, 2004
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 to this first objective, and to pave the way for the cascading parameters that we will construct next.
12. Click the Layout tab to return to the Layout view.
13. Select Report --> Parameters from the main menu atop the Report Designer, as shown in Illustration 14.
The Report Parameters dialog, where we define parameters for the report, appears, as depicted in Illustration 15.
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.
14. In the Parameters list, on the left side of the dialog, click the single entry, ProductFamily, to select it.
15. Click the Remove button underneath the Parameters list.
The ProductFamily parameter is removed from the list.
16. 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 page of the matrix.
17. 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.
18. 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.)
19. Select Properties from the context menu that appears, as shown in Illustration 16.
The Matrix Properties dialog opens, defaulted to the General tab.
20. Click the Filters tab.
21. Click the Value field of the single occupied row to select it.
22. Click the Delete button to delete the reference to the parameter, as indicated in Illustration 17.
The remaining reference to the now-deleted parameter is itself deleted. All that remains to tailor the report template to the Marketing department's specifications is to remove the Store Cost and Store Profit columns.
23. Select the Store Cost data textbox in the Matrix Properties dialog, as shown in Illustration 18.
24. Press the DELETE key on the PC to eliminate the Store Cost column and its contents.
25. Perform the same deletion process for the Store Profit data textbox, removing the corresponding column.
26. Grabbing the right side of the design surface, adjust the bare portion to the right side of the now narrower report form by dragging.
The Layout view of the report now appears as depicted in Illustration 19.
The report is ready to execute, without any limiting filters, so we can examine its new layout.
27. Click the OK button to close the Matrix Properties dialog.
28. Click the Preview tab again, to execute the report.
The report executes, and returns the full list of products, as shown (zoom reduction at 75%) in Illustration 20.
We have produced the core view requested by the Marketing department, and we are ready to build the cascading prompts that will meet their additional specifications.
Add Cascading Parameters