At this point, we will create calculated members for the other relative
time aggregations that the information consumers have requested in their
business needs specifications. We will follow the steps we took in creating
the Year-to-Date Total for each of the additional relative time
members we need, basing our construction of the calculated members on the settings
presented in the steps and tables that follow.
22. Right-click the Calculated
Members folder in the tree, once again.
23. Select New
Calculated Member ... on the context menu that appears.
The Calculated
Member Builder opens.
24.
Create the Quarter-to-Date Total calculated member by completing
the fields of the Calculated Member Builder, with the settings presented
in Table 3 below.
|
Setting
|
Our Input:
|
|
|
|
|
Parent
Dimension
|
Relative
Time
|
|
Parent
Member
|
[All
Relative Time]
|
|
Member
Name
|
Quarter-to-Date
Total
|
|
Value
Expression
|
SUM(PeriodsToDate([Time].[Quarter]),([Relative
Time].[Relative Time].[Current]))
|
|
|
|
Table 4: Calculated
Member Builder Settings: Month-to-Date Total Calculated Member
The Calculated Member Builder appears, with our input, as shown in Illustration
59.
Illustration 59: The
Calculated Member Builder: Month-to-Date
Total (Compressed View)
The Calculated Member Builder closes, and the new Month-to-Date Total calculated
member joins the other members of the Calculated Members folder. Let's
create the three "comparative" calculated members requested by the
information consumers next.
30.
Right-click
the Calculated Members folder in the tree, once again.
31.
Select New
Calculated Member ... on the context menu that appears.
The Calculated
Member Builder opens.
32.
Create the Prior Year Total calculated member by completing
the fields of the Calculated Member Builder to with the settings
presented in Table 5 below.
|
Setting
|
Our Input:
|
|
|
|
|
Parent
Dimension
|
Relative
Time
|
|
Parent
Member
|
[All
Relative Time]
|
|
Member
Name
|
Prior
Year Total
|
|
Value
Expression
|
([Measures].CurrentMember, ParallelPeriod
([Time].[Year], 1, [Time].CurrentMember), ([Relative Time].[Relative
Time].[Current]))
|
|
|
|
Table 5: Calculated
Member Builder Settings: Prior Year Total Calculated Member
The Calculated Member Builder appears, with our input, as depicted in Illustration
60.
Illustration 60: The
Calculated Member Builder: Prior
Year Total (Compressed View)
The Calculated Member Builder closes, and the new Prior Year Total
calculated member joins the other calculated members in the tree, as before.
NOTE: For more information on the ParallelPeriod()
function, see my article
MDX Time Series Functions, Part III: The LastPeriods() and
ParallelPeriod() Functions in
the MDX Essentials series at Database Journal.
34.
Right-click
the Calculated Members folder in the tree, once again.
35.
Select New
Calculated Member ... on the context menu that appears.
The Calculated
Member Builder opens.
36.
Create the Prior Quarter Total calculated member by completing
the fields of the Calculated Member Builder, with the settings presented
in Table 6 below.
|
Setting
|
Our Input:
|
|
|
|
|
Parent
Dimension
|
Relative
Time
|
|
Parent
Member
|
[All
Relative Time]
|
|
Member
Name
|
Prior
Quarter Total
|
|
Value
Expression
|
([Measures].CurrentMember, ParallelPeriod
([Time].[Quarter], 1, [Time].CurrentMember), ([Relative Time].[Relative
Time].[Current]))
|
|
|
|
Table 6: Calculated
Member Builder Settings: Prior Quarter Total Calculated Member
The Calculated Member Builder appears, with our input, as shown in Illustration
61.
Illustration 61: The
Calculated Member Builder: Prior
Quarter Total (Compressed View)
The Calculated Member Builder closes, and we see Prior Quarter Total
appear in the Calculated Members folder within the tree. Let's create
the final calculated member, Prior Month Total, to support the
information consumers' expressed business requirements via the Relative Time
dimension.
38.
Right-click
the Calculated Members folder in the tree, as before.
39.
Select New
Calculated Member ... on the context menu that appears.
The Calculated
Member Builder opens.
40.
Create the Prior Month Total calculated member by completing
the fields of the Calculated Member Builder, with the settings presented
in Table 7 below.
|
Setting
|
Our Input:
|
|
|
|
|
Parent
Dimension
|
Relative
Time
|
|
Parent
Member
|
[All
Relative Time]
|
|
Member
Name
|
Prior Month
Total
|
|
Value
Expression
|
([Measures].CurrentMember, ParallelPeriod ([Time].[Month],
1, [Time].CurrentMember), ([Relative Time].[Relative Time].[Current]))
|
|
|
|
Table 7: Calculated
Member Builder Settings: Prior Month Total Calculated Member
The Calculated Member Builder appears, with our input, as depicted in Illustration
62.
Illustration 62: The
Calculated Member Builder: Prior
Month Total (Compressed View)
The Calculated Member Builder closes. We see the results of our handiwork within
the complete members within the Calculated Members folder that appears
in Illustration 63.
Illustration 63: The
Relative Time Calculated Members Set
We have
created the members of the Relative Time dimension required to support
the capabilities requested by the client information consumers. We will
conclude this exercise with a quick verification of the operation of these
structures in the section that follows. A subsequent article in my MSSQL Server Reporting Services series will
focus upon the use of the structures within an enterprise reporting
environment.
» 
Comment and Contribute
