Display calculated data

To display calculated data in a report, you create a computed column, such as the Total column, using an 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. With an interactive report, you use an expression builder wizard to create computed data. You can build an expression from a list of predefined functions or you can create a custom expression.

The expression builder wizard 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.

The expression builder contains categories with functions and operators that you use to create and edit expressions. When you build an expression to create a computed column, first select a category, then select an associated function to compute the data. Next, select the columns on which to perform the calculation.

To build an expression for a new computed column:

  1. Select and right-click the column to the left of the location of the new computed column.
  2. Select Column> New Computed Column.
  3. In the New Computed Column glance (also known as a contextual call-out) Provides information and actions in a dialogue box for an item on the screen when the user right-clicks or taps the item., complete the following fields
    1. Column Label — Enter a name for the new computed column. The name you specify appears in the column header.
    2. Select Category — Select an option from the following categories:
    3. Comparison
    4. Financial
    5. Text
    6. Math
    7. Date and time
    8. Logical
    9. Select Function — Do one of the following:
    10. Select a function from the list
    11. Select Advanced to manually create an expression. See Create a custom expression and .
  4. Click Tap OK. The new computed column, based on the expression you built, appears in the report.

If you are familiar with writing expressions, you can create custom expressions to insert computed columns in a report. For example, you could multiply each value in the QUANTITYORDERED field with the corresponding value in the PRICEEACH field, to obtain the value in the Total column. When you use a data field in an expression, you must enclose the field name within brackets ([]).

Examples:

Display an employee's first and last names, which the database stores in two fields. The & operator concatenates string values.

[FirstName] & " " & [LastName]

Display a full address by concatenating values from four data fields and adding commas as appropriate:

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

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

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

Use the DIFF_DAY function to calculate the number of days it took to process an order for shipping:

DIFF_DAY([OrderDate], [ShippedDate])

Use the ADD_DAY function to calculate a payment due date when the payment term is net 30:

ADD_DAY([InvoiceDate], 30)

Use 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])

Uses the TRIM function to remove leading or trailing blank characters:

TRIM([CustomerName])

After you provide a column label, select a category, and select the advanced function in New Computed Column, do the following:

  1. In the Enter Expression field, enter the expression that performs the calculation:
    1. 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.
    2. 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.
    3. After you complete typing the expression, select Validate. If the expression is syntactically correct, a message informs you that the expression is valid.
    4. Click tap OK.
  2. In New Computed Column, click tap OK. The computed column appears in the report.

When you create an expression that contains a literal number, enter 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")

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

  1. Select and right-click a date column.
  2. Select Column > New Computed Column.
  3. In the New Computed Column box, complete the following fields:
    • Column Label — Enter a name for the calculated column. For example, enter Total Hours.
    • Select Category — Select Date and Time.
    • Select Function — Select ADD_DAY
    • Column — Select the column to which to add the specified number of days.
    • Days — Enter the number of days to add, then click tap OK.The calculated column appears in the report.
  1. The following section describes how to display the difference between two date values.
  2. Select and right-click a date column.
  3. Select Column> New Computed Column.
  4. In the New Computed Column glance, complete the following fields:
    • Column Label — Enter a name for the calculated column. For example, for a calculation that subtracts the order date from the ship date, type Days to Ship.
    • Select Category — Select Date and Time.
    • Select Function — Select DIFF_DAY.
    • Column1 — Select the column to subtract from Column2, for example Order Date.
    • Column2 — Select the column from which to subtract Column1, for example Ship Date. Choose OK. The calculated column appears in the report, displaying the difference between the two dates.

Some characters are reserved for internal use and have a special meaning. For example, Interactive Viewer uses brackets to denote a data field. The following characters are reserved in interactive reports:

[

]

?

' (single quotation mark)

If the name of a data field contains a reserved character, the interactive report encloses the reserved character in single quotation marks ( ') when you select the data field for 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, the dialog box displays an error message.

To minimize syntax errors, select the field from the list in the New Computed Column dialog box and allow the software to construct the expression, instead of typing it yourself. The following examples show the appearance of data fields containing reserved characters in the New Computed Column dialog box. The examples show both versions of the names, the changed name and the original name with reserved characters, as follows:

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