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
Elon's Blog 