Duplicate Records

[Adele75]

I have a reports with a few groups (employee number and Cost Centre), in the details section I get multiple records for each person due to salary changes.

I want to select each person if they are current or not (despite having them appear more than once in the details section). I have been using a formula which allocates 1 or 0 depending if they are current staff or not.

i.e.
Name Start Date Leave Date Current Staff Salary
Jo Bloggs 01/01/03 1 15,000
Jo Bloggs 01/01/03 1 16,000
Mo Bloggs 01/01/99 31/12/01 0 22,500
Mo Bloggs 01/01/99 31/12/01 0 25,000
Mo Bloggs 01/01/99 31/12/01 0 27,500

If I do a count or sum on the Current Staff formula it includes the multiple records. Any ideas on how to make this work?

Any help would be much appreciated

CR 9
SQL

Adele

 

[lbass]

You could use a running total. Let's say your current staff formula is {@current}. Then choose this as the field, sum (not count, since a count will include "0" results, too), evaluate on change of group (EmployeeNumber), reset on change of group or never, depending on the subtotal level you are looking for.

-LB

 

[Adele75]

Thank you for your help. That has solved that problem. I have now come across another.

I now need to create a formula that calculates if the person is a starter in a Parameter driven date range. Using the same info above, if the date range is 01/12/02 to 31/01/03, I need Jo Bloggs to appear as a starter but to also show that his salary has changed. (There is a date field for date of salary change)

Every time I get the right figures out of the formula I can't summarise it as it contains things that can't be summarised or totaled using Running totals (ie other formulas). Which I need to be able to do to give total figures for Starters, Current Staff, Salary Changes etc.

Thanks for any help....... until then I will keep struggling.

 

[lbass]

Please define what you mean by "Starter." Please also explain whether you need to include former employees in your report--these could be filtered out in your record selection formula. Don't know what you mean by needing to "show that salary has changed"--what do you want to display? Also share your formula for {@current}.

In general, if you are using running totals within groups and then need to summarize across groups, you develop a second set of running totals and change the reset to "Never" or to the higher order group that you want to summarize.

-LB