| Author |
Message |
NewbieCom
Newbie
Joined: 01 Feb 2011
Location: United States
Online Status: Offline
Posts: 7
|
   Topic: Help design Paystub reprint
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
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
|
IP Logged |
|
NewbieCom
Newbie
Joined: 01 Feb 2011
Location: United States
Online Status: Offline
Posts: 7
|
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.
|
|
IP Logged |
|
NewbieCom
Newbie
Joined: 01 Feb 2011
Location: United States
Online Status: Offline
Posts: 7
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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?
|
|
IP Logged |
|
NewbieCom
Newbie
Joined: 01 Feb 2011
Location: United States
Online Status: Offline
Posts: 7
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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?
|
|
IP Logged |
|
NewbieCom
Newbie
Joined: 01 Feb 2011
Location: United States
Online Status: Offline
Posts: 7
|
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
|
|
IP Logged |
|
NewbieCom
Newbie
Joined: 01 Feb 2011
Location: United States
Online Status: Offline
Posts: 7
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
|
IP Logged |
|
|
|
