Employee Wages Report Problems

[maxrisc]

I'm new to crystal reports this week and got this far in a report that our payroll department needs. This is how I have it setup so far.

1st 2nd 3rd Pay Date
Division
Program
Site
Smith, John Q 100.00 0.00 0.00 1/8/2003
Smith, John Q 100.00 0.00 0.00 1/20/2003
Jones, Bob A 0.00 0.00 50.00 3/14/2003
Jones, Bob A 0.00 0.00 50.00 3/29/2003
Jones, Bob A 0.00 150.00 0.00 2/10/2003
Jones, Bob A 0.00 150.00 0.00 2/24/2003
Jones, Bob A 100.00 0.00 0.00 1/8/2003
Jones, Bob A 0.00 0.00 0.00 1/20/2003
Bing, Mary B 0.00 0.00 40.00 3/14/2003
Bing, Mary B 0.00 0.00 60.00 3/29/2003
Bing, Mary B 0.00 0.00 0.00 2/10/2003
Bing, Mary B 0.00 0.00 0.00 2/24/2003
Bing, Mary B 50.00 0.00 0.00 1/8/2003
Bing, Mary B 40.00 0.00 0.00 1/20/2003

Site Total: 390.00 300.00 200.00

Site2…



I have 3 "Grouping Levels" setup Division, Program and Site. And each Site needs subtotaled. I got that part but I am having problems with the following...
Question 1 - I would like the “3 months” to report on one line and automatically subtotal the 2 pays (or some months 3 pays) in each month. So, for each person only have one line with the total gross. (the "pay date" is not in my report, I just added it here to show where the numbers were coming from). I have the information i want for this part but i would like to clean it up and make the report smaller. I want it to look like this:

Smith, John Q 200.00 0.00 0.00
Jones, Bob A 100.00 300.00 100.00
Bing, Mary B 90.00 0.00 100.00

Right now my formula for the 1st Gross is: (2nd and 3rd are same with the month and days changed.)

