# MDX: Aggregate [Date].[Calendar].[Quarter] level to create new [Date].[Calendar].[Semi Annual] Level

0

I have a Date Dimension that has Month Hierarchy

``````Year -> Quarter -> Month -> Day -> Hour
``````

I have two measures. Measure_A (Aggregation is Max), Measure_B (Aggregation is SUM)

I need to Multiply Measure_A by 24 on "Day" level then I need to SUM it Semi Annually.

Is it possible to create a new hierarchy by just using an MDX query? Below is the output that i am trying to achieve

``````Year | CY H1 | Measure_C | Measure_B
``````

Where: `Measure_C` is the SUM of (Measure_A * 24) from January 1 to June 30, and `CY H1` will be the new level.

I don't have any problem with Measure_B because its aggregation is SUM. Only with Measure_A beacause the aggregation is Max.

Is this possible to achieve without altering the cube, just do an MDX? Thanks.

``````Updated:
``````

This is my query and output as of the moment,

Query:

``````With
MEMBER [Measures].[Measure_C] AS
SUM (   Descendants ( [Date].[Date Calendar],  [Date].[Date Calendar].[Day]), [Measures].[Measure_A] * 24 )
, Format_String = "#,##0"

Select
{
[Measures].[Measure_C]
} ON Columns,
NON Empty
{
[Date].[Date Calendar].[Quarter].ALLMEMBERS
} ON Rows
From [Cube]
``````

Output:

``````+------+---------+-----------+
| Year | Quarter | Measure_C |
+------+---------+-----------+
| 2011 | Q1      |        12 |
| 2011 | Q2      |        45 |
| 2011 | Q3      |        12 |
| 2011 | Q4      |        25 |
+------+---------+-----------+
``````

What I am trying to do is to merge Q1 and Q2 to get Calendar Semester for the first half (CY H1)

Required output:

``````+------+---------+-----------+
| Year | Half Yr | Measure_C |
+------+---------+-----------+
| 2011 | CY H1   |        57 |
| 2011 | CY H2   |        37 |
+------+---------+-----------+
``````

Please note that we don't have a natural Hierarchy for `CY H1`. But according to the comment below, this is not possible using mdx alone.

By the way, I am doing this for reporting services so I guess I will just do the magic in the presentation layer since this is not achievable using MDX query alone. :)

I am not sure what you mean by `Year | CY H1 | Measure_C | Measure_B`. Is this meant to be e. g. the column header? This is not possibly in MDX. You can only have members of the same hierarchy on the same level of the column headers.FrankPl
Yes you are right about the column headers, I just thought if it is possible to do in mdx. thanks for your comment :)ggarcia

1

Assuming that your year 2011 is named `[Date].[Date Calendar].`, then you can add calculated members as children of it like this:

``````With
MEMBER [Measures].[Measure_C] AS
Aggregate ( Descendants ( [Date].[Date Calendar],  [Date].[Date Calendar].[Day]),
[Measures].[Measure_A] * 24
)
, Format_String = "#,##0"
MEMBER [Date].[Date Calendar]..[CY H1] AS
Aggregate({ [Date].[Date Calendar].[Q1], [Date].[Date Calendar].[Q2] })
MEMBER [Date].[Date Calendar]..[CY H2] AS
Aggregate({ [Date].[Date Calendar].[Q3], [Date].[Date Calendar].[Q4] })

Select
{
[Measures].[Measure_C]
} ON Columns,
NON Empty
{
[Date].[Date Calendar]..[CY H1],
[Date].[Date Calendar]..[CY H2]
} ON Rows
From [Cube]
``````
0
``````With Member [Measures].[Measure C] as Sum( [Date].... : [Date].... ), [Measures].[Measure A] ) * 24