Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 20, 2005

MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX - Page 7

By William Pearson

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.

Illustration 35: Accessing the Matrix Properties

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.

Illustration 36: Select and Delete the Parameter Reference

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.

Illustration 37: Targeted Levels for Removal in the Customized Report

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.

Illustration 38: Existing Groups in the Clone Report

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.

Illustration 39: Groups Tab after Eliminations

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.

Illustration 40: The Grouping and Sorting Properties Dialog with Our Changes

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.

Illustration 41: Brand Name Field as Sort Criteria ...

41.  Click OK to accept our changes.

We return to the Groups tab.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM