SSAS - Show Measures on Rows

Question

I am very new to MDX and SSAS in general. I am trying get all measures and dates in a nice tabular form. So something like:

╔═════════╦══════╦═══════╗
║ Measure ║ Date ║ Value ║
╠═════════╬══════╬═══════╣
║  Meas1  ║   D1 ║     3 ║
║  Meas1  ║   D2 ║     8 ║
║  Meas1  ║   D3 ║     9 ║
║  Meas2  ║   D1 ║     7 ║
║  Meas2  ║   D2 ║     4 ║
╚══...════╩═══..═╩═══...═╝

I thought of doing a cross join on the rows axis, but not sure what to put on the columns axis.

Any suggestions?

Answer 1

You can put the default member of any hierarchy that you are not using otherwise on the columns axis. If you have in your cube e. g. a hierarchy [Geography].[City], which has the default member [Geography].[City].&[All], your query could look like this:

SELECT [Geography].[City].&[All]
       ON COLUMNS,
       { [Measures].[Meas1], [Measures].[Meas2] }
       *
       { [Date].[Month].[D1], [Date].[Month].[D2], [Date].[Month].[D3] }
       ON ROWS
  FROM [YourCube]

Actually, the problem is that the axes in MDX have to be in the correct order, and no axis may be in a query if not all the axes before are contained. And as the columns axis is the second (numbered 1, as the numbering starts at 0 with the columns axis), the columns axis must be there. So the problem is that you must put an non empty set into the columns axis. And as there is always an implicit slicer axis containing the default members (which normally are the All members) of all hierarchies not mentioned in the query, the idea of this solution is to move any arbitrary of these to the columns axis to make it an non empty set without altering the result - which would have happened if you would have used e. g. [Geography].[City].Members, as then the values would have been shown broken down by cities.

If you would have wanted to put the measures and dates into the columns axis, this would have been much more easy, as this is the first axis. Then the query could just have been:

SELECT { [Measures].[Meas1], [Measures].[Meas2] }
       *
       { [Date].[Month].[D1], [Date].[Month].[D2], [Date].[Month].[D3] }
       ON COLUMNS
  FROM [YourCube]

And if you would not have needed the values, you could have used an empty set on the columns like this:

SELECT {}
       ON COLUMNS,
       { [Measures].[Meas1], [Measures].[Meas2] }
       *
       { [Date].[Month].[D1], [Date].[Month].[D2], [Date].[Month].[D3] }
       ON ROWS
  FROM [YourCube]

But then you would just have had the row headers, and nor value column in the columns.

Note that a query can have zero, one, two, three, or more axes from the conception of MDX. Just most client tools including SSMS have difficulties displaying result sets with more than two axes. But this is a restriction of the client tools,not of MDX.