Cognos BI Tool Tips
Updated: Jun 14, 2021

When reporting on multi-record data sets with the Cognos BI tool and you need to report the latest change, set up a Filter that pulls the latest date. An example of this would be a report where you need a report of all Employees and their Job Titles as of the end of a prior period.
To do this, you need to use the Maximum Function along with the Employee Number.
The format is:
[Business Layer].[Employee Job History].[Effective Date]
=
Maximum([Business Layer].[Employee Job History].[Effective Date] for [Business Layer].[Employee].[Employee Number])
If you are not used to seeing a field name such as [Effective Date] with the additional identifiers [Business Layer].[Employee Job History], this indicates that the field name is not included in Query. The filter still functions the same but your Query does not contain with unnecessary fields.