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

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.

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 possibe.

Data Types

• Strings

  • Alpha (words), alphanumeric, character, car, varchar or sometimes numeric

    • Names, Codes, 9 digit year

  • Filters and calculations must include a single quote (') before and after the value

    • Name = ‘Mickey Mouse’

    • Year = ‘2023’

    • ID = ‘123456789’

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

• Dates

  • Dates, Date and Time formatted as yyyy-mm-dd hh:mm:ss.###

    • No matter how the original data source is formatted, Cognos formats the date time as above

    • Report developers can change the output format but filters and calculations must use the Cognos format

  • Filters and calculations must be formatted like a date or date time, no quotes

    • Date = 2023-04-15

    • DateTime = 2023-04-15 18:00:00.000

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

• Numbers

  • Integers, decimals, money amount

  • Filters and calculations do not include quotes or commas

    • Amount = 12345.67

  • 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

  • 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 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 no to.

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

• Use unions instead of joins when possible