The filters and calculations that you can use in Cognos are dependent on the data type within the field you are using.

Data Types

• String = An alpha numeric representation of data

  • AKA: Dimension, word, description, code, character, varchar

  • Examples: words, descriptions, names

  • Examples: year, ID, Code, PID, age = any number you do not want to add up

• Date = Data formatted as a date

• Number = Data formatted as a number

  • AKA: amount, count, integer

Filter Codes by Data Type

Different filters work for different data types. More options can be found here = https://ucsdcollab.atlassian.net/wiki/spaces/ACP/pages/11174932

*This assumes you are not using the Cognos wizard. The Cognos filter wizard will create these for you.

• Strings

  • [field] like ‘%end_of_string’

  • [field] ends with ‘end_of_string’

  • [field] like ‘Beginning_of_string%’

  • [field] starts with ‘Beginning_of_string’

  • [field] = ('word1')

  • [field] <> ('word1')

  • [field] in ('word2', ‘word3’, ‘word4’)

  • [field] NOT in ('word2', ‘word3’, ‘word4’)

• Dates

  • [date_field] between YYYY-MM-DD and YYYY-MM-DD

  • [date_field] = YYYY-MM-DD

  • [date_field] < YYYY-MM-DD (will give days before YYYY-MM-DD)

  • [date_field] >= YYYY-MM-DD (will give days on and before YYYY-MM-DD)

  • [date_field] > YYYY-MM-DD (will give days after YYYY-MM-DD)

  • [date_field] >= YYYY-MM-DD (will give days on and after YYYY-MM-DD)

  • Note: you can use YYYY-MM-DD filters on [date_time_fields] without getting an error

• Numbers

  • [number] < #

  • [number] >= #

  • [number] = #

  • [number] between # and #

AND

By default, Cognos applies each filter as an AND with all the other filters in the query.

Example:

• [Shirt] = ‘Red’

• [Pants]= ‘Blue’

• [Shoe]= ‘Green’

A report with the above filters will only return outfits that have a red shirt AND blue pants AND green shoes.

OR

Cognos does make it easy to combine filters for OR statements

Example:

• [Shirt] = ‘Red’

• [Pants] = ‘Blue’ OR [Shoe] = ‘Green’

A report with the above filters will return outfits with (1) red shirts and blue pants, regardless of shoe color OR (2) red shirts and green shoes, regardless of pants color.