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 Nov 20, 2006

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

By William Pearson

Preparation: Modify the OLAP Report for Use within Our Practice Session

We will next make a few modifications to prepare the report for our practice session. Our objective will be to begin the session with a simple OLAP report that contains no Parameters, and which is fitted with a simple filter. Let’s open the report in Layout view (for those of us not already there) and make the necessary settings to place it into a state upon which we can commence our practice steps.

1.  Right-click DBJ_OLAP_Report.rdl (or your own choice of a similar report) in the Solution Explorer.

2.  Select Open from the context menu that appears, as shown in Illustration 1, as necessary.

Illustration 1: Opening the New Report...

DBJ_OLAP_Report.rdl opens in Layout view.

We will start with the Data tab.

3.  Click the Data tab.

We enter the Data tab, where we will remove a handful of components that we do not need for our practice session. We will accomplish this from the perspective of the MDX Query Builder, the main components of which are labeled in Illustration 2 below.

Illustration 2: The MDX Query Builder

4.  With the Dataset selector, select the ProductList dataset, as depicted in Illustration 3.

Illustration 3: Select the ProductList Dataset...

5.  Once the Dataset loads, click the Delete button, as shown in Illustration 4.

Illustration 4: Deleting the Unneeded Dataset...

6.  Click Yes on the Microsoft Report Designer warning message dialog that appears next, as depicted in Illustration 5.

Illustration 5: Confirm Intention to Delete...

The primary (and sole remaining) dataset, ProductData opens.

7.  Within the Query pane of the MDX Query Builder, right-click the column heading for Internet Total Product Cost.

8.  Click Delete Internet Total Product Cost from the context menu that appears, as shown in Illustration 6.

Illustration 6: Deleting the Unwanted Measure from the Dataset

9.  Within the Calculated Members pane, right-click the sole Calculated Member, Profit.

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

Illustration 7: Deleting the Calculated Member

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

12.  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.

13.  Within the Metadata pane, expand the Date dimension, by clicking the "+" sign to its immediate left.

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

15.  Expand the Fiscal hierarchy within the Fiscal folder.

16.  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.

We will now add in a simple filter, to make our report a bit more manageable for the practice session.

17.  Click and drag the Fiscal hierarchy into the leftmost box (currently containing the phrase "<Select dimension>"), of the bottom row of the Filter pane, as shown in Illustration 10.

Illustration 10: Adding the Date.Fiscal Hierarchy to the Filter Pane

18.  Click the Filter Expression box within the same row of the Filter pane to enable the selector.

19.  Click the downward arrow of the Filter Expression selector to expose the selection tree.

20.  Expand All Periods by clicking the "+" sign to its immediate left.

21.  Select FY 2004 and FY 2005 by checking the boxes to the left of each, as depicted in Illustration 11.

Illustration 11: Select FY 2004 and FY 2005 as the Filter Expression

22.  Click OK to accept our settings, and to close the selector.

The newly added row in the Filter pane appears, as shown in Illustration 12.

Illustration 12: Our New Entry in the Filter Pane

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.

23.  Click the Layout tab, as depicted in Illustration 13.

Illustration 13: Click the Layout Tab

24.  On the Layout tab, within the Matrix Data Region, select the value appearing underneath the Internet Total column heading.

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

26.  Select Delete from the context menu that appears, as shown in Illustration 14.

Illustration 14: Delete the Value for Internet Total Product Cost

27.  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.

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