Query Studio

Adding a Calculation to a Query Studio 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 Introduction.

If an expression is used in multiple reports or by different report authors, ask Changepoint Support 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.

To add a calculation to a Query Studio report:

Open the Reports application.
Open the report you are changing 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.

Concatenating Strings

When Query Studio concatenates strings locally and if any of the involved strings contain null values, the result of the concatenation is an empty cell or a null value. This occurs because Query Studio requires that an expression that involves a null value returns a null value. Many databases ignore null strings when they perform concatenations. For example, if you concatenate strings A, B, and C, and if string B is a null value, the database may concatenate only strings A and C.