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 May 16, 2005

MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters - Page 8

By William Pearson

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

Illustration 34: Targeted Levels for Removal in the Customized Report

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.

Illustration 35: Existing Groups in the Clone Report

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.

Illustration 36: Groups Tab after Eliminations

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.

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

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.

Illustration 38: AdHoc_TopBottomFoodmart Sales.rdl (Partial View), Reflecting Our Modifications

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.

Illustration 39: Deleting the ProductList Picklist Query ...

The Dataset definition is deleted, leaving the ProductData query in its place. In the next section, we will add parameterization to complete the requirements.

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