Calculating Totals

Depending on the selected type of total (subtotal, total or totals by rows or columns), the following total calculation methods are available:

• Sum.

• Average. Average will be calculated by cells with data. If cells are empty, on calculating the average, those cells are not taken into account. For example, if 18 of 20 cells contain data, the average will be calculated by 18 cells.

NOTE. To calculate arithmetic mean, select the Treat Empty Values as Zeros checkbox.

• Minimum.

• Maximum.

• Number of non-empty.

• Number of empty.

• Mode.

• Median.

• Variance.

• Root-mean-square deviation.

• Skewness.

• Kurtosis.

• Range.

• Variation coefficient.

• Sum of squares.

• First-order autocorrelation.

• Upper decile.

• Upper quartile.

• Lower quartile.

• Lower decile.

• Grand total. Grand total based on server aggregation of facts.

• Totals from source. It displays aggregated source data. It is available if a data source has configured dimension aggregation of cube facts and global cache.

If dimension element data has the Text/Date/Time format, totals are calculated only for the following total types: Maximum, Minimum, Number of Empty Cells, Number of Non-Empty Cells.

In the web application the user can set custom names of table totals. To do this, execute the following operations:

1. In the Total Type (from included) drop-down list select one of the applied table total types.

2. Specify a new total name in the Name box.

To restore default total names, manually clear the Name box or click the Clear button.

NOTE. When a new total type is selected, the name specified for the previous total type is saved.

The example of table with default total names:

The example of table with custom total names:

The example of row totals:

The example of column totals:

The first method:

1. Select one or several cells with totals values in the table.

2. Go to the Calculation Options tab in the Totals group of tabs on the side panel.

The second method:

1. Select any cell or data range in the table.

2. Go to the Totals tab in the Table group of tabs of the side panel.

If the Treat Empty Values as Zeros checkbox is selected, empty values in cells are treated as zeros on calculating totals. This is important, for example, on calculating average values.

NOTE. The checkbox selection does not impact the Number of Empty and Number of Non-Empty total calculation and in the report status bar if the Average option is calculated.

The example of deselected and selected checkbox:

When the checkbox is selected, values of parent elements of the hierarchy are ignored. The checkbox is deselected by default.

The Grand Total checkbox determines displaying of grand totals in table columns/rows calculated by all elements with set selection. The checkbox is selected by default.

The Subtotal checkbox determines displaying of totals in columns/rows depending on checkbox states:

• By Hierarchy. Totals are calculated by hierarchy of dimensions in table sidehead/heading by each element with set selection.

• By Levels. Totals are calculated by levels of dimensions in table sidehead/heading for each element with set selection.

NOTE. If table sidehead/heading includes several hierarchical dimensions, on calculating subtotals, each next left/top dimension includes total of the topmost level of the right/bottom dimension.

When these checkboxes are selected, names of total elements show names of parent elements of hierarchy and/or level names, respectively. The By Hierarchy and By Levels checkboxes are selected by default.

TIP. If grand total matches subtotal by hierarchy or by levels, enable grand total to avoid duplicate information.

The example with selected checkboxes: Grand Total, Subtotal, and By Hierarchy:

The example with selected checkboxes: Grand Total, Subtotal, and By Levels:

If the Output Before Data checkbox is selected, totals are displayed before data cells.

Example:

If the Calculate Total for One Element checkbox is selected, when calculating totals by columns and by rows, the totals are also calculated:

• For elements by each level.

• For elements with one row or column.

The example of deselected checkbox:

The example of selected checkbox:

1. Select one or several cells with totals in the table.

2. Go to the Output String tab in the Totals group of tabs of the side panel.

Select the total type applied to the table in the drop-down list.

Select name type in the list:

• Source. It displays source element name.

• Total Method. It displays total method name.

• Composite. It displays name of the source element and total method.

Determine delimiter between element and total method names in the edit box. The item is available when the Composite name type is selected.

Examples of displaying totals in the parent element string:

• When the Grand Total checkbox is selected, the Sum total type is set, the Combined element name is selected, and the "," separator is set:

• When the Totals by Hierarchy checkbox is selected, the Sum total type is set, and the Total Method element name is selected:

1. Select the cell with totals in the table.

2. Select the Totals button on the side panel.

3. Go to the Data Formatting or the Header Formatting tab.