Technical Questions: Grouping

Grouping

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11137
Printed Date: 03 May 2024 at 3:22pm

Topic: Grouping

Posted By: Zuzanna
Subject: Grouping
Date Posted: 18 Sep 2010 at 5:57am

I have A report grouped by deduction code(S100). The detail data looks similar to the following:

EmplId Ded Code PayEnd Dt EmplyeContr EmplyrContr DedType

1010 S100 2010/09/09 0.0 25.00 2

1010 S100 2010/09/09 10.00 0.0 1

1010 S100 2010/10/09 0.0 25.00 2

1010 S100 2010/10/09 10.00 0.0 1

The user wants to see the last/highest PayEnd Dt Employee and Employer Contribution.

ie.

1010 S100 10.00 25.00

I put the detail field({SJ000146.LHS_DED_EMPLYE_SHR} and {SJ000146.LHS_DED_EMPLYR_SHR} on the group line and I when I run the report I see

1010 S100 10.00 0.0

I set up the following formula, but it doesn't work, the Employer Share gets overwritten by the last row:

If {SJ000146.LHS_DED_TYPE} = '2' Then
If {SJ000146.LHS_DED_EMPLYR_SHR} > 0 Then
{SJ000146.LHS_DED_EMPLYR_SHR}

I still see the following when I run the report

1010 S100 10.00 0.0

How do I save off the Employer Contribution, so it shows on the report (global variable ?).

Thanks in advance.

Replies:

Posted By: Senthil Raj
Date Posted: 19 Sep 2010 at 9:16pm

if you directly put the detail fields in the group line it will show you the last record fetched...

so you use maximum of EmplyeContr and EmplyrCont in the group line(footer)

-------------
Live And Let Live...

Posted By: Zuzanna
Date Posted: 20 Sep 2010 at 2:34am

The last record fetched has 0.0 in the Employer contribution.

Posted By: Senthil Raj
Date Posted: 20 Sep 2010 at 6:52pm

that's right

use maximum of EmplyeContr and EmplyrCont in the group line(footer)

-------------
Live And Let Live...

Posted By: Zuzanna
Date Posted: 21 Sep 2010 at 3:46am

I have a group on the ded code within that are rows with different Pay End date. I need to see the EmplyeContr and EmplyrCont from the last/highest Pay End date. It is not necessary the maximum one. Other totals are summarized by the ded code.

EmplId Ded Code PayEnd Dt EmplyeContr EmplyrContr DedType

1010 S100 2010/09/09 0.0 25.00 2

1010 S100 2010/09/09 15.00 0.0 1

1010 S100 2010/10/09 0.0 15.00 2

1010 S100 2010/10/09 10.00 0.0 1

Sorry, I just noticed that on the report that it could be different.

I created 2 formulas, first one in the detail

Global NumberVar ER_Share;
If {SJ000146.LHS_DED_TYPE} = '2' Then
If {SJ000146.LHS_DED_EMPLYR_SHR} > 0 Then
ER_Share := {SJ000146.LHS_DED_EMPLYR_SHR};

Second one in the ded code group

Global NumberVar ER_Share;
ER_Share;

I still get 0.0 in EmplyrContr.

Posted By: DBlank
Date Posted: 21 Sep 2010 at 11:50am

SInce you requested help on this from the other post...

If I understand you correctly, Senthil was giving you good feebback.

I assume you want this per employee and also per deduction type

You need top grroup on Deduction type then on EMployee ID

in the group footer 2 (employee id level) you need to use 2 different formulas to show the 2 values

Maximum(EmplyeContr,emplID)

MAXIMUM(EmplyrContr,emplid)

Is this what you are trying to do?

Posted By: Zuzanna
Date Posted: 22 Sep 2010 at 2:39am

Hi,

yes I am grouping on EmplId and Ded Type. The user wants to see the EE and ER contributions from the highest Pay End Date on the Ded Type group level. The maximum worked, but gave me the highest contribution within the Ded Type group instead of the contribution from the highest Pay End Date within one Ded Type group. I've been having trouble with the formulas, so please help. ie from the following

EmplId Ded Code PayEnd Dt EmplyeContr EmplyrContr DedType

1010 S100 2010/09/09 0.0 25.00 2

1010 S100 2010/09/09 15.00 0.0 1

1010 S100 2010/10/09 0.0 15.00 2

1010 S100 2010/10/09 10.00 0.0 1

I need to see on the S100 group lebel EE contr 10.00 and ER contr 15.00

Thanks !

Posted By: DBlank
Date Posted: 22 Sep 2010 at 4:30am

Ahhh, you need it to be the value from the maximum date not just the maximum value of the group.

try a Running Total

name=EE

field to summarize=EmplyeContr

Type=MAximum

Evaluate=Use a formula

MAXIMUM(PayEndDt,EmplId)=PayENdDt

Reset=Group level 2 (EmplID)

Place on group footer 2

repeat for the othe rbu change the field to summarize

name=ER

field to summarize=EmplyrContr

Type=MAximum

Evaluate=Use a formula

MAXIMUM(PayEndDt,EmplId)=PayENdDt

Reset=Group level 2 (EmplID)

Place on group footer 2

Posted By: Zuzanna
Date Posted: 22 Sep 2010 at 10:26am

Thanks, it worked ! You're the best.

Posted By: Zuzanna
Date Posted: 23 Sep 2010 at 10:33am

Hi,

I had a request for additional change to my report. The user wants to show Current Ded summ on the report. The rows can also come in the following sequence ie.

EmplId Ded Cd PayEnd Dt EmplyeContr EmplyrContr Curr Ded DedType

1010 S100 2010/09/09 0.0 25.00 10.00 2

1010 S100 2010/10/09 0.0 15.00 12.00 2

1010 S100 2010/09/09 15.00 0.0 10.00 1

1010 S100 2010/10/09 10.00 0.0 8.00 1

I need to see on the S100 Ded Cd group level EE contr 10.00 and ER contr 15.00 (this works great) and

new request - Curr Ded 20.00 ( summ of 12.00(from highest PayEnd Date Ded Type 2) + 8.00(from highest PayEnd Date Ded Type 1)

Thanks in advance.

Posted By: DBlank
Date Posted: 23 Sep 2010 at 10:41am

Create 2 RTs to get the 12 and 8 values then SUm those to RTS in a formula

name=Formula1 (or whatever you want)

field to summarize=CurrDed

Type=MAximum

Evaluate=Use a formula

MAXIMUM(PayEndDt,EmplId)=PayENdDt and DedType=2

Reset=Group level 2 (EmplID)

name=Formula2 (or whatever you want)

field to summarize=CurrDed

Type=MAximum

Evaluate=Use a formula

MAXIMUM(PayEndDt,EmplId)=PayENdDt and DedType=1

Reset=Group level 2 (EmplID)

Formula field is {#Formula1} + {#Formula2}

Place on group footer 2

Posted By: Zuzanna
Date Posted: 24 Sep 2010 at 6:52am

Hi,

this works when the employee has both Type 1 and Type 2 deductions, but if there is only 1 particular ded per ded code/employee, I seem to lose the amount and it shows as nothing.

ie.

EmplId Ded Cd PayEnd Dt EmplyeContr EmplyrContr Curr Ded DedType

1000 S100 2010/09/09 0.0 25.00 10.00 2

1000 S100 2010/10/09 0.0 15.00 12.00 2

1000 S100 2010/09/09 15.00 0.0 10.00 1

1000 S100 2010/10/09 10.00 0.0 8.00 1

The above works, curr ded shows as 20.00 in Group2 footer

1010 S100 2010/09/09 15.00 0.0 10.00 1

1010 S100 2010/09/15 15.00 0.0 10.00 1

1010 S100 2010/10/09 15.00 0.0 10.00 1

The above doesn't work, curr ded shows as nothing istead of 10.00

I created RTs for Type 1 and Type 2 curr ded and on the Formula field I put

If {#EE_Curr_Ded} > 0 And
   {#ER_Curr_Ded} > 0 Then
{#EE_Curr_Ded} + {#ER_Curr_Ded}
Else
If {#EE_Curr_Ded} > 0 Then
    {#EE_Curr_Ded} Else
   If {#ER_Curr_Ded} > 0 Then
     {#ER_Curr_Ded}

I am still getting nothing in Group2. I want to see 10.00, it still works when the empl has both 1 and 2 type.

When I display the #EE_Curr_Ded and #ER_Curr_Ded in Group2 footer, it displays 10.00 in #EE_Curr_Ded. Why is the formula not working ?

Posted By: DBlank
Date Posted: 24 Sep 2010 at 8:15am

in your formula that adds EE and ER change the pick list setting about how to handle NULLS to 'Use defaalt values'

formulas often 'die' on null values unless you do not tell it what to do when it hits or if you change it to defualt values

since sometimes you have ne EE or ER this would be a NULL

Posted By: Zuzanna
Date Posted: 24 Sep 2010 at 9:08am

Sorry, I don't know where to find it. Is it somewhere in the Formula Editor ?

Posted By: DBlank
Date Posted: 24 Sep 2010 at 9:55am

when you open up a formula to edit it, mine is in the upper right hand corner but that is configurable. it is a pick list with 2 options in it.

Posted By: Zuzanna
Date Posted: 24 Sep 2010 at 10:39am

I am on version 9. Is it possible that it's somewhere else ? Is it some kind of a tab called pick list ? I am using Crystal Syntax or does it matter ?

The only thing I see is under File>Options - under When reading data, check box for either Convert Database NULL Values to Default or Convert Other NULL Values to Default.

There is also under File>Report Options>General Settings - check box for Convert Database NULL Values to Default and another one for Convert Other NULL Values to Default.

Thanks.

Posted By: DBlank
Date Posted: 24 Sep 2010 at 11:50am

it is hidden in there somewhere but you can try this instead.

(if isnull({#EE_Curr_Ded}) then 0 else {#EE_Curr_Ded})

(if isnull({#ER_Curr_Ded}) then 0 else {#ER_Curr_Ded})

Posted By: Zuzanna
Date Posted: 27 Sep 2010 at 5:48am

It worked. Many thanks.