Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 24, 2004

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

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date