Better MDX Query in 2005?

I've worked on several analysis services and reporting services solutions but this has me stumped. I have an X,Y scatter chart in RS that needs to hit a cube. So I need an MDX query that returns something like this:

DiseaseAbrev Pathway Feasibility
ADD 2.7 1.9
XS 4.0 1.0
YYY 1.4 2.0

The goal is to have two columns, Pathway and Feasibility, that contain a weighted measure that is calculated by summing a set of other measures times a weighting factor. The Disease Score Type dimension has a parent-child hierarchy set up so that Pathway and Feasibility are two members who roll up the weighted leaf members. When applied to the Score measure (see query below) this gives me what I want.

SELECT {[Disease Score Type].[Parent Id].&[2],[Disease Score Type].[Parent Id].&[1]} ON COLUMNS,
[Disease].[Disease Abbrev].[Disease Abbrev] ON ROWS
FROM [IVDB]
WHERE ([Measures].[Score], [Disease Selection].[Short List].&[Short List])

But, and this is a big problem, Reporting Services requires measures and ONLY measures in the first axis. So this does not work, which is really frustrating since this is really the most elegant way of modelling the problem domain. How can I write a better query that returns a Pathway and a Feasibility column containing weighted sums of the Score measure for just those two dimension members?

Can someone please help me?

There is nothing really wrong with your MDX, it is an unreasonable limitation of Reporting Services which has been raised by a few people, including fellow MVP Chris Webb here: http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!163.entry

A work around is to create a an OLE DB datasource in RS2005 then choose the "Microsoft OLE DB Provider for Analysis Services 9.0" provider, you don't get the fancy designer and have to enter your MDX by hand, but you can structure your query any way you like.

|||

Thank you! The work around is fine for this report (and the dozen or so left to do...). I don't need the Query Builder in Reporting Services since I usually wind up writing the MDX directly to get what I want. And if I want a fancy designer the one in SQL Management Studio works very well. The only thing you really give up this way is direct support for parameters etc - but I can use the same tricks as the 'bad-old-days' of Analysis Services 2000 where you could fake parameters by using string functions to build the MDX!

It's just too bad that 2005 has a set of restrictions that make it's new features useless for anything but the most trivial MDX.

Thank you again!

Micah

|||

One way of working around the Analysis Services Provider limitation in this case is to use calculated measures, like:

With

Member [Measures].[PathwayScore] as

([Measures].[Score], [Disease Score Type].[Parent Id].&[2])

Member [Measures].[FeasibilityScore] as

([Measures].[Score], [Disease Score Type].[Parent Id].&[1])

SELECT {[Measures].[PathwayScore], [Measures].[FeasibilityScore]} ON COLUMNS,
[Disease].[Disease Abbrev].[Disease Abbrev] ON ROWS
FROM [IVDB]
WHERE ([Disease Selection].[Short List].&[Short List])