Grouping dimension members using mdx query on the fly with aggregation

Question

I am new to MDX and I just want to ask if it is possible in MDX query to make aggregations and groupings on the fly.

Here is the scenario, I have a dimension called "Department". And it has department code values e.g.

I also had a measure called "Sales".

What I need to do here is to make a Calculated Member that will get the Maximum "Sales" grouped per department based on the its first two digits. For example, consider the following output when browsing the cube using the Department dimension and Sales Measure

Department | Sales
1234       | 100
1257       | 200
1346       | 100
1390       | 400

Then I need to make an MDX query to produce an output something like below,

Department | Sales
12xx       | 200
13xx       | 400

You will notice that Maximum Sales based on the two digits of each department concatenated with "xx" string were the expected output.

DHN DHN · Accepted Answer · 2012-11-13T11:37:56

Well determining the maximum is not a problem. with <name> as max(<something>) but you should reconsider the approach with the on the fly grouping.

I'm sure, that it is achievable, although I cannot provide a solution, but it will perform poorly. (I'm assuming that the digits of the department are not implemented as measure)

If you need this grouping more often you should add an additional dimension, or better a hierarchy to the department dimension.

Grouping dimension members using mdx query on the fly with aggregation

1 Answers