Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part II - Page 4
March 20, 2006
Procedure: Enhance the Report per the Business Requirements
Add a Multivalue Parameter for Sales Territories to the Report
We will add parameterization (with multivalue input capabilities) for Sales Territory Groups before making further structural changes to support the more involved business requirements described by the client representatives.
1. Click the Data tab for the report.
We arrive at the Data view, where we see the ProductData Dataset open in Design Mode, as shown in Illustration 10.
Click for larger image
NOTE: If the Data tab opens with another Dataset, or if it opens in Query Mode, make the necessary changes in the DataSet selector and / or the Design Mode toolbar button, respectively, as appropriate.
2. Click the "Click to execute the query" link (in the middle of the Results pane) to populate the Results pane.
The Results pane appears as depicted in Illustration 11.
Click for larger image
3. Select Sales Territory in the Dimension selector, underneath the Product selection already in place, within the Filter pane atop the Results pane, as shown in Illustration 12.
Click for larger image
4. Select Sales Territory Group in the Hierarchy selector, to the immediate right of the Dimension selector within the Filter pane.
5. Select Equal in the Operator selector, to the immediate right of the Hierarchy selector.
6. Check the All Sales Territories checkbox within the Filter Expression selector, to the immediate right of the Operator selector, as depicted in Illustration 13.
7. Click OK on the Filter Expression selector to accept our selection and close the dropdown selector.
8. Click the checkbox in the Parameters column of the Filter pane, to the immediate right of the Filter Expression selector.
Because we have selected to parameterize the query for Sales Territory Groups, Report Designer will automatically create the components we need to support the parameterization we have requested. Among the components created are a Dataset to support the Sales Territory Group parameterized filter, as well as the Report Parameter that goes along with the new Sales Territory Group parameter. The Sales Territory Group Dataset is actually used in conjunction with the Report Parameter to populate the runtime picklist that appears when we execute the report.
We can prepare to observe the very act of creation of these components within Report Designer, by taking the following steps.
9. Select Report --> Report Parameters from the main menu, as shown in Illustration 14.
The Report Parameters dialog opens. We see a single parameter listed, the ProductCategory parameter, as presented in the partial dialog depicted in Illustration 15.
10. Click OK to dismiss the Report Parameters dialog.
11. Click the downward arrow on the Dataset selector for the report file.
We note that two Datasets appear, ProductData and ProductList, as shown in Illustration 16.
Our next step, shifting to Layout view, will trigger the creation of a new Report Parameter as well as a new supporting Dataset, as we shall see in the following steps.
12. Click the Layout tab to shift to Layout view.
13. Select Report --> Report Parameters from the main menu, once again.
The Report Parameters dialog opens. This time, we see that a second Report Parameter has been created.
14. Click the new Report Parameter within the Parameters list (on the left side of the dialog).
The new SalesTerritorySalesTerritoryGroup parameter, appears as presented in the partial dialog depicted in Illustration 17.
15. Click OK to dismiss the Report Parameters dialog.
16. Click the Data tab.
17. Click the downward arrow on the Dataset selector for the report file, within the Data view, as we did earlier.
We note that three Datasets now appear. A new Dataset, SalesTerritorySalesTerritoryGroup, has joined the two pre-existing Datasets, as shown in Illustration 18.
18. Select the new Dataset within the Dataset selector.
19. Click the Execute Query button in the toolbar atop the Data view to populate the Results pane.
20. Click the Refresh fields button to refresh the Dataset fields.
The new Dataset populates the Results pane, which appears as depicted in Illustration 19.
Having verified that the support components have been automatically set up for us, let's preview the report to see the new Report Parameter in action.
21. Click the Preview tab.
The report executes, with the defaults for the Report Parameters (All Sales Territories for the new Sales Territory Group parameter) in place. The report appears as shown in Illustration 20.
Let's take a look at the effects of selecting a couple of the Territory Groups within the Sales Territory Group parameter (we will leave the Product Category parameter at default it really doesn't concern us at present).
22. Select Europe and North America (and deselect All Sales Territories) within the Sales Territory Group parameter, as depicted in Illustration 21.
23. Click the View Report button.
The report executes, and then appears as shown in Illustration 22.
We see that the report has contracted in width, displaying only the selected Sales Territory Groups, Europe and North America, as we requested. We have therefore verified the effectiveness of the multivalue Report Parameter within its "typical" use for a matrix data region: the columns of the matrix data region "adjust" to reflect the choices within the Report Parameter. This illustrates one of the primary advantages in using a matrix data region in conjunction with a multivalue Report Parameter.
Our client has requested something a little more innovative, however. In the next section, we will extend our work so far, to enable our report to generate a complete, standalone "report" (with separate row and column axes) to appear for each of the Sales Territory Groups selected within the new Report Parameter.
24. Select File --> Save All from the main menu, to save our work to this point.