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.

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:

If the Ignore Values in Nodes checkbox is selected, values of hierarchy parent elements are ignored on calculation.

NOTE. It is recommended to use the parameter only if data was aggregated into parent element from all child elements.

The example of deselected and selected checkbox:

The Grand Total checkbox determines displaying of grand totals calculated by all table rows or columns.

The By Hierarchy and By Levels checkboxes are relevant if elements of hierarchical dimension of different levels of hierarchy are displayed in the table. Selecting these checkboxes enables the user to display names of parent elements of hierarchy or level names, respectively, within the name of the total element.

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 + By Hierarchy, Grand Total + 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.

Select the Grand Total checkbox and determine settings of the displayed total: the type - Sum, the element name - Composite, delimiter - ",". The source table is shown on the left, the table after the checkbox is selected is on the right:

Select the Hierarchy Total checkbox and determine settings of the displayed total: the type - Sum, the element name - Total Method. The source table is shown on the left, the table after the checkbox is selected is on the right:

Select the Grand Total and the Hierarchy Total checkboxes and determine settings of the displayed total: the type - Sum, the element name - Source. The source table is shown on the left, the table after the checkbox is selected is on the right:

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.