Copyright © 2019 Vintage HIll Consulting. All rights reserved

  • LinkedIn Social Icon
  • Facebook Social Icon

Cognos BI Tool Tips

Updated: Jul 29, 2019

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.

30 views