Statistics are useful for extra calculations on data populated from queries.
You can add extra rows and columns to the grid that contain statistical information based on the results in the grid.
Note: statistics are only displayed when viewing the results in outline mode in the grid.
Adding statistics
To add statistic values:
| • | Click on the  button on the grid menubar. |
You can add grand rows, sub rows and columns to the grid.
Select the statistical rows and columns you would like to add simply by checking the boxes.
The following statistical functions are available:
Total |
The total of all values |
Totals |
The "smart" total of all values (see below) |
Average |
The average of values |
Minimum (Min) |
The minimum value |
Maximum (Max) |
The maximum value |
Count |
The number of values in the calculation |
Standard Deviation (Std. Dev.) |
The sample standard deviation of values, using the unbiased population formula |
Variance |
The sample variance of values, using the unbiased population formula |
Sum |
The sum of all values |
Sums |
The "smart" sum of all values (see below) |
Notes:
| 1. | Both Standard Deviation and Variance require at least two values in the row/column to calculate. |
| 2. | No statistics will be displayed if the row axis doesn't contain a hierarchy. |
| 3. | Sub statistics will not be displayed if the row axis only contains a single hierarchy. |
Difference between Total and Sum
In most, but not all cases, the Total and Sum statistics will provide the same result. The difference between the two lies in the fact that the Sum statistics always adds all involved cell values, regardless of the measure and members related to the cell value. Total on the other hand, takes the aggregation function of the measure into account (usually sum, but this can also be Min, Max or Distinct Count for example) and also looks at the unary operator of a member. This can lead to different results.
The Total and Sum statistics are available in both regular and "smart" versions. The difference between the two is that the smart versions avoid values being counted twice. This can happen when a hierarchy with multiple levels in involved in the statistic. For example, consider the following example:
The Total statistic value is the sum of all rows, including the values for Alberta, British Columbia and Ontario. But as the value for Canada automatically includes these values, counting both the parent and the detail values will provide an incorrect result. Smart statistics remove any parent aggregations if children are present in the statistic calculation. The smart Totals value in this case correctly recognizes this, and only counts the values for Alberta, British Columbia and Ontario. Canada is discarded while calculating the statistic, as it is an aggregate of these values.
DataWarehouse Explorer will always try to calculate the statistics on the server whenever possible. In most cases, statistics will be the same when calculated on the server or locally. However, in some cases, a statistic can not be calculated reliably locally. This is the case when the underlying measure is calculated, has an aggregation function other than [sum, count, min or max] (such as [Distinct Count]) or the members involved have unary operators defined for them. That's why server based statistics are always preferred.
Under certain cases however, the statistics can not be calculated on the server, due to technical limitations. This happens when a filter has been defined on one or more columns and the row axis contains more than one hierarchy. In this case the statistics will be calculated locally. A warning will be displayed in this case and the results statistics will always be displayed in Italic.
Note: you can force statistics to be calculated locally by removing the check next to the Calculate statistics on server checkbox.
Example
The grand row is displayed at the bottom of the grid and includes all results in the calculation.
Sub statistics are calculated for all values within the level.
|
Figure 1 |
