If you’ve been reading this blog for a while, you probably know that I’m not the
greatest fan of the way that support for MDX has been implemented in RS2005. Anyway, here’s a little tip that might soften the pain…
In RS2005 you are always forced to put the Measures dimension on the columns axis in your queries if you’re using a data source of type ‘Microsoft SQL Server Analysis Services’ (you can still get the glorious AS/RS2K experience if you use an OLEDB connection instead along with the AS OLEDB Provider). While this is irritating at the best of times, it does have one serious consequence: how can you parameterise a query by Measures? It doesn’t look like the RS dev team thought this would be a valid scenario, but in fact in my experience there are plenty of times where you do want to do it. It is possible though, and here’s a solution:
First of all, you need to create a query that will give you a list of measures on rows. The following AdventureWorks example shows how to do this:
WITH
SET MYSET AS HEAD([Date].[Date].[Date].MEMBERS, COUNT(MEASURES.ALLMEMBERS)-1)
MEMBER MEASURES.MeasureUniqueName AS
MEASURES.ALLMEMBERS.ITEM(
RANK(
[Date].[Date].CURRENTMEMBER, MYSET
)-1
).UNIQUENAME
MEMBER MEASURES.MeasureDisplayName AS
MEASURES.ALLMEMBERS.ITEM(
RANK(
[Date].[Date].CURRENTMEMBER, MYSET
)-1
).NAME
SELECT {MEASURES.MeasureUniqueName, MEASURES.MeasureDisplayName} ON 0,
MYSET ON 1
FROM [Adventure Works]
It relies on there being a level on a hierarchy somewhere in your cube that has more members on it than there are measures, so it’s a bit of a hack, but this isn’t a problem most of the time and is the easiest way of solving the problem in MDX. You just create a set with the same number of members in as you have measures, put that on rows in your query, and then using calculated measures return the name/unique name of the measure which has the same rank in the set Measures.AllMembers as the currentmember on rows (Date.Date in this case) has in that set.
After you’ve created a new report parameter tied to this resultset you can create the query you want to parameterise. Once again the need to have measures on columns needs to be worked around – this time you need to create a calculated measure in your WITH clause, put that on columns, and then parameterise the definition of that calculated measure. Here’s an example:
WITH
MEMBER MEASURES.SELECTEDMEASURE AS STRTOMEMBER(@MyMeasure, CONSTRAINED) SELECT NON EMPTY { MEASURES.SELECTEDMEASURE} ON COLUMNS, NON EMPTY { ([Date].[Calendar Year].[Calendar Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE
Before this will work though, you need to declare the query parameter used by this query. To do this, click on the ‘query parameters’ button in the toolbar above where your MDX appears (it looks like an @ symbol overlaid on a table) and then fill in the following values:
- Parameter as the name of your parameter, without the leading @. So in our example it would be MyMeasure
- Dimension needs to be left blank – the Measures dimension doesn’t appear on the dropdown list
- Hierarchy needs to be blank too
- Multiple Values needs to be left unchecked
- Default must be entered, but you can use an expression such as MEASURES.DEFAULTMEMBER
Thanks go to my colleague Tony for working this last bit out.
You can then bind this query to a table in your report and select different measures to slice by. Hope this helps!
