MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX - Page 7
October 20, 2005
The changes we have made are largely to simplify the query, to narrow the Dataset it returns to more leanly support the new requirements of the report. We will see the query again, in our target drillthrough report. There we will reuse it, together with the DRILLTHROUGH statement, to generate a report that presents the detail under a selected Store Sales value, as we shall see.
Having recast our Dataset query to the new business requirements, we now must delete a remaining reference to the parameter we have removed, which we can access via the Properties dialog for the matrix. We will have additional tasks to perform within the Properties dialog, as well.
15. Click the Layout tab to return to the Layout view of the report.
16. 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.
17. 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.)
18. Select Properties from the context menu that appears, as shown in Illustration 35.
The Matrix Properties dialog opens, defaulted to the General tab.
19. Click the Filters tab.
20. Click the Value field of the single occupied row to select it.
21. Click the Delete button to delete the reference to the parameter, as indicated in Illustration 36.
The remaining reference to the now-deleted parameter is itself deleted. Our next steps, which we will also undertake within the matrix Properties dialog, involve a bit of "level extraction." We recall that the consumers have told us that the row axis needs to display only the lowest two levels of the Product dimension, Product Brand Name and Product Name. Moreover, they have declared that the custom report will no longer require drilldown features, as it will be a fixed report whose purpose in life is as a limited analysis tool. Further, they have stated that the total Stores Sales value displayed for each product will represent the total for 1997, therefore the existing Quarter groups (and the drilldown feature which is based upon the Quarter groups) can also be eliminated.
Our next steps will deal with the requested "extractions," as well as with the addition of a new group, Product Name, to ready the report for the specialized focus that the Purchasing department representatives have specified. These extractions include the Product Family, Product Category and Product Subcategory portions of the row axis, and the column levels Year and Quarter.
The targeted "extractions" are depicted in Illustration 37.
22. Click the Groups tab.
Four groups appear in the Rows list box, and two additional groups appear in the Columns list box. The groups appear, with those targeted for elimination enclosed in red rectangles, as shown in Illustration 38.
23. Click the BrandSales_Product_Department field (the top in the Rows list) to select it.
24. Click the Delete button to delete the group.
25. Click the BrandSales_Product_Category field (now the top in the Rows list) to select it.
26. Click the Delete button to delete the group.
27. Click the BrandSales_Product_Subcategory field to select it.
28. Click the Delete button to delete the group.
29. Click the BrandSales_Year field (currently the top entry in the Columns list) to select it.
30. Click the Delete button to delete the group.
31. Click the BrandSales_Quarter field to select it.
32. Click the Delete button to delete the group.
The Groups tab appears, after our deletions, as depicted in Illustration 39.
33. Click the BrandSales_Brand_Name group (now the only entry in the Rows list) to select it.
34. Click Edit.
The Grouping and Sorting Properties dialog for the group opens to the General tab.
35. Click the Visibility tab.
36. Click the Visible radio button, to enable static visibility for the BrandSales_Brand_Name group, in accordance with the information consumers' wishes.
37. Uncheck the box marked "Visibility can be toggled by another report item" located on the lower half of the tab.
The Grouping and Sorting Properties dialog appears as shown in Illustration 40.
38. Click the Sorting tab.
39. Click the top row in the Sort on list, just beneath the Expression label, to expose the selector.
40. Using the selector, select the following from the picklist provided to populate the top row of the list:
The Grouping and Sorting Properties dialog, Sorting tab, with our input, appears as we see in Illustration 41.
41. Click OK to accept our changes.
We return to the Groups tab.