Query Studio

Adding a Summary to a Report in Query Studio

You can use predefined summaries to calculate the total, count, maximum, minimum, or average of the values in individual report items.

You can apply a summary to

detail values
These values appear in the detail rows of your report. This functionality is available only if the data in a report item is a measure.
summary values
These values appear in footers.

The summaries available depend on the type of data in the selected report item. For example, the only summaries you can use with text data is Count or Count Distinct .

There is no visible indication that a summary is applied. For example, you apply the predefined summary function Average, and then save the report. Someone can open the report later and not be aware that the summary figures shown are averages, not totals.

Query Studio provides the following predefined summary functions.

Function	Description
Total	Returns the sum of a set of values. Note: The largest value that Total can return is the maximum value for the data type of the column. The Total function is performed iteratively. It takes the value from the first row and then adds the second row to it, followed by the third row, and so on. If at any point the intermediate value exceeds the maximum value for the data type, the data source will return an error. Important: The Total function is valid for numeric fields. If used on non-numeric fields (Date fields for example) an error is displayed.
Count	Returns the total number of records. For more information, see Count and Count Distinct Functions below.
Maximum	Returns the maximum value. When applied to date or time data, this returns the latest value.
Minimum	Returns the minimum value. When applied to date or time data, this returns the earliest value.
Average	Returns the average of a set of values.
Calculated	Specifies that the summary is defined within the expression that is used to populate the column. Note: It is expected that the expression itself is an aggregation function and should not require modification to provide summary values.
Automatic	Summarizes values based on the data type.
Median	Returns the median value of the selected data item.
Standard Deviation	Returns the standard deviation of the selected data item.
Count distinct	Returns the total number of unique non-null records. For more information, see Count and Count Distinct Functions below.
Variance	Returns the variance of the selected data item.
None	Removes footers. This applies only to footer values, not details.

Count and Count Distinct Functions

The default behavior when you use the Count function differs depending on the type of data that you are counting.

Data type	Default and Options
Query item (relational)	Default: Count Distinct. Options: Choose between Count and Count Distinct.
Set of members (levels, hierarchy)	Default: Count Distinct. Options: Only Count Distinct is possible.
Attributes - OLAP and Dimensionally Modeled Relational (DMR) data sources	Default: None. Summaries are not possible.
Measures - OLAP and DMR	Default: Count. Options: Only Count is possible.
Measure - relational fact query item	Default: Count. Options: Choose between Count and Count Distinct.

To add a summary to a report:

Open the Reports application.
Open the report that you want to edit in Query Studio.
Click the heading of the report item you want to summarize.
From the Edit Data menu, click Summarize.
In the Summary for footers box, click the function you want.

Note: If you want to apply one summary calculation to the detail values and a different summary calculation to the footer values, or you want to change the default order of operations in a calculated column, see Advanced Summaries in Query Studio.
Click OK.

Tip: To remove a summary, in the Summary for footers box, click None.