Modeling a new computed field in Cognos

You can model an equation in Cognos® to define a computed field in the application.

Note: If you do not know how to use IBM® Cognos Analytics - Reporting, contact an experienced Cognos report author or IBM Support.

Procedure

Log on to IBM Cognos Analytics as an IBM OpenPages® user with the locale set to Report Design Language.
Create a list report that you can use to model the computed field equation.

Drag the following ID query items onto the report page to establish a context for the calculation:

An object ID

Example

SOXBUSENTITY HIERARCHY >> SOXPROCESS–SOXCONTROLOBJECTIVE HIERARCHY >> [SOXRISK] >> [RI_RISK_ID]

A reporting period ID

Example

SOXBUSENTITY HIERARCHY >> SOXPROCESS–SOXCONTROLOBJECTIVE HIERARCHY >> [SOXRISK] >> [REPORTING_PERIOD_ID]

Click Toolbox on the Insertable Objects pane and complete the following actions:
1. Drag a Calculation object onto the report page.
2. At the prompt, type a name.
  For example, type Calc-Risk.
In the Expression Definition pane of the model, complete the following actions:
1. Enter an expression using model query items from the same namespace, function, or parameters.
  
  The Cognos SQL used to define this computed value can be an existing query item in the published Cognos framework or an equation involving multiple query items. Some of the predefined database functions may also be useful for computed fields (such as getting an exchange rate or localizing strings). For details, see the IBM OpenPages Report Author's Guide.
  For example, the following equation returns a value with the percentage by which the inherent severity of a risk was reduced after associated controls were applied to that risk. Sample output might be: 2.46.
```
total ([DEFAULT].[SOXCONTROL].[CN_INHERENT_SEVERITY_REDU]for [DEFAULT].[SOXCONTROL].[RISK_ID]) / 100
```
2. Validate the expression and make any needed changes.
Run the report. Check the results.

Click XML Show Specification on the toolbar to view the Cognos SQL in an XML representation. The following XML sample shows which sections of the report will be used to define the computed field in IBM OpenPages and the corresponding field name in the application.

<querySet xml:lang="en-ca">
	<BIQuery name="Query1">
		<cube>
			<factList>
				<item refItem="RI_RISK_ID" aggregate="none"/>
				<item refItem="REPORTING_PERIOD_ID" aggregate="none"/>
				<item refItem="Calc-Risk" aggregate="none"/>
				<tabularModel>
					<dataItem name="RI_RISK_ID">
						<expression>[DEFAULT].[SOXRISK].[RI_RISK_ID]</expression>
					</dataItem>
					<dataItem name="REPORTING_PERIOD_ID">
						<expression>[DEFAULT].[SOXRISK].[REPORTING_PERIOD_ID]</expression>
					</dataItem>
					<dataItem name="Calc-Risk">
						<expression>total ([DEFAULT].[SOXCONTROL].[CN_INHERENT_SEVERITY_REDU]
							for [DEFAULT].[SOXCONTROL].[RISK_ID]) / 100</expression>
					</dataItem>
		</tabularModel>
	</querySet>

Note: Because the values in the Report Specification XML window cannot be selected, you can copy the report specification to the Clipboard (Tools | Copy Report to Clipboard) and then paste the information into a text document. Then, you can copy the attribute values into the application user interface. The value to be used in the Equation definition box can also be obtained from the Expression Definition pane of the calculation object.

What to do next

In OpenPages, define the computed field. For more information, see Defining a computed field.