Search code examples
ssasmdxcube

transpose mdx results => values are lost

I created an MDX query with some named calculus (using 'WITH' keyword). The last part is:

SELECT
{[Measures].[PCT0p02],[Measures].[PCT0p2],[Measures].[PCT0p5],[Measures].[PCT0p8],[Measures].[PCT0p98]} on 0
FROM [My cube]

It gives me this:

the result

I would like to transpose these results in order to feed an SSRS report.

But write

SELECT
{     } on 0,
{  [Measures].[PCT0p02],[Measures].[PCT0p2],[Measures].[PCT0p5],[Measures].[PCT0p8],[Measures]. 
[PCT0p98] } on 1
FROM [My cube]

returns

bad result

Please tell me how not to lose the value

EDIT: The answer given works, but when I try to use it in SSRS I get an error: it complains that a measure is needed in the columns axis: problem in SSRS

Solution

  • You need to have something in your 0 axis to satisfy MDX, however you dont want it to modify your result. Defaultmember will help with this.. Example lets say you have a dimension DimA, within DimA you have an attribute AT1, then your query will be

    SELECT
{  DimA.AT1.Defaultmember   } on 0,
{  [Measures].[PCT0p02],[Measures].[PCT0p2],[Measures].[PCT0p5],[Measures].[PCT0p8],[Measures]. 
[PCT0p98] } on 1
FROM [My cube]