Insert calculated data

Most business reports require calculations to track business activities. Some of this calculated data can be included in the data set. Often, however, a data set does not provide all the data you want to display in your report. In this case, you can create your own calculations by creating a new field called a computed column.

Create a computed column

When you create a computed column, you write an expression, which is a statement that indicates how to calculate the data.For example, the value in a Total column is calculated by multiplying the value in the QUANTITYORDERED field by the value in the PRICEEACH field.

[PRICEEACH]*[QUANTITYORDERED]

Note: When you refer to a data field in an expression, you must enclose the field name within brackets ([ ]). Field names are case-sensitive.

Report Studio supports typical mathematical operations, such as addition, subtraction, multiplication, and division. Computed columns, however, are not limited to mathematical calculations. Report Studio also supports many functions for manipulating date-and-time and string data. A function is a set of instructions that do something and return a result. For example, if an EmployeeName field contains values with leading or trailing blank characters, you can remove the blank characters by using the TRIM( ) function as follows:

TRIM([CustomerName])

In this example, [CustomerName] is the input value, or argument, you supply to the TRIM( ) function to tell the function to trim the values in the CustomerName field.

Report Studio also enables you to create aggregate data for the values in a computed column.

To create a computed column:

Select the column to the left of the new computed column you want to insert.
From the toolbar, select Calculation.
In the Calculation dialog box, enter a name in the Column Label field for the computed column. The name you specify appears in the column header.
In the Enter Expression box, enter the expression to calculate the values you want to display:
- To use a data field in the expression, type the left bracket ([), then select a field from the list that appears.
- The list shows only the fields in use in the table. It does not show all the fields in the data set. To use a field that is not in the list, you must add the field to the table.
- To use a function, enter the first letter of the function, then select a function from the list that appears. The functions indicate the arguments, if any, you need to supply.
When you finish building the expression, select Validate. If the expression is syntactically correct, the following message appears:
The Expression is valid
If the expression contains an error, a message that describes the error appears.
After you validate the expression, click OK. The computed column appears in the table.

Add data fields to an expression

It is common to use a data field in an expression for a computed column. A computed column only has access to data fields that are used in the table. Sometimes, however, you need to write an expression that uses a field provided by the data set, but you do not want to display the field values in the table.

For example, if you insert an EmployeeName field and a Phone field in a table, then you insert a computed column, the computed column only has access to the EmployeeName and Phone fields. You cannot create an expression that combines, for example, values from the AddressLine1, City, State, and PostalCode fields. To create such a computed column, you must first add the data fields to the table, without actually using the fields in the table.

You can use the data fields you add for the following actions:

Filtering data using the Advanced Filter dialog box
Applying a conditional format
Representing data graphically in a chart

To add a data field:

Select and right-click the table, then select Data Fields.
In the Data Fields dialog box, click Add More Fields and expand the data set as necessary. The Data Set dialog box displays all the fields in the data set.
Select the field to add. To add multiple fields, press Ctrl as you select each field. When finished, click OK
In the Data Fields dialog box, click OK. The selected fields are now available to use in any expression.
To remove data fields, select each data field in the Data Fields dialog box then select Delete Selected Fields.
If you are in the process of defining an expression for a computed column, you can select Add Data Field, in the Calculation dialog box, and repeat the process to add fields.

Write expressions

An expression can contain any combination of literal values, operators, functions, and references to data fields.