I have a report that shows all employees (active and termed) within two companies. Due to recent company transfers, the report is now showing two lines, one line showing terminated in their old company and one line showing active in their new company.
We need the report to show only the active line if the employee has a two lines. Does anyone know a formula that can do this? We do have a status code in the report showing if the line is active or termed.
Even if the formula would be something like if "John Doe" and "Company A" then do not include that would work
The pattern you are dealing with seems to be somewhat akin to a slowly changing dimension. How you filter out the non-active records depends on what metadata/data is available. Some indication somewhere in the record that the employee has been terminated in one company would probably be necessary. You speak of "one line showing terminated in their old company". Depending on what that is could be what you would be looking for.
What thought has been given to tracking the historic data in the old company of the employees who were transferred to the new company?
This really is a modelling problem, not a report author problem, which would suggest that it cannot be ruled out that other situations could exist.
Quote from: starrynight99 on 27 Dec 2024 04:17:43 PMEven if the formula would be something like if "John Doe" and "Company A" then do not include that would work
Taking into account what bus_pass_man said (which is the most robust way to do this), in the short term you could perhaps count the number of statuses per employee in your report, and set up a filter that brings back all the employees with a count of 1, and also those employees with a count of 2 and a status of 'Active'. eg:
count([status code] for [Employee no]) = 1 OR (count([status code] for [Employee no]) = 2 AND [status code] = 'Active')
Let us know if this gets you any closer...
Cheers!
MF.
Quote from: bus_pass_man on 29 Dec 2024 05:58:33 AMThis really is a modelling problem, not a report author problem, which would suggest that it cannot be ruled out that other situations could exist.
If you are working in Report Studio, you could model this out using multiple queries, and specified joins. Effectively, doing the customized modelling in the report. If often do this to prove my concept, in order to sell the concept the modelling team.
In one query find all of the records, in the other query find the predictably incorrect records, and then use an union exclude to rid the good query of the bad records. Its not elegant, but it can prove to your modeller that they need to augment the model.
Just my 2 cents...
Given this current output:
| EmployeeId | EmployeeName | Status | Company |
| A00001 | Alice | Active | B |
| A00002 | Wally | Terminated | A |
| A00003 | Dilbert | Terminated | A |
| A00003 | Dilbert | Active | B |
| A00004 | Pointy Haired Boss | Active | A |
|
Adding these items:
Data Item:
rnk
rank ([Status] ASC for [EmployeeId])
Filter:
[rnk] = 1
Produces:
| EmployeeId | EmployeeName | Status | Company |
| A00001 | Alice | Active | B |
| A00002 | Wally | Terminated | A |
| A00003 | Dilbert | Active | B |
| A00004 | Pointy Haired Boss | Active | A |
|