Enhancing Reports Using Formulas and Variables

See the following video to learn more about the Formulas.

A custom calculation sometimes contains only report objects, for example [Sales Revenue] / [Number of Sales]. Calculations can also include functions in addition to report objects.

A function receives zero or more values as input and returns output based on those values.

For example, the Sum function totals all the values in a measure and outputs the result. The formula Sum([Sales Revenue]) outputs a total of sales revenues. In this case, the function input is the Sales Revenue measure and the output is the total of all Sales Measures.

The text in report cells always begins with the equal sign (=) .

Literal text appears in quotation marks, while formulas appear without quotation marks.

For example, the formula Average([Revenue]) appears in a cell as =Average([Revenue]). The text "Average Revenue?" appears as ="Average Revenue?".

You can use text alone in a cell, or mix formulas and text by using the plus (+) operator.

If you want a cell to display the average revenue preceded by the text "Average Revenue:", the cell text is as follows: ="Average Revenue: " + Average([Revenue]).

Note the space at the end of the text string so that the text and the value are not placed directly side-by-side in the cell.

The Formula Editor displays the function syntax when you select the function.

To use a function you need to know its name, how many input values it requires and the data types of these input values. You also need to know the type of data that the function outputs.

For example, the Sum function takes a numerical object as input (for example a measure showing sales revenue) and outputs numeric data (the sum of all the values of the measure object).

Here is the syntax of the Abs function: num Abs(number).

This syntax tells you that the Abs function takes a single number as input and returns a number as output.

Display the prompt input using the UserResponse function.

The example syntax =UserResponse(prompt_string) can be broken down into the following parts:

You have a report showing Year, Quarter and Sales revenue. The State object also appears in the report data, although it is not displayed. When the user runs the report, they are presented with a prompt and they must choose a state. You want to show the state that they have chosen in the report title. The text in the prompt is "Choose a State:".

A variable is a formula that you save with a name. A qualification is assigned to the variable, which is based on the formula. A variable acts as an object in the document and displays in the Available Objects panel in the Web Intelligence main window, similar to any other data object. You can use the variable as you would use any object to display the results of the calculation in multiple blocks and reports throughout the Web Intelligence document.

The advantage of creating a variable is that you can reuse the formula without having to enter it each time. A variable is defined according to the formula you enter when you create it. However, a variable behaves like a standard object in a query and a document.

A variable is saved in a document. Therefore, it can be used in any report within the document.

Considering the advantages of a variable, create a variable instead of a formula. When required, you can create a formula and later convert it to a variable, but creating a variable directly saves you time and effort.

Variables are useful when you require a calculation and plan to use it more than once and in different blocks and report tab pages within a document.