Performing calculations in a report¶

About calculated data¶

Most BIRT reports require calculations to track sales, finances, inventory, and other critical business activities. You can use Interactive Viewer to create calculations to count items in a warehouse or provide more complex financial data, such as tracking stock portfolio performance over time.

To display calculated data in a report, you create a computed column, such as the Total column in the report shown in Figure 6-1. In the example, you need to calculate the Total because the original report does not provide this data.

Figure 6-1 Report with computed column

Interactive Viewer provides a convenient expression builder wizard to create computed data. You use Expression Builder to do one of the following:

Enable Interactive Viewer to build an expression. Select a function to use, then select one or more columns across which Interactive Viewer performs the calculation.
Create a custom expression. Select a function, then create an expression and validate it, after which Interactive Viewer performs the calculation.

About expressions¶

When you create a computed column, you build or create an expression that indicates how to calculate data. When you build an expression to create a computed column, first select a category, then select a function to use to compute the data. Interactive Viewer provides an expression builder containing categories with functions and operators that you use to create, modify, and view expressions. You can select the columns across which to perform the calculation. Figure 6-2 shows an example of building an expression to calculate the extended price, which is not included in the report.

Figure 6-2 Building an expression

If you are already familiar with writing expressions, you can create custom expressions to insert computed columns in a report. In the example, as shown in Figure 6-3, you multiply each value in the QUANTITYORDERED field with the corresponding value in the PRICEEACH field, to obtain the value in the Total column. The following expression calculates the Total:

Figure 6-3 Performing a calculation

:: [PRICEEACH]*[QUANTITYORDERED]

When you use a data field in an expression, you must enclose the field name within brackets ([ ]).

Interactive Viewer supports typical mathematical operations, such as addition, subtraction, multiplication, and division. In addition to mathematical calculations, Interactive Viewer supports functions for processing date-and-time and string data. You can create a computed column to display data that is not displayed in the report, or if you want to display data differently from its appearance in the report. For example, if a customer name field contains values with leading or trailing blank characters, you can remove the blank characters by using the TRIM( ) function as follows:

TRIM([CustomerName])

Creating and editing a computed column¶

In Interactive Viewer, you can use the expression builder to enable the viewer to construct an expression for you, or you can construct a custom expression by selecting Advanced in the Category field. The expression you create is used to compute the new calculated column that appears in the report.

This section describes how to create and edit computed columns in a report.

How to build an expression for a new computed column

Select the column to the left of where you want to place the new computed column. From the context menu, choose Column.New Computed Column. New Computed Column appears.
In Column Label, type a name for the new computed column. The name you specify appears in the column header.
In Select Category, select an option from the following categories:

Financial

Math

Date and time

Logical

Comparison

Text

A list of functions appears in Select Function.

In Select Function, do one of the following:

Select a function from the list, then go to step 5.

Select Advanced to manually create an expression. Then perform the steps listed in How to create a custom expression for a new computed column.

Based on the function you select, one or more column fields appears. In each Column field that appears, select a column from the list.

Choose OK. The new computed column based on the expression you built, appears in the report.

How to create a custom expression for a new computed column

In Enter Expression, type the expression that performs the calculation:

