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 Jan 16, 2007

Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II - Page 3

By William Pearson

We next need to remove the single Report Parameter that was associated with the parameterized Product Category filter that we have removed from the Filter pane. This Product Category Filter pane entry had been established with the Parameter checkbox selected, a common way to create a parameterized filter, as we saw in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, and as we shall see later in the practice session within this article. Because parameterization was selected for the Product Category filter, it follows that a Report Parameter might well have been created at the time the entry was made. We will now remove the remaining Report Parameter.

14.  From the main menu, select Report -> Report Parameters..., as depicted in Illustration 9.

Illustration 9: Select Report -> Report Parameters...

The Report Parameters dialog opens, and we see a single Report Parameter listed.

15.  Ensure that the ProductCategory Parameter is selected.

16.  Click the Remove button, as shown in Illustration 10.

Illustration 10: Removing the Residual Report Parameter ...

17.  Click OK to accept our modifications, and to dismiss the (now empty) Report Parameters dialog.

We will make preparatory modifications to the Dataset next. First, we will eliminate an unneeded data field.

18.  Within the Data pane, click the Sales Reason column heading, to select it.

19.  Drag the Sales Reason column heading into the Metadata pane, to remove it from the Data pane, as depicted in Illustration 11.

Illustration 11: Removing the Sales Reason Data Field from the Data Pane

The Sales Reason column disappears, as desired. We will now add data fields that will be useful within our practice report.

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

21.  Expand the Calendar folder that appears within the Date dimension.

22.  Expand the Calendar hierarchy within the Calendar folder.

23.  Drag Calendar Year into the Data pane, dropping it to the left of the Sales Territory Group column, as shown in Illustration 12.

Illustration 12: Adding Calendar Year to the Dataset

The new Calendar Year column appears, as desired.

24.  In like manner, drag Calendar Quarter into the Data pane, dropping it between the Calendar Year and Sales Territory Group columns.

25.  Similarly, drag Month into the Data pane, dropping it between the Calendar Quarter and Sales Territory Group columns.

26.  Drag Date into the Data pane, dropping it between the Month and Sales Territory Group columns.

The newly added columns in the Data pane appear as partially depicted in Illustration 13.

Illustration 13: The New Columns in the Data Pane (Partial View)

Having made these changes on the Data tab, we are ready to move to the Layout tab, where we will perform further modifications to the report file.

27.  Click the Layout tab, as shown in Illustration 14.

Illustration 14: Click the Layout Tab

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

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

30.  Select Delete from the context menu that appears, as depicted in Illustration 15.

Illustration 15: Delete the Value for Internet Total Product Cost

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

The gray column and row bars (the “handles”) disappear, as a light, patterned outline forms around the Matrix data region, and the context menu appears.

32.  Select Properties from the context menu, as shown in Illustration 16.

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

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

33.  Click the Groups tab.

34.  In the Rows section of the Groups tab (in the upper half of the tab), select the sole group, named matrix1_Sales_Reason.

35.  Click the Edit button, as depicted in Illustration 17.

Illustration 17: Editing the Matrix1_Sales_Reason Row Group ...

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

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


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

38.  Select =Fields!Calendar_Year.Value within the selector, as shown in Illustration 18.

Illustration 18: Replacing the Existing Group Expression ...

39.  Click OK, to accept our modifications, and to close the Grouping and Sorting 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