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 Relative Dimension Time Parent [All Member Relative Time] Member Quarter-to-Date Name Total Value SUM(PeriodsToDate([Time].[Quarter]),([Relative Expression 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)

29.  Click OK.

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 Relative Dimension Time Parent [All Member Relative Time] Member Prior Name Year Total Value ([Measures].CurrentMember, ParallelPeriod Expression ([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)

33.  Click OK.

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 Relative Dimension Time Parent [All Member Relative Time] Member Prior Name Quarter Total Value ([Measures].CurrentMember, ParallelPeriod Expression ([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)

37.  Click OK.

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 Relative Dimension Time Parent [All Member Relative Time] Member Prior Month Name Total Value ([Measures].CurrentMember, ParallelPeriod ([Time].[Month], Expression 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)

41.  Click OK.

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.