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
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!
Chris,I already posted this on your "Rant: Reporting Services and Analysis Services" blog entry but thought I should duplicate…After a little tinkering I discovered that if you create a datasource in RS2005 of type OLE DB then choose the provider as "Microsoft OLE DB Provider for Analysis Services 9.0", the functionality from RS2000 is preserved.. Yay!I imagine you would have already worked this out but for all those who come across this blog in the future….
Oops… just realised that you were well aware of this since you say "you can still get the glorious AS/RS2K experience if you use an OLEDB connection instead along with the AS OLEDB Provider"Please ignore previous comment 🙂
Can you do this for the rows axis of a query?
Gabe, do you mean have a parameter which controls which dimension appears on the rows axis? If so, then yes, that should be possible. I\’m a bit pressed for time today (and for the rest of this week), but here\’s roughly what the MDX would look like:
WITH
SET MYROWS AS STRTOSET("[Date].[Day Name]" + ".MEMBERS")
MEMBER MEASURES.DEMODIM AS
MYROWS.ITEM(
RANK([Date].[Date].CURRENTMEMBER, [Date].[Date].MEMBERS)-1
).ITEM(0).UNIQUENAME
MEMBER MEASURES.DEMOMEASURE AS
(MEASURES.[INTERNET SALES AMOUNT], STRTOMEMBER(MEASURES.DEMODIM), [Date].[Date].[All Periods])
SELECT {MEASURES.DEMODIM, MEASURES.DEMOMEASURE } ON 0,
HEAD(
[Date].[Date].MEMBERS
, COUNT(MYROWS)
)
ON 1
FROM [Adventure Works]
You\’d parameterise the string containing the name of the dimension/hierarchy you wanted to see in your report, and make sure you had put the dimension/hierarchy with the most members in your cube on rows. I\’ll think about this problem a bit more and maybe do a full blog entry next week…
Thanks a lot for thius suggestion. I\’ve implemented in a recent project and was very helpfull !!!
Aser
Hi Chris, tried to use the "Microsoft OLE DB Provider for Analysis Services 9.0" datasource to put measures on rows for a work report. It kind of works – except that it treats all the measures as a single Field of the Dataset. By that, I mean you can\’t format the measures individually and as I had a mixture of money, counts and percentages it gave me a problem. Good stuff though, I also used the measures drop down in another report.
Sean.
Wonderful article!!!Exactly what I was looking for.Sam