11    Using Formulas and Variables

Create complex calculations by using data objects retrieved from your database along with more than 160 built-in reporting functions and 40 operators in the Formula Editor. By using formulas and variables to transform data into analytical information, you’ll be able to make better business decisions.

Formulas and variables are commonly used structures that allow you to create calculations using the data retrieved from a query. Variables are used to store the calculation syntax of a formula in reusable objects that are saved in Web Intelligence reporting documents.

Variables can be used in reports to perform a variety of tasks that display your data in different ways than when retrieved by queries. Variables can be used to insert If–Then–Else logic into a column or chart, contain complex calculations that produce precise views of information, or create analytical formulas that solve difficult business problems—all within a single object that can be used throughout a reporting document.

The formula bar provides the capability to quickly revise the definition of an object within a toolbar, similar to Microsoft Excel. The Formula Editor or Create Variable windows can be used to create extensive and detailed formulas with the convenience of viewing all available data elements, functions, and operator types in the same window.

The next several sections introduce you to the Formula Editor, explain the syntax used in creating formulas and variables, and provide examples to help you get the most out of the editor when creating unique calculations.

11.1    Formulas and Variables

The first step in creating a formula or variable or modifying the definition of an existing field is to enable either the formula bar or Formula Editor. Regardless of the viewer set in the Web Intelligence preferences, the formula bar is accessible only while in Design mode. The second step is to understand the syntax used in the Web Intelligence Report Panel. We’ll begin by editing an existing report and describing the various ways to access the Formula Editor, also known as the Create Variable window.

To add a new variable to a reporting document without placing it directly in a data table, select the Data Access tab provided in the ribbon of tabs located above the report. Next, select the Data Objects tab in the second set of subtabs. Figure 11.1 shows the New Variable icon accessible inside the Data Objects tab. The default variable type is dimension. Select the small down arrow immediately to the right of the New Variable button to choose between Dimension, Detail, or Measure as the new variable type.

Creating a New Variable Object from the Tab Ribbon

Figure 11.1    Creating a New Variable Object from the Tab Ribbon

11.1.1    Converting In-Place Objects into Formulas

Formulas are calculations created to transform data retrieved by your queries for producing results that solve business problems. These formulas can be created as variables and then used in data tables and charts throughout a Web Intelligence document rather than creating single instances of the formula.

Every object added to a table or chart in a report contains a formula definition, even if the object added comes directly from objects retrieved by the query. Figure 11.2 shows the dimension object selected in a table with the formula bar displayed. Notice that the formula for the selected object is =[City].

Formula of the Selected Dimension Object

Figure 11.2    Formula of the Selected Dimension Object

The formula or definition of a selected object in a table can be edited by changing the definition in the formula bar. An example of a revision that can be applied to the selected object is displayed in Figure 11.3, in which the City object was replaced with a formula by modifying the object definition in the formula bar.

Revised Formula in the Selected Dimension Object

Figure 11.3    Revised Formula in the Selected Dimension Object

Editing the definition of an object in the formula bar will result only in modifying the specific instance of the object. To use the formula in other report elements within the document, click on the Create Variable icon to transform the formula into a variable usable throughout the document.

Tip

Clicking on the Create Variable icon located to the left of the formula bar launches the Formula Editor and gives you access to available objects, functions, and operators for creating even more complex and powerful formulas.

Figure 11.4 shows the other commands to use when editing an object definition or formula:

Revising the Formula of a Selected Object in a Cross Table

Figure 11.4    Revising the Formula of a Selected Object in a Cross Table

11.1.2    Exploring the Formula Editor

After launching the Formula Editor, you can graphically build or edit your formula by using elements from the following three categories of objects for advanced formula creation:

Figure 11.5 shows the Formula editor in the Create Variable window, which is used for creating and editing formulas graphically, writing freehand syntax, or a combination of both.

Formula Editor in the Create Variable Window

Figure 11.5    Formula Editor in the Create Variable Window

Formulas can be edited manually, by dragging and dropping, or by double-clicking on Available objects, Functions, and Operators and then placing them in the Formula area. To validate the syntax of the statement created in the formula, click on the green checkmark to the right of the window. If the formula is incorrect, the position of the first invalid identifier will be displayed.

For a quick example of the proper syntax to be used for a function or operator, click on the object and then view the contents of the description box located at the bottom of the editor. A brief description of the function or operator will appear, followed by the proper syntax structure of the object selected.

Functions in the formula editor are presented in nine different categories. A tenth category is also provided that contains all 160+ functions. These nine functional groups are listed here along with a few commonly used functions:

Section 11.3 provides examples and additional information about these formulas.

11.1.3    Creating Variables

The difference between variables and formulas is that variables have been converted into reusable objects. By promoting a calculation or formula into a variable, you’re creating a local result object that can be used throughout a reporting document.

To turn a formula into a variable, click on the Create Variable icon located to the left of the formula bar, as shown in Figure 11.6.

Create Variable Icon to Convert the Formula into a Variable

Figure 11.6    Create Variable Icon to Convert the Formula into a Variable

The Create Variable window allows you to perform all of the same tasks as the Formula Editor, with the addition of being able to assign a name and qualification to the variable. If Detail is selected as the qualification type, you also have the opportunity to assign an associated dimension to the object. The following are the modifiable sections in the Variable Definition section of a new variable:

Figure 11.7 shows the Create Variable window used to name and set the qualification of a new variable object being converted from a formula.

Create Variable Window

Figure 11.7    Create Variable Window

Formula Descriptions, Syntax, and More on This Function

If you’re new to a function or want to see the syntax that it requires, just select the function from the list of available functions in the Create Variable window. After selecting the function, you’ll see a description of what the function does along with the data type, syntax required, and a link to several examples labeled More on this function. Pictured in Figure 11.8 is the Formula Editor with the new MemberAtDepth function selected.

Syntax Help with Functions

Figure 11.8    Syntax Help with Functions