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.

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

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.

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

• Use unions instead of joins when possible