Reporting changes in multi-record data sets using the Cognos BI tool requires specific techniques to ensure accuracy and relevance, especially when dealing with dynamic data like employee job titles over time. This process often involves filtering the data to reflect the most recent changes up to a specific period.
To report the latest changes in Cognos BI, you can set up a filter using the Maximum Function, which pulls the latest date for each record. This approach is particularly useful when you need to report on data like employee job titles as of the end of a prior period, ensuring you're working with the most up-to-date information.
We'll demonstrate how to effectively use the Maximum Function in Cognos BI to report on changes, using employee data as an example. We’ll also explain how field names are structured in Cognos BI and their significance in your queries.
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.