MDX Query to return data from the same Measure in different columns

Question

I have an MDX query running againts a SSAS cube that looks like this

select non empty crossjoin([A].[B].[C], [A].[B].[D], [A].[B].[E]...) on rows,
[Measures].[Balance at Year End] on columns
from [Finance]
where [Date].[Fiscal].[Year].&[2015]

And it returns the list of departments by hierarchy and their year end balances.

I am trying to add the Balance at the end of 2014 and 2013 as another columns in the same table and can't seem to figure out the correct syntax for that.

where {[Date].[Fiscal].[Year].&[2015],[Date].[Fiscal].[Year].&[2014],[Date].[Fiscal].[Year].&[2013]}

returns me a summed column. I am using the SQL Server Management studio to run the queries and later plan to put it into Excel using the hack from http://blog.crossjoin.co.uk/2009/12/18/binding-an-excel-table-to-the-results-of-an-mdx-query/

Thanks

Mike Mike · Accepted Answer · 2016-01-27T20:40:38

So I've found a way to get this to work by doing a crossjoin on the Balance for the columns.

 crossjoin([Measures].[Balance at Year End],{[Date].[Fiscal].[Year].&[2016],[Date].[Fiscal].Year.&[2015],[Date].[Fiscal].[Year].&[2014]}) on columns

MDX Query to return data from the same Measure in different columns

1 Answers