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

Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header - Page 4

By William Pearson

Preparation: Enhance the Report per the Business Requirements

As we noted in the Objective and Business Scenario section above, the authors / developers with which we are working have outlined a few enhancements that they wished to make to the report clone, to outfit it to meet a specific business need that is different than the need addressed by the original report. Let's make these changes, before addressing an approach to adding the previously problematic Dataset field to a Page Header, which we will also add to the report.

1.  Right-click RS025_Dataset Field in Header.rdl in the Solution Explorer.

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

Illustration 19: Opening the New Report ...

RS025_Dataset Field in Header.rdl opens in Layout view, and appears as depicted in Illustration 20.

Click for larger image

Illustration 20: Our Report Opens in Layout View ...

Let's preview the report, so as to get a feel for its general operation prior to performing our enhancements.

3.  Click the Preview tab to execute RS025_Dataset Field in Header.

Execution begins (the report initially executes with the default parameter setting).

The report executes, and appears as shown in Illustration 21.

Illustration 21: The Report Appears with Default Parameter Selection

We are now positioned to make modifications to the report to support the expressed business requirements. To do so, we will first go to the Data tab, and the MDX Query Designer, where we will make additions to the query to bring in newly required data elements.

4.  Click the Data tab.

The MDX Query Designer appears, with the existing query in place, as depicted in Illustration 22.

Illustration 22: The MDX Query Designer

5.  Within the Metadata pane for the Adventure Works cube, locate the Sales Territory dimension.

6.  Expand the Sales Territory dimension by clicking the "+" sign to its immediate left.

7.  Drag the Sales Territory Country Attribute Hierarchy to the Results pane, dropping it between the existing Sales Territory Group and Internet Order Quantity columns, as shown in Illustration 23.

Illustration 23: Placing the Sales Territory Country Attribute Hierarchy Item ...

NOTE: A red line appears to indicate the drop point for the data element.

8.  Within the Metadata pane, as before, locate the Sales Reason dimension.

9.  Expand the Sales Reason dimension by clicking the "+" sign to its immediate left.

10.  Drag the Sales Reason Type Attribute Hierarchy to the Results pane, dropping it to the left of the existing Sales Reason column (making Sales Reason Type the left-most column in the pane).

11.  Click the Refresh Fields button, within the toolbar, to ensure that the Dataset fields are refreshed within the Datasets pane. We should see both Sales Territory Country and Sales Reason Type appear in the pane upon refreshment, as depicted in Illustration 24.

Illustration 24: Refreshing the Dataset ...

NOTE: If the Datasets pane is not in evidence, we can call it by selecting View --> Datasets from the main menu.

Having made the Dataset adjustments, we will now modify the query parameter default within the Filter pane.

12.  Click the Filter Expression column within the Filter pane (the top pane in the MDX Query Designer), to enable the selector button, as shown in Illustration 25.

Illustration 25: Enabling the Selector within the Filter Expression Column ...

13.  Click the downward pointing selector arrow.

The multi-value picklist appears, as depicted in Illustration 26.

Illustration 26: The Picklist Appears for the Product Category Parameter ...

14.  Select the All Products checkbox within the selector, unchecking any others, as depicted in Illustration 27.

Illustration 27: Selecting All Products as the Default for the Parameter ...

15.  Click OK to accept changes, and to close the Filter Expression column selector.

The Filter Expression column of the Filter pane appears, reflecting our change, as shown in Illustration 28.

Illustration 28: The Filter Expression Column with Default of "All Products"

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