To use a data field in the expression, type the left bracket ([), then select the required field from the list that appears. The list displays only fields in the report.

To use a function, type the first letter of the function, then select the function from the list that appears. The functions indicate the arguments, if any, that you need to specify.

After you complete typing the expression, choose Validate. If the expression is syntactically correct, Information appears informing you that the expression is valid, as shown in Figure 6-4.

Figure 6-4 Expression valid confirmation message

If the expression contains an error, Information displays an error message. Choose OK.

On New Computed Column, choose OK. The computed column appears in the report.

How to edit a computed column

Select the computed column. From the context menu, choose Column > Edit Computed Column > New Computed Column appears. To modify the expression, continue as described in the previous section.

Building an expression¶

The expression builder in Interactive Viewer supports typical mathematical functions, such as percent of total, running sum, and percent of difference. It also supports a range of financial, logical, date-and-time, text, and comparison functions. Table 6-1 lists the functions available in each category of the expression builder.

Table 6-1 Categories and functions for creating a computed measure

Category	Function
Logical	IF
Financial	% OF % OF DIFFERENCE RUNNINGSUM
Text	FIND LEFT LEN LOWER RIGHT SEARCH TRIM TRIMLEFT TRIMRIGHT UPPER
Math	ABS ADD CEILING DIFFERENCE MOD PRODUCT RANK RATIO ROUND ROUNDDOWN ROUNDUP SQRT
Comparison	BETWEEN IS IS NULL LIKE NOT NULL
Date-and-time	NOW TODAY

A detailed description of all functions is available in a later section of this document.

Creating a custom expression¶

An expression is a statement that produces a value. An expression can be a literal value, such as:

1.23
"Hello, World!"

An expression can contain any combination of literal values, operators, functions, and references to data fields, as shown in the following examples. For detailed descriptions of the functions that Interactive Viewer supports, refer to a later section of this document. The following expression displays a customer.s first and last names, which the database entry stores in two fields. The & operator concatenates string values.

[FirstName] & [LastName]

The following expression displays a full address by concatenating values from four data fields and adding commas as appropriate:

[Address1] & ", " & [City] & ", " & [State] & " " & [Zipcode]

The following expression calculates a gain or loss percentage. The expression uses the mathematical subtraction, division, and multiplication operators, -, /, and *.

([SalePrice] - [UnitPrice])/[UnitPrice] * 100

The following expression uses the DIFF_DAY function to calculate the number of days it took to process an order for shipping:

DIFF_DAY([OrderDate], [ShippedDate])

The following expression uses the ADD_DAY function to calculate a payment due date when the payment term is net 30:

ADD_DAY([InvoiceDate], 30)

The following expression uses the IF function to evaluate if the value in the country column is UK. If the condition is true, the function replaces the value with United Kingdom. If the condition is false, the country values are displayed as stored.

IF(([Country]="UK"),"United Kingdom",[Country])

Using numbers and dates in a custom expression¶

When you create an expression that contains a literal number, type the number according to the conventions of the US English locale. In other words, use a period (.), not a comma (,) as the decimal separator, even if you are working in, for example, the French locale. For example:

Correct: ([Quantity] * [Price]) * 1.5
Incorrect: ([Quantity] * [Price]) * 1,5

Similarly, when you create an expression that contains a literal date, type the date according to the conventions of the US English locale. For example, if you are working in the French locale, type 03/12/2007 to represent March 12, 2007. Do not type 12/03/2007, which is the convention for the French locale. You must enclose literal date values in double quotation marks(” ”), as shown in the following expression that calculates the number of days from the order date to Christmas:

DIFF_DAY([OrderDate], "12/25/08")

How to add days to an existing date value

To create a column that displays date values that are greater than the date values in another column, complete the following steps.

Select a column. From the context menu, choose Column > Choose New Computed Column > New Computed Column appears.
In Column Label, type a name for the calculated column. For example, type Forecast Shipping Date.
In Enter Expression, type the letter A. A list appears, displaying functions that begin with A.
Choose ADD_DAY(date, daysToAdd).
For the first argument, type a left bracket ([) and select the date column from the list. For example, select Order Date.
For the second argument, type the number of days to add. In this case, type 7.
Validate the expression and choose OK. The calculated column appears in the report. For every value in the Order Date column, the calculated column displays a date seven days later than the order date.

How to subtract date values in a calculated column

The following section describes how to display the difference between two date values.

Select a column. From the context menu, choose Column > Choose New Computed Column > New Computed Column appears.
In Column Label, type a name for the calculated column. For example, for a calculation that subtracts the actual shipping date from the date requested, type Shipping Delay.
In Enter Expression, type the letter d. A list appears, displaying functions that begin with d.
Choose DIFF_DAY(date1, date2).
For the first argument, type a left bracket ([) and select the first date column from the list. For example, select Date Requested.
For the second argument, type a left bracket ([) and select the second date column from the list. For example, select Actual Shipping Date.
Validate the expression and choose OK. The calculated column appears in the report, displaying the difference between the two dates.

Using reserved characters in a custom expression¶

Some characters are reserved for internal use and have a special meaning. For example, as described in the examples in previous sections, Interactive Viewer uses brackets to denote a data field. The following characters are reserved in Interactive Viewer:

[
]
?
' (single quotation mark)

If the name of a data field contains reserved characters, Interactive Viewer removes the reserved characters when you select the data field to use in an expression. For example, if the name of a data field is OBSOLETE?, Interactive Viewer changes it to [OBSOLETE’?’] in the expression. If you type [OBSOLETE?] in the expression, an error message appears. To avoid syntax errors, always select the field and let the application construct the correct expression.

Figure 6-5 shows an example of a list of data fields, having reserved characters in the names of three fields. The following fields show both versions of the names, the changed name, enclosed in brackets, and the original name showing reserved characters:

[ORDER''S STATUS] - ORDER'S STATUS
[PRODUCTCODE'['4digit']'] - PRODUCTCODE[4-digit]
[OBSOLETE'?'] - OBSOLETE?

Figure 6-5 Data fields having reserved characters