Cognos will do what it is told to do, if you give it clear directions.

Run Time

• Turn on “Include performance details” to see how fast your report objects are running in edit mode (HTML only): https://www.ibm.com/support/pages/how-include-report-performance-details-report-output

  • Note: The run options that you set apply only to the current session. When you close Cognos, the options return to the default settings.

Filters

• Apply the broadest filter first. This will minimize the number of records Cognos is processing.

• In a report using joins or unions apply the filter at the lowest level, in the queries prior to the join/union if possible.

Prompts

• Apply prompt filters at the lowest level possible. This may seem redundant but it limits that amount of data used as soon as possible.

• If prompts are taking a long time to load then

  • Filter the prompt query to only values that will be available in the report. IE. If the report is only displaying 2 years then the prompt query should be filtered for the same 2 years.

  • Use a smaller view to populate the prompt query.

Fields

• For the final draft, include only what you need. Remove any fields you included for testing. Extra fields in the query take up bandwidth because they are still pulling data into the query, even if they are not show in the final display.

• If the grouping you are using duplicates an already existing grouping use a level span instead. Grouping is a calculation that take Cognos resources, level span is a display item that does not.

Aggregations

• Activity Hub packages contain pre-set aggregations on all fields based on the common intended use of that field.

• Calculated fields are created with a ‘default' aggregation that required Cognos to guess what aggregation to use. Best practice is to set the aggregation appropriately on all calculated fields.

• Strings = When displaying the field values, set the detail and summary aggregation to none

• Dates = When displaying the field values, set the detail and summary aggregation to none

• Numbers = When displaying the field values

  • A default detail and summary aggregation will be set based on how the number is intended to be used.

  • Set the detail and summary aggregation to none if you want to see separate rows for each value and/or you do not want Cognos to summarize the number.

  • If you have created a new field Cognos will set the detail and summary aggregation to Default, this means Cognos is guessing what you want to aggregate by. This takes extra processing power and may not create the total you want. Set this value to improve performance and accuracy.

Data Item Calculations

• Do not go into filters

• If, then, else statements

  • If, then, else statements must result in the same data type for each step

    • if (data type whatever) then (data type C) else (data type C)

  • For better performance using elseif, use a case statement. Why? Calculations within calculations run for every line, calculations as separate calculations run separate calculations at once. Case statements run each line in order.

• Case, when, then, end statements

  • Case, when, then, end statements must result in the same data type for each step

    • case when (data type whatever) then (data type C) else (data type C) end

  • Case statements run in the order they are written, top down.

• Mathematical calculations

  • Any math calculation you can do in Excel or SQL

SubQueries

• Remove autogrouping and summarization on the lowest level detail queries. If you don’t need Cognos to create totals at the lowest levels then tell Cognos not to.

  • You can do this field by field or

  • You can do this in the query properties by going to the Query view then selecting the query then going to the properties and shutting ‘Auto group & summarize’ off.

    • 

• Remove additional fields that you are not using in the final display.

• Use unions instead of joins when possible.

Joins

• Remove additional fields that you are not using in the final display.

• Use aggregation on measure fields in order to summarize where possible. This will reduce the number of rows used in the join.

• Use unions instead of joins when possible.

• Use inner joins (1:1) when possible, this is the quickest running join.