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 Jul 29, 2008

Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I - Page 4

By William Pearson

10.  Click Delete on the context menu that appears, as depicted in Illustration 7.

Illustration 7: Deleting the Calculated Member

11.  Click “Yes,” when asked, “Are you sure ....”

12.  Right-click the single entry in the Filter pane (directly atop the Query pane).

13.  Click Delete to eliminate the existing Product Category Parameter from the Filter pane, as shown in Illustration 8.

Illustration 8: Deleting the Product Parameter from the Filter Pane

We will make an addition to the Dataset next.

14.  Within the Metadata pane, expand the Date dimension, by clicking the “+” sign to its immediate left.

15.  Expand the Fiscal folder that appears within the Date dimension.

16.  Expand the Fiscal hierarchy (labeled “Date.Fiscal”) within the Fiscal folder.

17.  Drag the Month level into the Data pane, dropping it to the left of the Sales Reason column, as depicted in Illustration 9.

Illustration 9: Adding Months to the Dataset

The new Month column appears, as desired. Having made the necessary changes on the Data tab, we are ready to move to the Layout tab, where we can conclude our preparatory modifications to the report file.

18.  Click the Layout tab, as shown in Illustration 10.

Illustration 10: Click the Layout Tab

19.  On the Layout tab, within the matrix data region, select the value appearing underneath the Internet Total column heading.

20.  Right-click the value in the text box (the value appears as =Sum(Fields!Internet_Total_Product_Cost.Value) ).

21.  Select Delete from the context menu that appears, as depicted in Illustration 11.

Illustration 11: Delete the Value for Internet Total Product Cost

22.  Right-click the upper left-hand corner of the Matrix Data Region (the gray square).

The gray column and row bars disappear, as a light, patterned outline forms around the matrix data region, and the context menu appears.

23.  Select Properties from the context menu, as shown in Illustration 12.

Illustration 12: Select Properties from the Context Menu ...

The Matrix Properties dialog appears, defaulted to the General tab.

24.  Click the Groups tab.

25.  In the Columns section of the Groups tab (in the lower half of the tab), select the middle group, named matrix1_Sales_Territory_Group.

26.  Click the Edit button, as depicted in Illustration 13.

Illustration 13: Editing the Matrix1_Sales_Territory_Group Column Group ...

The Grouping and Sorting Properties dialog appears, defaulted to its General tab.

27.  Atop the General tab, change the existing Name to the following:


28.  Click the row containing the Expression value (currently the expression is “Fields!Sales_Territory_Group.Value”), within the Group on list, to enable the selector.

29.  Select =Fields!Month.Value within the selector, as shown in Illustration 14.

Illustration 14: Replacing the Existing Group Expression ...

30.  Click OK, to accept our modifications, and to close the Grouping and Sorting Properties dialog.

31.  Click OK on the Groups tab, to close the Matrix Properties dialog.

32.  Once again on the Layout tab, within the Matrix Data Region, right-click the value appearing underneath the Internet Orders column heading (the leftmost of the two remaining value cells).

33.  Select Properties on the context menu that appears, as depicted in Illustration 15.

Illustration 15: Modifying Properties for the Count Value ...

34.  On the Textbox Properties dialog that next appears, click the Format tab.

35.  Replace the existing Format code setting (in the upper left corner of the Format tab) with the following string:


Here we are simply changing the existing format to one more appropriate for a count value. The Format code appears on the Format tab of the Textbox Properties dialog as shown in Illustration 16.

Illustration 16: Replacing the Existing Format Code ...

36.  Click OK to accept our modifications, and to dismiss the Textbox Properties dialog.

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