Show TOC

Object documentationCumulative Total Parameters Locate this document in the navigation structure

 

Use the Configure Cumulative Total Parameters screen to configure parameters specific to the Cumulative Totals template. With this template, you can build queries that cumulate the value of a measure over a specific date range.

In the result, the measure is first cumulated over the date range specified, and the starting cell displays that initial period's sum. Thereafter, each cell in the grid displays the accumulation of the previous sum plus the sum in the next time period, and so on.

This kind of query is particularly useful if you have a specific target to hit by the end of the year, for example, and you want to track your progress toward it throughout the year. This can address business questions such as the following:

What is my monthly year-to-date expense total?

Structure

The main sections and controls of this screen are listed in the following table:

Section

Control

Description

Calculate

Running total of measure

Lists all measures in the cube on which the query is based.

Select the measure for which you want to display a running total.

For Members of

Dimension

Lists all 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.

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.

For Date Range

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 levels in the currently selected hierarchy.

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.

Display the last ... periods

If checked, restricts your display to the number of periods specified.

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:

Calculate section:

  • Running total of measure = Store Sales

For Members of section:

  • Dimension = Product

  • Hierarchy = Product

  • Level = Product Name

For Date Range section:

  • Time dimension = Time

  • Hierarchy = Time

  • Period level = Month

  • From = July

  • To = December

  • Display the last ... periods = checked; set to 3 periods

The data used to cumulate the total, together with the result of this query (the shaded 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 of Store Sales for Machine Parts (Result Shaded)

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 ... periods field, the result will show values in the entire table.