Combine employee data to 1 row per employee... 1

[abowling]

Hello,

I know this question has been asked several times, but I was unable to find the answer. I am on Reportsmith, Oracle. I have a report that has several different earnings codes; therefore pulling multiple rows per employee. I have created separate derived fields for each earning code, so that now I have separate columns for each, but there are still multiple rows per employee. What is the derived field to combine into one row? (see below) thanks!!
code1 code 2 code 3
ee1 500
ee1 250
ee1 250

 

[RSHelp]

If you have multiple earnings codes you will have to do a "hidden detail" report.

Insert a footer for each employee
Sum each column
Insert the name into the footer
Hide the details on your report (Format » Section » Detail » check Hide Section)

You will have to do some clean-up of the alignment but then you are only seeing the footer and in effect have one line per employee. Make sure if you export this to Excel or something you check the Retain Formatting box or your detail will come back.

 

[abowling]

Thanks for the answer, however I need to export it to excel, and when I try to do this while checking the "retain formatting" box to download only the totals, it bombs out my Reportsmith. I know there is a derived field that will do this, I'm just not sure what it is.

 

[Helixe]

I avoid saving to an Excel file directly in ReportSmith. This may be the problem with your file bombing out. It might be a matter of file size. I normally save to a Text file with "retain format". I then use Excel to open the Text file. The app will prompt you to choose between delimited or fixed width. Choose delimited then tab. You might want to play with alignment in ReportSmith within the report footer. When opening the file in Excel, you'll sometimes notice that data is not lined up.

 

[CharlesCook]

You could also use Data Base Grouping to get it down to one record per person.

Check out :

http://charlescook.com/PPS/ReportSmithandADPPCPayrollforWindows-DatabaseGrouping.pps

CharlesCook.com
Specializing in ReportSmith Training and Consulting

 

[maryb701]

if i run a report showing various paydates for an employee for a quarter, how can i combine them to one line per employee. The accumulator cant be used because it is for a previous quarter.

example:

J.Smith 1/1/07 $300
J.Smith 1/17/07 $500
J.Smith 2/4/07 $600

can i get this as a sum on one record?

 

[CharlesCook]

Did you look at my Data Base Grouping power point?

Check out :

http://charlescook.com/PPS/ReportSmithandADPPCPayrollforWindows-DatabaseGrouping.pps

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[maryb701]

yes and I can't get it to work with EV4 reportsmith. Unless I am not understanding something.

 

[CharlesCook]

It was created for PCPW but the ideas work for all versions.

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[ArtieArt]

You are probally using Decode in the formula with out
Sum(Distinct in front of it. If so, highlite the formula and change String Functions to Summary and double click Sum(Distinct. This will put the function in the formula correctly. Then go to table and make sure the Earning Code and Earnings amout are Query Only and lastly reset Database Grouping and that should do it.....

Artie Dunnings
ADTECH Consulting Inc
HRIS Functional & Technical Trainer
Alpharetta, GA

 

[maryb701]

Thanks. I will check that out

 

[maryb701]

Ok i did that...but I am still getting one line per check date rather than one line.

these are my derived fields:

Ck Gross: SUM(DISTINCT "PS_AL_CHK_DATA"."CK_GROSS")

StateTax: SUM(DISTINCT DECODE("PS_AL_CHK_DATA"."STATE_TAX_CD",'31',"PS_AL_CHK_DATA"."STATE_TAX_AMT",0 ) )

LocalTax: SUM(DISTINCT DECODE("PS_AL_CHK_DATA"."LOCAL_TAX_CD",'222J',"PS_AL_CHK_DATA"."LOCAL_TAX_AMT",0 ) )

I have the fields in query only

my selections are for certain locations; ck date range;and a certain paygroup.

my tables selected:
PS_personnel
PS_AL_CHK_DATA
PS_AL_CHK_HRS_ERN
PS_AL_CHK_DED

i was able to group the deductions to go on one line per pay but i need to group various paychecks for an employee to one total on one line.

 

[maryb701]

one more thing...it does not seem to be pulling everyone up

 

[ArtieArt]

If you can call me I can walk you through it in less then 10 minutes. You have allot going on. You need outer joins between the Check_Data and Chk_DED tables and your probally loosing people that don't have any deductions for take pay_date There are 6 link between the two and well as the CHK_HRS_ERN. All fields with in the Check tables must be Query Only except the check_date, you can include that.
678-772-3058. I would be glad to help...

Artie Dunnings
ADTECH Consulting Inc
HRIS Functional & Technical Trainer
Alpharetta, GA

 

[maryb701]

thanks Artie, i will call when i get in tomorrow.

 

[maryb701]

Artie ! thanks for the help on the phone. YOU ROCK!!!!!

 

[sherin113]

Hello,

Would you be so kind to send me this report you built? I have to do a similar one and always have trouble with database grouping. This would be a tremendous help if you can.

Kind Regards.