Calculations

You can perform many types of calculations in Query Studio. For example, you can calculate the sum or average of the values in one column, or multiply the values in two columns.

Calculation results are not stored in the underlying data source. Instead, Query Studio reruns the calculation each time you run the report. The results are always based on the most current data in the data source.

Note: The functions available when creating calculations are restricted by functions available in the data source.

You can perform calculations in Query Studio by adding summaries or by adding calculations.

Summaries

Use the Summarize command to add or remove footer values, or to change how detail values are calculated. For example, use the Summarize command to place an average in each footer.

Calculations

Use the Calculate command to create new report items using data from one or more report items. For example, use the Calculate command to add together several columns in a list report.

Add a Summary to a Report

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.
Count	Returns the total number of records. For more information, see Count and Count Distinct Functions.
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
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.

Advanced Summaries

You can use the advanced summaries feature to apply one summary calculation to the detail values and a different summary calculation to the footer values.

In most calculations, the default order of operations for detail values is summarize first, then calculate.

However, if a calculation involves two or more columns that have different detail summaries applied, the default order of operations is calculate first, then summarize. For example, you create the calculated column SALES using the formula PRICE (averaged) * QUANTITY (totaled).

For footer values, the default order of operations for most calculations is also summarize first, then calculate. However, there are two exceptions when the default order of operations is calculate first, then summarize. The first exception is when a calculation involves two or more columns that have different detail summaries applied. The default is calculate first, then summarize. For example, you create the calculated column SALES using the formula PRICE (averaged) * QUANTITY (totaled). The second exception is when a calculation involves a single column that is totaled, and a constant is added to this column. For example, you create the calculated column SALES + 1.00.

Steps

Open the report that you want 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.
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, click the Advanced link.
Click OK.
Tip: To remove a summary, in the Summary for footers box, click None.

Add a Calculation to a Report

You can perform calculations in a report using data from one or more report items. For example, you can multiply the values of a salary column by 1.05 to see the results of a 5 percent increase. You can also concatenate first names and last names into a single column.

You cannot edit a calculation. Instead, you must delete the calculated report item and recalculate it.

You can see the syntax of the calculation in the Expression box of the Calculate dialog box. You can also see the expression used in a calculation by viewing the report definition.

If you have the necessary permissions, you can open the report in Report Studio and add more complex calculations. However, after you edit a report in Report Studio, you can no longer edit it in Query Studio. For more information, see the Report Studio User Guide.

If an expression is used in multiple reports, or by different report authors, ask your modeler to create the expression as a standalone object in the model and include it in the relevant package.

The following functions are available when you create a calculation. Note that the analytic operations available depend on the data source you are using.

Arithmetic operations	Description
+ (sum)	Adds the values of the selected report items
- (difference)	Subtracts the values of one selected report item from another
* (multiplication)	Multiplies the values of the selected report items
/ (division)	Divides the values of the selected report items
^ (power)	Raises the values of the selected report item to an exponent
Absolute value	Calculates the absolute value of the numbers in the selected report item
Round	Rounds the values in the selected report item to the nearest integer
Round down	Rounds the values in the selected report item to the next lowest integer
Square Root	Calculates the square root of the values in the selected report item

Analytic operations	Description
Average	Calculates the average of values in the selected report items.
Maximum	Calculates the largest of the values in the selected report items.
Minimum	Calculates the smallest of the values in the selected report items.
Rank	Returns the rank of each value in the selected report item. For grouped reports, it can return the rank of each value in a group or within all values.
% of total	Calculates each value of a selected report item as a percentage of the total. For grouped reports, returns each value as the percentage of the group total or the overall total.
Percentile	Returns a percentile of values. For grouped reports, it can return the percentile for each value in a group or within all values.
Quartile	Returns the quartile of values. For grouped reports, it can return the quartile for each value in a group or within all values.
Quantile	Returns the quantile of values. For grouped reports, it can return the quantile for each value in a group or within all values.

Steps

Open the report that you want in Query Studio.
Click the headings of the report items you want to include in the calculation.
Click the calculate button on the toolbar.
In the Operation box, click the type of operation you want to perform.
Depending on the selected data, you may first need to choose an operation type from the Operation type box.
Choose which data to use for the calculation:
- To use the data in existing report items, in the Selected report items box, click the report items you want.
- To use other data, in the Number box, specify a number.
Depending on the calculation you choose, you may see additional options, such as changing the order of the operands, typing in a number, or choosing a grouping level.
Click Insert.
The calculated results appear in a new column. By default, the expression used in the calculation is used as the heading name.

Tips

To make the name of the calculated column more meaningful, type a new name in the New item name box.
To concatenate text into a single column, use the Concatenation operation.
When creating calculations that return a number data type, Query Studio applies formatting rules that depend on the calculation operation used. For more information, see Format Rules for Calculations Returning Number Data Types.