BI Query lets you add calculated attributes to a data object. A calculated attribute performs a user-defined calculation in a query. The calculation can be based on more than one attribute from more than one data object in a Design window. You can also apply a function to an attribute in the calculation.
For example, a data object may include Unit Price and Unit Cost attributes that retrieve a product’s market price and production cost, respectively. By creating a new attribute that calculates Unit Price minus Unit Cost, you can determine the profit made on each product.
If you are using BI Query User or Update, you can edit or delete any calculated attribute in a combined data model. However, in a split data model, you can edit or delete only calculated attributes that you have created, not those provided by the administrator.
For more information on editing and deleting calculated attributes, see the Help for your BI Query application.
Creating Calculated Attributes
You can create a calculated attribute that performs a calculation you define. The calculation can be based on more than one attribute from more than one data object in a data model.
When you create a calculated attribute, you can either set a specific data type for the attribute or let BI Query automatically set the data type based on the
calculated values.
If you select an automatic data type for a calculated attribute, BI Query cannot run the default SELECT DISTINCT query for the attribute. In this case, if you want to qualify the attribute, you cannot select the qualification value(s) from a list of data values retrieved from the database.
To create a calculated attribute:
1. Ensure that BI Query is not in Design mode.
2. In an attribute window or the Query window (in Query View mode), click the Function box for the attribute below which you want to add the calculated attribute.
3. On the pop-up menu that opens, click Calculation. The Edit Calculated Attribute dialog box opens.
4. In the Name dialog box, type the name you want to give to the calculated attribute.
5. From the Data Type list, select the data type for the attribute.
For more information on available data types for calculated attributes, see the Help for your BI Query application.
If you want the type to automatically match the data type of the calculation, select either Automatic Numeric or Automatic Character, depending on the calculation. You can also select a specific data type for the attribute.
If you are connecting to a Teradata DBMS and you are not using ODBC, you must select a specific data type for the calculated attribute. Do not use the defaults Automatic Numeric or Automatic Character.
6. In the Expression box, type the calculation. Add a space between each item in the calculation. You can also build the calculation using the available buttons, as follows:
• To enter the database name of an attribute, select a data object from the Attributes list, then select an attribute from the list below the Attributes list, then click Insert.
• To insert a prompt into the expression, click Prompts. In the Prompts dialog box, select the prompt you want to insert from the Prompts list; then, click Insert.
The following restrictions apply to prompts in calculated attributes:
• they must be mandatory.
• they must have an explicitly set data type.
• they can only contain one value (they must be linked to either a single-value data single-value query or data single-values result set).
• if the calculated attribute is in a query that may be used in BI Web, the prompt must accept a single value only; it can not be linked to a data values query.
Furthermore, if the same prompt is used elsewhere and contains multiple values, or if someone changes the prompt’s data type, the calculated attribute may fail.
• To insert a variable into the expression, click Variables. In the Variables dialog box, select the variable you want to insert from the Variables list; then, click Insert.
• To insert a function into the expression, click Functions. In the Functions dialog box, select the function you want to insert from the Functions list or Other Functions list; then, click Insert.
If you select a built-in function from the Functions list, it automatically applies to the selected attribute; the function and the database name of the attribute appear in the Expression box and the Aggregate option is selected.
(This ensures that when the calculated attribute is used in a query, the other attributes in the query are grouped.)
For more information on using functions in calculated attributes, see
“Including Functions in Query Expressions” on page 197.
7. To ensure that the other attributes in the query are grouped when the calculated attribute is used in a query, select Aggregate.
For more information on grouping, see “Types of Grouping” on page 188.
8. To save the attribute for future sessions, select Save in Data Model. This option is unavailable if you are running BI Query Update or BI Query User and you do not have the Save calculated attribute in model permission.
9. Click OK. The calculated attribute is added below the attribute you selected in step 2.