Ciphr : How can I use the same field twice in a Ciphr report to show different values (e.g. point in time and current)?

You may wish to include the same field more than once in one report, in order that different values can be reported on – one example would be wanting to show someone’s current department information alongside their department information as at a point in time.

Simply adding a field to the report and using it twice will not suffice – the same value will appear in the field in both places. It is however possible to use a different method to link to the same field from two different ‘starting points’, for example from Personal Details and Job and Pay Details fields.
For the purpose of this article, we will look at a relatively straightforward point in time Job and Pay Details report which shows a person’s department and division as at a point time, to which we wish to add the person’s current department and division details.

The image below shows the field selection for the report. Note the inclusion of the Department table as an additional table – this is key to the method in which we will be able to show two different values for the Department and Division for a person:

In the report design screen, using the fields ‘Department’ and ‘Division’ from the ‘Department’ table will show the person’s Point In Time department and division – with the database linking working as follows:

It is then possible to create a second link from the Personal Details table to the Department_1 table:

This means that when the ‘Orgunit1Name’ and ‘OrgUnit1Orgunit2Name’ fields (Department Name and Division Name respectively) are added into the report from the ‘Department_1′ table, the values which are brought through are the current values for the person:

The following screenshot shows an example of the report data: