Adding an MDX Query to a Reporting Services 2005 report and make it Parameter driven, using BIDS

Sometimes you may wish to create a SSRS 2005 report from an MDX query you have tested in SSMS 2005. This means you can create calculations on the fly,  that do not already exist in the cube, but are only need for a report.

The following steps outline how to (a) transfer your SSMS MDX Query into a SSRS 2005 Report, then (b) add a parameter to that report.

First, define your MDX Query in SSMS. The example MDX Query is against the “AdventureWorksDW” cube,  that you can download from Microsoft .

(Click on All images for full-size)

The MDX Query displays all Products for two measures, one of which, [VAT], is defined in the query as a calculated measure, for all Products.

/* Create a new Calculated */with member [Measures].[VAT] as [Measures].[Internet Sales Amount] * 1.175/* Display MDX Query */select { [Measures].[Internet Sales Amount] ,[Measures].[VAT]} on 0, non empty [Product].[Product].[Product] on 1 from[Adventure Works]

Next, Open BIDS and create a very simple SSRS report against the Cube. It’s not important at this stage what the query is, as it will subsequently be overwritten by our SSMS MDX Query. E.g.

Once defined, select the “Design Mode” button, shown above, to view the MDX applied.

Copy and paste the MDX from your SSMS window and execute the query to view results

Don’t forget to Refresh the dataset by Right-clicking on the Dataset and selecting “Refresh”

Next build your report in the “Layout” Tab

And then “Preview” it to see how it looks

OK, so that’s how to get your MDX query into a SSRS 2005 report.

Now let’s say we want to pick a particular group of products from the list.

We can alter our query with a MDX  “where” clause like this

(where [Product].[Product Model Lines].[Model Name].&[Mountain-100])

In SSMS, this runs as follows.

Pasted into BIDS it runs the same.

We will now create a Parameter for the Model Name as follows

From the BIDS view, select the “Query Parameters” button

Add a new Parameter and also enter a valid Default value, so you can press the OK button.

Next, Go to the Layout Tab and Add the parameter to the report

The “ModelName” parameter should already be there.

We need to edit the parameter “Available Values” to return the full list of Model Names

Within BIDS, let’s create a new Dataset called “ModelNames” which gets a list of valid Model Names from the Cube.

The MDX Query used is

select {} on 0, [Product].[Product Model Lines].[Model Name] on 1 from [Adventure Works] ;

This returns as follows.

Now, go back to The “Report Parameters” and edit the ModelName parameter as follows:

At this point the report displays the parameter, but it is not yet linked to the Report. E.g.

The report still returns values for the Default member model name,  “Mountain-100”

We will use a “strtomember” MDX function to pass the Model name to the MDX Query

So our where clause changes from

where [Product].[Product Model Lines].[Model Name].&[Mountain-100]

TO

where     strtomember(‘[Product].[Product Model Lines].[Model Name].&[‘ + @ModelName + ‘]’)

Notice the @ before the Parameter name to reference it in the MDX expression: @ModelName

Upon query execution, we see the values for “Mountain-100”

When we go to the Preview Tab to see the result, we can select the ModelName from the Drop down list and run the report accordingly as shown

The same principle can be used to add other parameters.

A short demonstration video of this can be downloaded from here