Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 27, 2004

MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts - Page 7

By William Pearson

The Dataset dialog, Query tab appears as depicted in Illustration 27.

Click for larger image

Illustration 27: Completed Dataset Dialog - Query Tab

10.  Click OK to accept our input and close the Dataset dialog.

We are returned to the design environment, Data tab, where we see the new MDX query appear in the Query view in the upper section of the tab.

11.  Execute the query by clicking the Run button, as shown in Illustration 28.


Illustration 28: Execute the Query with the Run Button ....

The query executes, and returns the dataset depicted in Illustration 29.


Illustration 29: The Returned Dataset

Having created the first of four datasets, we follow the same process with three additional datasets, one for each drilldown level of the Store hierarchy.

12.  Create the following datasets:

  • ds_pX_State
  • ds_pX_City
  • ds_pX_Store

using the corresponding details contained within Tables 2, 3 and 4 below.

In this Dialog Box:

Type the following:

Name:

ds_pX_State 

Data source:

FoodMart 2000

Command type:

Text

Query string:


-- RS007-2 MDX Query to Support State Picklist

WITH

MEMBER

[MEASURES].[State_Qualified_Name] 

AS

'[Store].CurrentMember.UniqueName'

MEMBER

[MEASURES].[State_Report_Name] 

AS

'[Store].CurrentMember.Name'

SELECT 

{[MEASURES].[State_Qualified_Name],
 
   [MEASURES].[State_Report_Name]}  ON AXIS(0),

{[Store].[Store State].Members} ON AXIS(1)

FROM

[Sales]
Table 2: ds_pX_State Dataset Details

 

In this Dialog Box:

Type the following:

Name:

ds_pX_City 

Data source:

FoodMart 2000

Command type:

Text

Query string:


-- RS007-3 MDX Query to Support City Picklist

WITH

MEMBER

[MEASURES].[City_Qualified_Name] 

AS

'[Store] .CurrentMember.UniqueName'

MEMBER

[MEASURES].[City_Report_Name] 

AS

'[Store].CurrentMember.Name'

SELECT 

{[MEASURES].[City_Qualified_Name],
 
   [MEASURES].[City_Report_Name]}  ON AXIS(0),

{[Store].[Store City].Members} ON AXIS(1)

FROM

[Sales]
Table 3: ds_pX_City Dataset Details

 

In this Dialog Box:

Type the following:

Name:

ds_pX_Store 

Data source:

FoodMart 2000

Command type:

Text

Query string:


-- RS007-4 MDX Query to Support Store Picklist

WITH

MEMBER

[MEASURES].[Store_Qualified_Name] 

AS

'[Store] .CurrentMember.UniqueName'

MEMBER

[MEASURES].[Store_Report_Name] 

AS

'[Store].CurrentMember.Name'

SELECT 

{[MEASURES].[Store_Qualified_Name],
 
   [MEASURES].[Store_Report_Name]}  ON AXIS(0),

{[Store].[Store Name].Members} ON AXIS(1)

FROM

[Sales]
Table 4: ds_pX_Store Dataset Details

Be sure, once you have added each dataset, to execute it via the Run button, as we did for ds_pX_Country above, to populate the fields of the dataset and to ensure accuracy of query input. Once the datasets are in place and have been executed, click the Fields tab, and choose each to verify its contents, from the selector shown in Illustration 30.


Illustration 30: Datasets Appear on Fields Tab

TIP:

In setting up picklist datasets for Reporting Services, I often like to leverage MSAS and take advantage of the cube structure to store the definitions instead of building the datasets as shown. This can be done through the creation of calculated members in MSAS, which are constructed to contain the MDX queries that define the datasets. The obvious benefit is the always-welcome reusability factor; if we create calculated members to contain queries that we reference frequently in our reports, we need only reference each member after typing the code once.

This also promotes consistency, and helps us to avoid errors. Named sets can act as a good complement to this approach, allowing us to go a step further and group the calculated members into a single reference that we can pull into a report axis easily. Other, perhaps less obvious benefits can also accrue to the innovative user. (This is another example of a technique that can be accomplished within the MSAS / Reporting Services combination, but which would be difficult or impossible in other enterprise OLAP reporting packages).

For examples along these lines, see my Database Journal article Create a Cube-Based Hierarchical Picklist, which focuses on the MSAS side of the subject.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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