Point in time report filter

[reportit]

Hello,

I'm working point-in-time report and would like to add department filter so leadership can see counts by dept.  I'm pulling the department field from our history data but I'm having issues with the filter I added through.  I started by creating the department filter.  When I run the report for our 'Sales' dept, the report includes employees previously in our 'Sales' dept; these employees should not be in the report since they were not in 'Sales' on the date selected.

Any ideas on what I can do to fix it?

[bus_pass_man]

From the sounds of it, it looks like you are encountering the modelling pattern of a slowly changing dimension.

It is possible that the ETL and the model have been set up to support the SCD type II case, where historic data is tracked. You would need to establish what has been implemented.

You need to find out whether your model has something akin to a current record so you can filter by that so that only the current records, which would record which department /position etc. each employee is currently in, would be returned.

Another thing to worry about is that this sort of report touches on semi-aggregates and I don't think Cognos does it very well in relational (it does it very well in DMR though)

Assuming that you can filter so that your measure is counting for each department that possibly won't be a worry and you would then get the correct current counts of employees for each department.