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 27, 2004

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

By William Pearson

Add Datasets and Parameters

The FoodMart 2000 Sales report that we have modified is supported by a dataset, called ProductData, which is generated by an MDX query.

1.  Click the Data tab of the Report Designer.

2.  If necessary, select ProductData in the dataset selector just underneath the Data tab.

The MDX query appears, as depicted in Illustration 21.

Click for larger image

Illustration 21: The MDX Query Underlying the ProductData Dataset

We would almost certainly remove the Cost measure from the query, as well as the Profit calculated field from the Fields tab (located either atop the report, or within the Dataset dialog of the ProductData dataset), but we will leave all in place for now for purposes of our practice exercise. (Were this a real world report, there are several items that I would modify / eliminate, but they will not interfere with our present purposes).

3.  Add the following WHERE clause at the end of the MDX query:

WHERE ([Store].[All Stores].[USA].[OR].[Portland].[Store 11])

The modified query appears in the Query view of the Dataset tab as shown in Illustration 22.

Illustration 22: The MDX Query with our Added WHERE Clause

In adding the WHERE clause, we have sliced the cube by a member of the Store dimension. Although we have been asked to create a parameter that allows information consumers to specify the identity of the store upon which they wish to slice the data at runtime, for the time being we are getting a working, "hard-coded" query into place that does what we wish, before adding parameters.

This is a good practice, as we shall see, because once we add parameters into the mix, we lose the flexibility of being able to generate datasets. Furthermore, to create a cascading picklist scenario, we will need to create a dataset for each level of "drilling" involved in getting to the lowest level member, an individual store, in our present case. Portions of the results are passed from one to the next, with each passed component acting as the parameter for the next subordinate dataset filter. The output of the lowest level dataset, the individual store in our case, forms the "qualified," unique MDX name that is then fed to the WHERE clause of the core dataset. The flow of the process conceptually resembles that depicted in Illustration 23.

Illustration 23: Cascaded Datasets "Assemble" the "Qualified" Name ...

As we can see in the illustration above, we will require four separate datasets, in addition to the core dataset (labeled in the illustration), to which we will return after assembling the additional datasets. The core dataset will be modified to insert the parameter output of the added datasets to the WHERE clause of the core dataset. While the order in which the components are created is not rigidly dictated, we will do them in order of intended operation, in an effort to make the steps involved more memorable, and their overall integration in the report clearer.

First, let's clean house further and delete the old parameter picklist dataset, ProductList.

4.  Click the Data tab, if necessary.

5.  Select the ProductList dataset in the Dataset selector atop Data tab.

The MDX query for the ProductList dataset appears in the query view.

6.  Click the Delete icon once ProductList is selected, as shown in Illustration 24.

Illustration 24: Select and Delete the ProductList Dataset

7.  Click Yes on the confirmation message box that next appears, as depicted in Illustration 25.

Illustration 25: Click Yes to Confirm Deletion ....

The ProductList dataset disappears. We are ready to begin the creation of our cascading picklist datasets at this point.

8.  Select <New Dataset ...> in the Dataset selector, as shown in Illustration 26.

Illustration 26: Select <New Dataset> ....

The Dataset dialog appears, defaulted to the Query tab.

9.  For the dialog boxes shown in Table 1, type the corresponding items:

In this Dialog Box:

Type the following:



Data source:

FoodMart 2000

Command type:


Query string:

-- RS007-1 MDX Query to Support Country Picklist





'[Store] .CurrentMember.UniqueName'






   [MEASURES].[Country_Report_Name]}  ON AXIS(0),

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


Table 1: ds_pX_Country Dataset Details

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