IIf({PRCKHIST.CHKDATE}>=#01/01/2003# and {PRCKHIST.CHKDATE}<=#01/31/2003#,{PRCKHIST.TOTALGROSS}-{PRCKHIST.REIMBPAY},0)

Question 2 - I have no idea how to do this but I need a count if they got a pay or not for a month and need that subtotaled by site. Every time I tried to do a count I ended up with a count on all records so I am not sure where to begin with this part. In my previous example I would want it to look like this

Site 1 Jan Feb Mar Jan Feb Mar
Smith, John Q 200.00 0.00 0.00 1 0 0
Jones, Bob A 100.00 300.00 100.00 1 1 1
Bing, Mary B 90.00 0.00 100.00 1 0 1
-----------------------------------------------------
Totals 390.00 300.00 200.00 3 1 2


Any help would be appreciated,
Allen H

 

[Naith]

Allen,

Very nicely explained post.

If we introduce some variables into your report, I think you'll find your solution.

Q1[\u]

(a) Under Site, group by Employee.

(b) Suppress the details section, and the Employee group header.

(c) In your suppressed details section, place the following formula:

WhilePrintingRecords;
NumberVar Jan;
NumberVar Feb;
NumberVar ...etc...
NumberVar Dec;

IIf(Month({PRCKHIST.CHKDATE}) = 1 and Year({PRCKHIST.CHKDATE}) = Year(CurrentDate),Jan := Jan + {PRCKHIST.TOTALGROSS}-{PRCKHIST.REIMBPAY},Jan := Jan);

Repeat the same approach for the remaining months. Note; it's usually better to refer to the months using a combination of the month and year functions rather than hardcoding dates - especially where February is concerned...

(d) In your Employee group footer, create a formula for each month:

WhilePrintingRecords;
NumberVar Jan;

Create an exclusive formula for each month and place in the appropriate columns.

(e) In your Employee group header, reset the month variables:

WhilePrintingRecords;
NumberVar Jan := 0;
NumberVar Feb := 0;
NumberVar ...etc...;

Q2[\u]

(a) In the Employee group footer, create a boolean formula as below:

WhilePrintingRecords;
If NumberVar Jan > 0
Then True
Else False;

As before, create an exclusive formula for each month.

Right click the monthly booleans, and insert a running total, set to count with a condition of True.

All the best,

Naith

 

[Naith]

Whoops! Sorry about the bad format. Hopefully this version is a bit easier on the eye:

Allen,

Very nicely explained post.

If we introduce some variables into your report, I think you'll find your solution.

Q1

(a) Under Site, group by Employee.

(b) Suppress the details section, and the Employee group header.

(c) In your suppressed details section, place the following formula:

WhilePrintingRecords;
NumberVar Jan;
NumberVar Feb;
NumberVar ...etc...
NumberVar Dec;

IIf(Month({PRCKHIST.CHKDATE}) = 1 and Year({PRCKHIST.CHKDATE}) = Year(CurrentDate),Jan := Jan + {PRCKHIST.TOTALGROSS}-{PRCKHIST.REIMBPAY},Jan := Jan);

Repeat the same approach for the remaining months. Note; it's usually better to refer to the months using a combination of the month and year functions rather than hardcoding dates - especially where February is concerned...

(d) In your Employee group footer, create a formula for each month:

WhilePrintingRecords;
NumberVar Jan;

Create an exclusive formula for each month and place in the appropriate columns.

(e) In your Employee group header, reset the month variables:

WhilePrintingRecords;
NumberVar Jan := 0;
NumberVar Feb := 0;
NumberVar ...etc...;

Q2

(a) In the Employee group footer, create a boolean formula as below:

WhilePrintingRecords;
If NumberVar Jan > 0
Then True
Else False;

As before, create an exclusive formula for each month.

Right click the monthly booleans, and insert a running total, set to count with a condition of True.

All the best,

Naith

 

[lbass]

I think you could just group on Employee and then insert a summary by right clicking on each of your month formulas (without using "whileprintingrecords&quot and inserting a sum at all group levels and at the grand total level. Then suppress the details. For your three counts to the right, create three formulas in this format and place them in the group footer, also:

if sum({@Jan},{table.employee}) > 0 then 1 else 0 //where {@Jan} is your detail formula for January.

-LB

 

[IdoMillet]

You can also achieve the desired results by simply inserting side-by-side CrossTabs.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[maxrisc]

Thank you all for your responses.

lbass,

It worked for almost everything, but, i need a total for that count at each site and it will not let me total the sum. :-(
Can any one give me some advise on how to count that total?

Naith,

I tried using your suggestion but the logic is way over my head right now and i couldn't get the totals right. I think it was the way i got to this point in my report. I used the select expert to choose the Jan, Feb, Mar totals that i needed and when I used your formula it gave me a total of all three columns. When i narrowed the select expert down to Jan it gave me the correct totals so i know it worked but i just don't know how to use them. If i remove the select expert all together it totals everything.

All,
Thanks again for the help, and could you recommend a book on Crystal Reports? I'm currently using Ver 8.5 and I'm looking for something that will cover the basics a little and then get into the advanced programming and functions. Kinda and overall reference to Crystal Reports. Also how similar is this to Visual Basic? Would it benefit me to learn VB for Crystal Reports or is that overkill?

Allen

 

[lbass]

You could try something like this. Let's call my earlier formula {@countJan}. For the site total, create three formulas:

{@resetsumJan} to be placed in the group (site) header:
whileprintingrecords;
numbervar sumJan := 0;

{@sumJan} to be placed in the group (Employee) footer:
whileprintingrecords;
numbervar sumJan := sumJan + {@countJan};

{@displaysumJan} to be placed in the group (site) footer:
whilerprintingrecords;
numbervar sumJan;

Repeat for remaining two months.

-LB