0

How do I consolidate duplicated codes?

Jet Reports Historic Posts

April 02, 2013 18:43

None

Follow

I am trying to extract paycheck detail information to send to an external employee portal so employees can start receiving their paychecks online.

I am doing a data dump for the payroll ledger entry table. The problem is that some of my codes for each employee are duplicated as we break out things by dimension so if an employee worked at 3 locations, their health insurance is broken out on 3 lines. How can I consolidate each code into one line to simplify their paycheck stub? I know how to sum it to get the correct amount on one line but I can't figure out how to either hide the duplicates or not have them pulled in at all.

Thanks for any help anyone can give me with this.

Holly

• Facebook
• Twitter
• LinkedIn

5 comments

• 
  Jet Reports Historic Posts April 04, 2013 07:23 Official comment

  Hi Holly,
  
  Using Hughes example, this will get you the employee codes where your entries are equal to your filters so if there are no entries, it will not produce an employee code.
  Taking this list, use another NL(rows) function now on the Ledger entries with the filter = the first NL function which will then create a row for each payroll code per employee code and then you can sum.
  
  Assume cell C11 =NL("Rows","Employee","No.","Link=","Payroll Ledger Entry","Employee No.","=No.","Posting Date",$C$2,"Payroll Control Code","DENTAL-ER|HEALTH-ER|LIFEADD") as per hughes.
  Assume cell D11 =NL("Rows","Payroll Ledger Entry","Payroll Control Code","Employee Code",C11,"Posting Date",$C$2,"Payroll Control Code","DENTAL-ER|HEALTH-ER|LIFEADD")
  Assume cell E11 = your SUM function
  
  Let us know if this works for you.
  
  Regards
  
  Thomas

• 0
  
  Jet Reports Historic Posts April 03, 2013 13:06

  Hi Holly,
  
  Can you post a sample of the formulas you currently have in place so we can see how you are currently getting your results.
  
  Thanks
  
  Thomas

• 0
  
  Jet Reports Historic Posts April 03, 2013 13:40

  Here is my formula:
  
  =NL("Rows","Payroll Ledger Entry",,"Posting Date",$C$2,"Payroll Control Code","DENTAL-ER|HEALTH-ER|LIFEADD")
  
  This is what the report is giving me:
  
  1000 4/3/2013 960676 PAYRO00506 LIFEADD Life and Accidental Death 3/10/2013 3/23/2013 -$$
  1000 4/3/2013 960676 PAYRO00506 DENTAL-ER Dental - ER 3/10/2013 3/23/2013 -$$
  1000 4/3/2013 960676 PAYRO00506 DENTAL-ER Dental - ER 3/10/2013 3/23/2013 -$
  1000 4/3/2013 960676 PAYRO00506 HEALTH-ER Health Care - Employer Portion 3/10/2013 3/23/2013 -$$$
  
  So, for the same employee, #1000, I have 2 Dental-ER's. Since this is just for the paycheck stub, I only need one code for each employee with the total to show up. I actually fixed this by grouping pay control codes and hiding all of the lines except the total. The problem now is all of the hidden rows. I'm not sure if the external source can pull data with hidden rows. So is there a way to have it only pull the code for each person once?

• 0
  
  Jet Reports Historic Posts April 03, 2013 16:45

  Hi Holly,
  
  If you only want to show each employee number once, this should be easy to do if you just replicate the employee numbers from the Employee table and use a Link= to link to the Payroll Ledger Entry like this:
  
  =NL("Rows","Employee","No.","Link=","Payroll Ledger Entry","Employee No.","=No.","Posting Date",$C$2,"Payroll Control Code","DENTAL-ER|HEALTH-ER|LIFEADD")
  I'm assuming there is a field in the Payroll Ledger Entry called Employee No.; if the field is called something else you might have to modify the formula slightly. Then you can do your sums for each employee or whatever from the Payroll Ledger Entry and just reference the employee number in this replicator. Does this help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts April 03, 2013 19:45

  Thanks for the response Hughes. I tried it and unfortunately, it is only giving me one line per employee and I need three lines per employee, one for each code. Any suggestions?
  
  Thanks,
  Holly