Parameterizing an MDX Query with a Report Parameter Value in Report Builder 2.0
While attempting to answer a forum post the other day, I discovered that creating a report against Analysis Services (AS) was not as easy as I had expected it to be. Simple reports using basic MDX are easy enough; the query designer does most of the work for you. But what if I want to create a report that contains a parameter with a set of available values that come from my data and uses the selected value in an MDX query to retrieve the data I want to display?
In the SQL Server case, the queries are simpler, and the query designer even helps me by recognizing query parameters in the query command text and prompting for values. In the AS case, queries can get pretty complex and query parameters can be anything: scalar values, strings that represent members, strings that represent sets, strings to be used in any number of MDX functions. This means that there is no easy way for the query designer to determine what to prompt for... so it doesn't prompt for any value, it simply tells you that your parameter is undefined. Not super helpful.
Until we add a complete MDX parser to the tool to help get past some of the difficulties metioned above, here is a sample of how you can get started:
First:
You'll want to create a dataset called OrganizationsQuery that you can use to use for your report parameter's available values (this is something you won't want to hard code since unique names in AS can be long and ugly). Your dataset's query might look something like this:
WITH
MEMBER OrgUniqueName as [Organization].[Organizations].CurrentMember.UniqueName
SELECT
NON EMPTY { [Measures].[OrgUniqueName] } ON COLUMNS,
NON EMPTY { (DESCENDANTS([Organization].[Organizations].[Organization Level 01].ALLMEMBERS) ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Adventure Works]
This will give you results that look like this:
Organizations OrgUniqueName
AdventureWorks Cycle [Organization].[Organizations].&[1]
European Operations [Organization].[Organizations].&[9]
France [Organization].[Organizations].&[11]
Germany [Organization].[Organizations].&[12]
etc...
Note: To build this query you will have to modify MDX directly. Use the right-most toolbar button to change to MDX editing mode.
Second:
Create a report parameter called @OrganizationsRP
-Set the Available Values to use the OrganizationsQuery dataset
-Set the Value to OrgUniqueName
-Set the Label to Organizations
Third:
Create a dataset called (FinancialAmountBySelectedOrganization):
SELECT
NON EMPTY { [Measures].[Amount] } ON COLUMNS,
NON EMPTY { strtoset(@OrganizationQP) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS
FROM [Adventure Works]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Note: To build this query you will have to modify MDX directly. Use the right-most toolbar button to change to MDX editing mode.
Executing this query from inside the query designer will not work yet; the query designer does not know what to use for @OrganizationQP. To fix this, click on the button that has the <'@'> symbol on it. Create a new query parameter called OrganizationQP (note that it should match the name used in your query, but without the @), and give it an appropriate dimension, hierarchy and default value(s). MDX query parameters need default values because the query needs to be executed in order to retrieve the schema (field names, etc.)
Finally:
Make sure that your dataset properties map the query parameter (OrganizationQP) to the report parameter [@OrganizationRP].
You should now be able to drop a table on to your report canvas, throw some fields from your FinancialAmountBySelectedOrganization dataset into it, and run the report. The table will only display results that match the parameters you select at view time.