Search code examples
multidimensional-arrayssasmdx

SSAS MDX query result only 2 dimensional (matrix), can it be "3" (pivot table)?

I am facing a problem, but maybe it is a nature of MDX,

I have lets say on Rows different Product Models, and I want to see couple of measures but for more than one period.

Currently, as the results have matrix-nature, I am only able to either: Have Product Models on rows and measures on columns, but then I can see these results only for one period (WHERE condition) or:

I can have again Product Models on Rows and date dimension on Columns, but then, I only can see one measure (default measure, or the one specified in the WHERE condition),

Is there some possibility to get results more like a "pivot" nature, so that I can see all three dimensions (Product models, measures, date) in the same time? And purely with MDX expression.

Thank you very much

Solution

  • You can create multidimentional pivot tables with mdx by also corssjoining many different dimensions within an axis:

    to illustrate with your example:

    SELECT

[Measures].[Internet Sales Amount] * [Customer].[Customer Geography].[Country] ON 0,

[Product].[Model Name].[Model Name] * [Date].[Calendar].[Calendar Year]  ON 1
FROM [Adventure Works]

    Philip,