Cognos BI Tool Tips

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.