1

I have a list of stores that provide to me their sales and transaction data. I want to do some reporting on these stores using Mondrian. Let's say I want to populate the column axis with the number of Sales by Month and then the number of Transactions by Year, where store locations are listed on the row axis. If I simply add the Sales measure to the column axis, it will apply to both the Month and Year levels. The same goes for the Transactions measure. Is there a way I can apply the Sales measure only to the Month level and the Transactions measure only to the Year level?

TheBeefMightBeTough
  • 13
  • 8

1 Answers1

1

Yes, you can put a union of two cross joins on the columns, e. g.

SELECT { [Measures].[Sales] } * [Date].[Date Hier].[Month].Members
       +
       { [Measures].Transactions] } * [Date].[Date Hier].[Year].Members
       ON COLUMNS,

       NON EMPTY
       [Store].[Store location].Members
       ON ROWS
  FROM [YourCube]

The + is an abbreviation for the Union function, like * is an abbreviation for the CrossJoin function. You have to take care that both sets that you Unionize have the same dimensionality, but this is the case here.

FrankPl
  • 13,205
  • 2
  • 14
  • 40
  • This is pretty awesome. It solves a major issue of mine. Offhand, do you know if olap4j is capable of this or if I need to use mdx directly? I have an API that uses olap4j's include/exclude selection operations to construct mdx, but it seems pretty limited. – TheBeefMightBeTough Jul 08 '14 at 17:55
  • @TheBeefMightBeTough I have no idea, I never used the olap4j query builder. By the way: If my answer resolved your question, you should mark it as the correct answer by clicking the checkmark beside it. – FrankPl Jul 09 '14 at 08:16