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

MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist - Page 8

By William Pearson

"Parameterizing" the Core Dataset

We began the design of our report with a simple, unparameterized core dataset. We can now logically approach the parameterization of the core dataset, with everything in place that will be required to make it work. While the handling of parameterization of our MDX query takes some getting used to, the important thing to remember is that the objective is simply to supply a precise MDX string, to pass to MSAS from Reporting Services.

This requires only a bit more effort than coding the MDX we have used to create the original dataset; the clumsiness comes with the introduction of the string by which we pass the MDX / parameter combination. In essence, we implant the parameter, and then turn the existing MDX into a string. While it works fine in passing the code to MSAS, converting the query to a string disables dataset refreshment.

Let's revisit our initial query, through which we have made the soon-to-be-inhibited designer aware of all the fields we expect to require from the dataset. Precision is important in the next steps, particularly with regard to the absence of carriage returns (pressing the Enter key to "push down" a line), and other stray actions.

1.  On the Data tab, select the FoodMart 2000 dataset in the Dataset selector, as shown in Illustration 35.

Illustration 35: Select the FoodMart 2000 Dataset Again ...

2.  Modify the original FoodMart 2000 dataset query to precisely the following (cut and paste, if helpful):

 ="SELECT {[Measures].[Amount]} ON COLUMNS,
{[Account].[All Account].[Net Income].[Total Expense]} ON ROWS FROM
[Exp_Finance] WHERE(" +Parameters!px_LocationHier.Value +")"

NOTE: Type as if a single line, without "artificially" creating line breaks via the Enter key, etc.

An explanation of the components of the above expression appears in Table 2.




The = sign alerts Reporting Services that an expression is to follow. The expression must be enclosed in double quotes.

{[Measures].[Amount]} ON COLUMNS, {[Account].[All Account].[Net Income].[Total Expense]} ON ROWS FROM [Exp_Finance]

The corpus of our initial query, sans the store / location dimension component. We will be setting up the capability to filter by geographic location via our hierarchical picklist; because this means that the "qualified" geography dimension will be specified in the WHERE clause (by its nature as a filter), we will remove the reference to the same store location information here.

WHERE(" +Parameters!px_LocationHier.Value +")"

The WHERE clause, a staple of any MDX query (whether explicit or not), enclosing the reference to the value of the parameter - in the present case, the value of the location parameter from the hierarchical picklist, stored in the "qualified" format, which therefore "completes" the WHERE clause.

The final set of double quotes (") is vital, as it signals "end of the expression" to MSAS.

Table 2: Parameterized Expression Components Summary

The Query pane, containing our modified input, appears as shown in Illustration 36.

Illustration 36: MDX Query in the Query Pane

NOTE: The Run icon is disabled, as depicted above, once the query is enclosed in double quotes.

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