Create a Report with Calculation Formulas

By using the function Calculate Report formulas, you can perform a calculation in a report and save the result in the database.

Procedure

  1. Create an account to store the calculated value on. Select Maintain/Account Structure/Define. Here you define a new account. Use the check box Calculation Account to define an account as a calculation account.
  2. Create a new report for calculation accounts. Select Reports/Create and select the option Calculation Report.
  3. Fill out the axes and row definitions as usual when creating a report and click OK.
  4. In the layout sheet in Microsoft Excel, create a fCalcVal formula that performs the calculation, see IBM Cognos Controller Functions for more information.
  5. Run the report and click Save.
  6. Now the calculated values need to be stored in the database of IBM Cognos Controller. Select Group/Calculate Report Formulas. Select the report containing the fCalcVal formulas and appropriate parameters. The calculated values are now saved in the database and can be handled like any other saved period value.

Results

  • The calculation account needs to be a statistical account.
  • Use the option Save between Formulas (Group/Calculate Report Formulas) if you have several calculation reports that interact with each other. For example if a fCalcVal formula uses a value that has been calculated by another FCalcVal formula in another report.
Tip: The option Show calculated values(Group/Calculate Report Formulas) gives you a preview of the calculated values.