Configuring Cumulative Total
Parameters 
Use
Use this screen to configure the parameters specific to the Cumulative Total template. See Cumulative Total (OLAP) for the description, business question, and workflow for using this template.
Procedure
...
1. In the Calculate section of the screen, select the measure for which you want to display a running total in the Running total of measure drop-down list. This list shows all the measures in the cube on which the query is based.
2. In the For Members Of section of the screen, select the level that contains the members you want to see in the query’s result using the parameters in the table below:
For Members Of Parameters
Parameter |
Values |
Action |
Dimension |
Lists all dimensions in the cube on which the query is based (see Selecting a Cube). |
Select the dimension that contains the hierarchy to display in the Hierarchy field. |
Hierarchy |
Lists all hierarchies in the currently selected dimension. |
Select the hierarchy that contains the level to display in the Level field. |
Level |
Lists all levels in the currently selected hierarchy. |
Select the level that contains the members you want to see in the query’s result. |
3. Configure the parameters in the For Date Range section of the screen according to the actions in the table below:
For Date Range Parameters
Parameter |
Values |
Action |
Time dimension |
Lists all the time dimensions in the cube on which the query is based. |
Select the dimension that contains the hierarchy to display in the Hierarchy field. |
Hierarchy |
Lists all hierarchies in the currently selected dimension. |
Select the hierarchy that contains the level to display in the Level field. |
Period level |
Lists all levels in the currently selected hierarchy. |
Select the level that contains the time granularity upon which to base the date range. |
From |
Lists all members in the currently selected level. |
Select the start period of the date range in which to cumulate the total. |
To |
Lists all members in the currently selected level. |
Select the end period of the date range in which to cumulate the total. |
4. If you want to restrict your display, check Display the last, and enter a numeric value to indicate number of periods.
5. Click Next.
Result
You proceed to the Define Filters screen.
Example
You want to show the cumulative total of store sales for certain machine parts. To create this query, configure the values on this screen in the following manner:
This example assumes you have similar types of metadata available in your data service.
Calculate section:
· Running total of measure = Store Sales
For Members of section:
· Dimension = Product
· Hierarchy = Product
· Level = Product Name
For Date Rangesection:
· Time dimension = Time
· Hierarchy = Time
· Period level = Month
· From = July
· To = December
Display the last = checked; set to 3 periods
The data used to cumulate the total, together with the result of this query (the yellow cells), are shown in the tables below:
Cumulative Total Query: Data Reference
Part |
Jul |
Aug |
Sep |
Oct |
Nov |
Dec |
Part A |
25 |
15 |
10 |
10 |
10 |
5 |
Part B |
20 |
10 |
5 |
5 |
10 |
15 |
Part C |
5 |
5 |
5 |
5 |
10 |
5 |
Cumulative Total Query: Result
Part |
Jul |
Aug |
Sep |
Oct |
Nov |
Dec |
Part A |
25 |
40 |
50 |
60 |
70 |
75 |
Part B |
20 |
30 |
35 |
40 |
50 |
65 |
Part C |
5 |
10 |
15 |
20 |
30 |
35 |
The result table above also illustrates the difference between restricting your display to the last few time periods and not restricting your display. If you uncheck the Display the last field, the result will show values in the grey area as well.