MDX in Analysis Services: Mastering Time: Change across Periods - Page 4

May 24, 2004

7.  Execute the query using the Run Query button.

The results dataset appears as shown in Illustration 3.


Illustration 3: The Results Dataset - Simple Time Members in Row Axis with Basic Measure

8.  Save the query as MXAS15-1.

This is about as simple as it gets, with the idea, again, to get the core Time levels enumerated and aligned in the row axis. Now let's add the components that are missing from the information consumers' specification: First and Second Half-Year aggregations.

9.  Within the query we have saved as MXAS15-1, replace the top comment line of the query with the following:

-- MXAS15-2:  Expanded Time Hierarchy (Adding Half-Year Aggregations) on Row Axis

10.  Save the query as MDX15-2, to preserve MDX15-1.

11.  Under the comment line we have just adjusted, and before the SELECT statement, add the following expression:


WITH
MEMBER 
     [Time].[1st Half-1997] 
AS  
    '[Time].[1997].[Q1] + [Time].[1997].[Q2]'
MEMBER [Time].[2nd Half 1997] 
AS 
    '[Time].[1997].[Q3] + [Time].[1997].[Q4]'

The purpose here is to create the two half-year aggregations, giving us the capability to present a "semi-annual" level within our Time row axis. Now, let's add the calculated members to the existing SELECT statement, in the appropriate positions for a logical presentation, by taking the following step:

12.  Under the following line within the SELECT statement of the existing query:

Time].[Year].[1997].[Q2],

and before the following line, already in place beneath it:

[Time].[Year].[1997].[Q3].Children,

insert the following expression:

[Time].[1st Half-1997],

We thus insert the first Half-Year aggregation at a logical point in the Time hierarchy in the rows axis. Now let's add the second semi-annual aggregation.

13.  Under the following line within the SELECT statement of the existing query:

Time].[Year].[1997].[Q4],

and before the following line, already in place beneath it:

[Time].[Year].[1997]} ON ROWS

insert the following expression:

Time].[2nd Half 1997],

The Query pane appears as shown in Illustration 4, with the inserted coding circled in red.


Illustration 4: The Query with Modifications Circled

14.  Execute the query using the Run Query button.

The results dataset appears, with new calculated members circled in red, as shown in Illustration 5.


Illustration 5: The Results Dataset - New Calculated Members Circled

15.  Save the query as MXAS15-2.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers