MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters - Page 8
May 16, 2005
The remaining reference to the now-deleted parameter is itself deleted. All that remains is a bit of "level extraction." We recall that the consumers have told us that the row axis needs only to display the lowest level of the Product dimension, 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.
Our next steps will deal with the requested "extractions," to ready the report for the specialized date and top and bottom count capabilities that the marketing department has 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 interactive date drilldown will be replaced by a hierarchical date picklist, which will allow date aggregation selection on the fly at run time).
The targeted "extractions" are depicted in Illustration 34.
Click for larger image
17. Click the Groups tab.
Four groups appear in the Rows list box, and two added groups appear in the Columns list box. The groups appear, with those targeted for elimination circled, as shown in Illustration 35.
18. Click the BrandSales_Product_Department field (the top in the Rows list) to select it.
19. Click the Delete button to delete the group.
20. Click the BrandSales_Product_Category field (currently the middle entry in the Rows list) to select it.
21. Click the Delete button to delete the group.
22. Click the BrandSales_Product_Subcategory field (currently the middle entry in the Rows list) to select it.
23. Click the Delete button to delete the group.
24. Click the BrandSales_Year field (currently the top entry in the Columns list) to select it.
25. Click the Delete button to delete the group.
26. Click the BrandSales_Quarter field (currently the middle entry in the Columns list) to select it.
27. Click the Delete button to delete the group.
The Groups tab appears, after our deletions, as depicted in Illustration 36.
28. Click the BrandSales_Brand_Name group (now the only entry in the Rows list) to select it.
29. Click Edit.
The Grouping and Sorting Properties dialog for the group opens to the General tab.
30. Click the Visibility tab.
31. Click the Visible radio button, to enable static visibility for the BrandSales_Brand_Name group, in accordance with the information consumers' wishes.
32. 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 37.
Although the information consumers have requested that the Product Name appear in the report, we are leaving the Product Brand Name group as a placeholder, which we will convert to house the Product Name data element once we modify the underlying data source in later steps.
33. Click OK to accept changes and close the Grouping and Sorting Properties dialog for the BrandSales_Brand_Name group.
34. Click OK to accept changes, to close the Matrix Properties dialog, and to return to the Layout view in Report Designer.
35. Click the Preview tab to execute the modified report.
AdHoc_TopBottomFoodmart Sales.rdl appears as partially depicted in Illustration 38.
The report's layout has been altered sufficiently to meet the layout requirements as expressed by the information consumers. Let's make one more "elimination:" let's remove the Dataset that supported the Product Family picklist we have all but dismantled from its capacity in the original report.
36. Click the Data tab.
37. In the Dataset selector atop the Data tab, (which is now occupied by ProductData, a Dataset created by the simple MDX query to support the sample FoodMart Sales report), select ProductList.
This exposes the simple MDX query that supported the picklist.
38. Click the Delete Selected Dataset button, shown circled in Illustration 39.
The Dataset definition is deleted, leaving the ProductData query in its place. In the next section, we will add parameterization to complete the requirements.