Print Page | Close Window

Help design Paystub reprint

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12201
Printed Date: 05 May 2024 at 9:33am


Topic: Help design Paystub reprint
Posted By: NewbieCom
Subject: Help design Paystub reprint
Date Posted: 01 Feb 2011 at 4:07am
Hello I am new to writing in Crystal Reports. I am trying create a check stub report. I am using SQL Server 2000 and my raw data is from Dynamics GP.  I am having a hard time formatting the report with the way the data is in the tables.  In the detail section of the report I am trying to put the Paycode info on right side of the detail band and deductions on the left hand side.  My data looks likes this
Paycode  Paytype       hrs           totalamount     employid    chekdate  etc
Paycode  Paytype       hrs           totalamount     employid    chekdate  etc
Paycode  Paytype       hrs           totalamount     employid    chekdate  etc
.
.
.
Paycode  Paytype       hrs           totalamount      employid    chekdate etc
 
Paycode can be a deduction, benefit, state tax, or an actual paycode.  The paytype is what determines if its is a deduction, benefit,state tax, and etc.  So on the report in the detail band everything will be repeated on the same side.  Any ideas as to how I can seperate them into the columns.  I even tried redo a query with subquery and that doesn't come out very well either.  HELP  I am taking ideas.
 
Thanks
 


Replies:
Posted By: DBlank
Date Posted: 01 Feb 2011 at 4:29am
If i understand you correctly you have mutiple rows that make up '1 pay stub'. YOu need to turn these rows into columns into that '1 pay stub', is that accurate?
Usually for his kind of thing you would group on a unifying field (like checkno) suppress the details and do eveything in the group footer.
You can use Running Totals or formula fields to get the ttoal values from all of some rows and place them wherever you want in the group footer.

Posted By: NewbieCom
Date Posted: 02 Feb 2011 at 3:53am
Hi DBlank! Yes that's is my data issue.  But how can you get the group header to print multiple rows.  Say for instance I might have 3 paycodes and 3 deductions, how will I get that to repeat in the group header.  I have tried creating couple of queries to seperate them out and put it on the detail band, but its not looking like it will work.

Posted By: NewbieCom
Date Posted: 02 Feb 2011 at 7:58am
I decided to try reposition the boxes depending on a certain condition.  I am trying to accomplish this by using the formula to move adjust the x on the position.  I basically want to move that box horizontally if the condition met.  What am I doing wrong.  This is my formula:
 iif({Command_1.pyrlrtyp}=2,5.800,0.000)
 
But it is not working.

Posted By: DBlank
Date Posted: 02 Feb 2011 at 12:03pm
Basically you can create different Running Totals (RTs) to calculate the rows that you want based on conditions your define and then place the RTs in the Group footer in the location you want to dispaly that type of data.
Can you give a real life example of the data rows and then how you want them to appear?

Posted By: NewbieCom
Date Posted: 03 Feb 2011 at 5:45am
I am posting the data I am working with.  This may sound real dumb, but I am not understanding how I can get more than one record of the same type to show in the group header or footer.
 
 
                       Check         Pay        Pay
ID         check #        Date            code       type     hours         rate          amount         YTD Pay        YTD Ded
001111   111111     2011-01-14   OVR          1       4.000       17.987        71.950            71.950             0.000
001111   111111      2011-01-14  REG           1       116.000   11.991       1390.960        2829.88             0.000
001111   111111      2011-01-14  DEFERR     2          0.000       0.000         25.000            0.000           50.000
001111   111111      2011-01-14  MED          2          0.000       0.000        152.000            0.000         304.000
001111   111111      2011-01-14  RET           2          0.000      0.000       131.660             0.000         273.250
 
 
I want the data in the report to look like this (I am not dead set on this way if I have to change it)
 
Pay       Hrs           Rate           Current      YTD                                Deduction      Amt      Ytd
OVR        4.00           17.987           71.950         71.950                              DEFERR            25.00      25.00
REG       116.00        11.991          1390.960       2829.88                             MED               152.00      304.00
                                                                                                              RET                131.66      273.25
 
Taxes                        Current                     YTD
Federal                          85.00                         85.00
State                             45.23                         45.23
 

 

Posted By: DBlank
Date Posted: 03 Feb 2011 at 6:06am
Here is a sample for 1 part but the concept applies to all of them
create a Running Total
name= PAY_OVR_HRS
field to summarize = hours
type of Summary= SUM (assuming you want a total if there were more than 1 row of this type per group) otherwise you can use MAXIMUM
Evaluate = use a formula
{table.pay_code} = 'OVR'
Reset = On change of group - select your check no group
Place in the group fotoer where you want to show OVR hours
 
name= PAY_OVR_RATE
field to summarize = rate
type of Summary= MAXIMUM
Evaluate = use a formula
{table.pay_code} = 'OVR'
Reset = On change of group - select your check no group
Place in the group fotoer where you want to show OVR rate
 
Keep doing this for each item replacing the evlauate formula with different types
Does this help?
 

Posted By: NewbieCom
Date Posted: 03 Feb 2011 at 7:15am
Thanks, it worked, but if Ireset it on the group then since, which is checkdate, it triples the totals.  If I change it to something else like on a field it does not display anything. Its blank

Posted By: NewbieCom
Date Posted: 03 Feb 2011 at 7:17am

I also tried using two seperate commands one for each type and trying to use two detail bands, but that did not work at all. I put information from one command in one band and then information from the 2nd command, and it just repeated the first command over again.

Posted By: DBlank
Date Posted: 03 Feb 2011 at 7:23am
you need another group level to keep one check together as I assume you only want the 'stub' per check.

Posted By: DBlank
Date Posted: 03 Feb 2011 at 7:28am
to get it to show zero's you can either
1. inside the RT evalaute formula chaneg the formual option to 'use deafualt values for Nulls'
or
2. Go TO FILE > Report Options and mark ' Convert Other NULL values to Defualt' to TRUE (chcek the box)

Posted By: NewbieCom
Date Posted: 11 Feb 2011 at 4:25am

Hi DBlank!  I finally got my report to work correctly.  The running total fields did not work very well.  But you suggesting another group level did.  So I was able to put group on the pay type which separated the pay codes from the deductions.  And for the header in the group report I just hide one when the group changed.  So thanks for your help and patience’s.


Print Page | Close Window