Removing duplicate records for terminated employees

[former_member238607]

Hi,

I'm relatively new to BI/BW and I'm having an issue with an existing WebI HCM report.

One of the fields being reported on is termination date.  The report should show one line per employee, but terminated employees are displaying two lines - one with the termination date and one without.

Can anyone tell me how I can suppress the second line (either in WebI or BeX) and only show the one with the termination date?

Thanks for your help!

[Former Member · Accepted solution]

You could select the whole block and assign a report filter (termination date is not null).That should suppress the one's which doesnt have a date field on them and should solve ur issue.

Let me know if this worked for you.

Cheers!

[former_member238607 · Accepted solution]

I can't filter on termination date, because the report needs to include both active and terminated employees.  It is calculating the value of a year-end payout, but terminated employees won't receive one.  I can suppress the calculation on the rows that have a termination date, but since these employees have a second (earlier) record without a termination date, the system calculates a payout for them, causing invalid totals on the report.  The report pulls data by year, and H.R. wants to see all employees for the year (both active and terminated).

I had a similar issue in this report for rate of pay.  I was getting two records back on employees that had a pay change during the year.  I was able to fix that by creating a calculated key figure and using exception aggregation of 'Last Value'.  I don't see any way to do that with termination date since it is a characteristic.

The BeX Query had termination date in the rows; I tried moving it to free characteristics but that did not fix the issue.

[Former Member · Accepted solution]

How about if we approach with a new variable like

new variable= if (employer=active and date= null) then "dont delete"
                     else if (employer=terminated and date=null) then delete
                     end

now create a report filter on the new variable.

Let me know if this makes any sense.

Cheers!

[Former Member]

Hi Jeff,

You can count the number of rows for the termination date. I assume you have data somewhat like below. perform the below steps

Employee ID     Termination Date     Variable 2

1                        12/31/2012            1

1                                                    0

2                                                    1

3                                                    0

3                         11/15/2012           1

Variable 1 =Count(Termination Date) In(Employee ID)

Variable 2 =If(variable 1>1 and IsnUll(terminationdate);0;1)

Place variable 2 next to termination Date column and make sure variable is evaluating correctly. you should see 0 and 1 as shown above. Filter on Variable 2 to show only 1 yo should get one row per employee.

Hope this helps