|
Featured Database Articles
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
|
» 