Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CONDITIONAL GROUPING 2

Status
Not open for further replies.
Ann28

Ann28

MIS
Apr 2, 2004
97
US
Hi everyone –

REALLY, APPRECIATE YOUR HELP!

Trying to create a crystal report with DB2 (IBM) back end.

Here is the data:

MEMBER_SSN CHECK CHECK_STATUS ISSUED_DATE AMT WHT_TYPE WHT_AMT WH_DESCRIPTION WH_TAX_CODE
---------- --------- ------------ ----------- ------------ -------- ----------- --------------- -----------
999999999 510855 P 03/18/2005 254.89 FICA 17.11 FICA FIC
999999999 510855 P 03/18/2005 254.89 FICA 17.11 SOCIAL SECURITY FIC
999999999 510855 P 03/18/2005 254.89 FICA 17.11 SOCIAL SECURITY FIC
999999999 510855 P 03/18/2005 254.89 MED 4.00 MEDICARE FIC
999999999 510855 P 03/18/2005 254.89 MED 4.00 MEDICARE FIC
999999999 510855 P 03/18/2005 254.89 MED 4.00 MEDICARE FIC
888888888 510821 P 01/14/2005 101.25 FEDM 0.00 FED MARRIED F
888888888 510821 P 01/14/2005 101.25 FEDM 0.00 FED MARRIED F
888888888 510821 P 01/14/2005 101.25 FICA 7.23 FICA FIC
888888888 510821 P 01/14/2005 101.25 FICA 7.23 SOCIAL SECURITY FIC
888888888 510821 P 01/14/2005 101.25 FICA 7.23 SOCIAL SECURITY FIC
888888888 510821 P 01/14/2005 101.25 NYC 1.17 NY CITY C
888888888 510821 P 01/14/2005 101.25 NYC 1.17 NY CITY C
888888888 510821 P 01/14/2005 101.25 NYC 1.17 NYC C
888888888 510821 P 01/14/2005 101.25 NYS 7.00 NY STATE S
888888888 510821 P 01/14/2005 101.25 NYS 7.00 NY STATE S
888888888 510821 P 01/14/2005 101.25 NYS 7.00 NYS S
111111111 510823 P 01/14/2005 101.25 FED 0.00 FED MARRIED F
111111111 510823 P 01/14/2005 101.25 FICA 7.23 SOCIAL SECURITY FIC
111111111 510823 P 01/14/2005 101.25 MAS 7.00 MASS STATE S
555555555 510824 P 01/14/2005 101.25 FED 0.00 FED MARRIED F
555555555 510824 P 01/14/2005 101.25 FICA 7.23 SOCIAL SECURITY FIC
555555555 510824 P 01/14/2005 101.25 NYS 7.00 NY STATE S

The report should be grouped by the STATE & CHECK
("S" code indicates the state)
Here is how it should look:

REPORT:

GROUP: OTHER [\u]

999999999 510855 P 03/18/2005 254.89 FICA 17.11 FICA FIC
999999999 510855 P 03/18/2005 254.89 FICA 17.11 SOCIAL SECURITY FIC
999999999 510855 P 03/18/2005 254.89 MED 4.00 MEDICARE FIC

TOTAL: 1 SUM 254.89

GROUP: NY STATE [\u]

888888888 510821 P 01/14/2005 101.25 FEDM 0.00 FED MARRIED F
888888888 510821 P 01/14/2005 101.25 FICA 7.23 SOCIAL SECURITY FIC
888888888 510821 P 01/14/2005 101.25 NYC 1.17 NY CITY C
888888888 510821 P 01/14/2005 101.25 NYS 7.00 NY STATE S
888888888 510821 P 01/14/2005 101.25 NYS 7.00 NYS S

555555555 510824 P 01/14/2005 101.25 FED 0.00 FED MARRIED F
555555555 510824 P 01/14/2005 101.25 FICA 7.23 SOCIAL SECURITY FIC
555555555 510824 P 01/14/2005 101.25 NYS 7.00 NY STATE S

TOTAL: 2 SUM 202.50

GROUP:MASS STATE [\u]

111111111 510823 P 01/14/2005 101.25 FED 0.00 FED MARRIED F
111111111 510823 P 01/14/2005 101.25 FICA 7.23 SOCIAL SECURITY FIC
111111111 510823 P 01/14/2005 101.25 MAS 7.00 MASS STATE S

TOTAL:1 SUM 101.25

I tried different variations with grouping but it either groups on WH_TYPE or check#

if {ILGW_WHT_DESC.WH_TAX_CODE} = "S" then
{ILGW_WHT_DESC.WH_DESCRIPTION}


THANKS A LOT[3eyes]
 
Sort by date Sort by votes
Field to sum: Net

Evaluate: On change of group: Group2: check

reset On change of group Group1 (state formula)

The run_tot is on the GRP_FOOTER1 (STATE)

thanks
 
Upvote 0 Downvote
I apologize, my mistake I was using different field to SUM
[sad]
 
Upvote 0 Downvote
LB –
Thanks for you help

One more thing for the same report.

Here is the data (I’ve used the running total with evaluate & reset option)
CHECK AMT AMT_NET WHT_AMT ISSUED_DATE

--------- ------------ ------------ ----------- -----------

510835 -110.44 -125.00 -7.75 03/09/2005

510835 -110.44 -125.00 -5.00 03/09/2005

510835 -110.44 -125.00 -1.81 03/09/2005

510835 110.44 125.00 1.81 03/09/2005

510835 110.44 125.00 5.00 03/09/2005

510835 110.44 125.00 7.75 03/09/2005
The running total does not like the minus sign amounts
It ended up giving me only the positive numbers:

The results:
Amt: = 110.44, Amt_Net: 125.00
Should have been 0

THANK YOU,
Ann.
 
Upvote 0 Downvote
If there can be more then two amounts per check (both a negative value and a positive), then the evaluation formula should be set on a different field--whichever field is unique to the negative vs. positive value. It looks like each value is either a debit or credit--what field tells you that? You would use that as your evaluation "on change of" field.

-LB
 
Upvote 0 Downvote
there is "V" indicator for negative amounts
on the evaluation criteria choose the formula
if check_type = V then {record} else {group} ?


THANK YOU VERY MUCH FOR YOUR HELP
 
Upvote 0 Downvote
You should either group on {table.V} and use "on change of group" (V), or sort the report by the V field, and then use on change of field {table.V} in the evaluation area.

-LB
 
Upvote 0 Downvote
I am sorry I am confused.

What do you mean by on change of group" (V), on change of group" (V),??

Can I add something like this to the evaluation condition?

if {ILGW_VACATION.CHECK_STATUS} = "V" then
{ILGW_VACATION.CHECK} else
GroupName ({@Groupping})

Thanks,
Ann.
 
Upvote 0 Downvote
i should use the new formula field if v then display rtotal1 (where evaluation is by group) esle
display rtotal2 (where evaluation is by field)!
 
Upvote 0 Downvote
In the running total, in the evaluation section, check "on change of field" and then choose {table.V} OR
add a group to your report on {table.v} and then in the running total, choose "on change of group" and select {table.v}. You don't need a formula.

-LB
 
Upvote 0 Downvote
LB –

Please bare with me here!

Here is what I did (still not accumulating negative amounts)
data:
CHECK CHECK_STATUS WHT_AMT WHT_TYPE AMT AMT_NET WHT_AMT ISSUED_DATE

--------- ------------ ------------------------------ -------- ------------ ------------ ----------- -----------

510835 V YIM LAN WONG FICA -110.44 -125.00 -7.75 03/09/2005

510835 V YIM LAN WONG PAC5 -110.44 -125.00 -5.00 03/09/2005

510835 V YIM LAN WONG MED -110.44 -125.00 -1.81 03/09/2005

510835 V YIM LAN WONG MED 110.44 125.00 1.81 03/09/2005

510835 V YIM LAN WONG PAC5 110.44 125.00 5.00 03/09/2005

510835 V YIM LAN WONG FICA 110.44 125.00 7.75 03/09/2005

Running tot: ILGW_VACATION.AMT

Added the GROUP BY CHECK_STATUS (P OR V)
Evaluate on change of group: Group3_CHECK_STATUS
Reset on change of group: GroupA (state)

REALLY APPRECIATE YOUR HELP
Ann
 
Upvote 0 Downvote
You have identified the wrong field. You need to find the field that distinguishes the negative from the positive numbers and use that as the on change of field. Can you explain what is making those numbers negative?

-LB
 
Upvote 0 Downvote
Its the data both negative & positive checks are identified with "V" status.

I'll change the V with positives for P

THANKS FOR YOUR HELP!!!

 
Upvote 0 Downvote
I changed V to P if the amt is positive
it does not work
 
Upvote 0 Downvote
I don't have a clue what V or P refer to, so don't know whether what you tried makes sense. Can you verbally explain why the same values appear both as negatives and positives? There has to be some field that distinguishes the positive from the negative records, as I said earlier, maybe a field that identifies the entry as a credit versus a debit.

-LB
 
Upvote 0 Downvote
LB -
thank you very much for you help!

if CHECK_STATUS = V the amount is negative
if CHECK_STATUS = p the amount is positive

data:
CHECK CHECK_STATUS WHT_AMT WHT_TYPE AMT AMT_NET WHT_AMT ISSUED_DATE FUND

--------- ------------ ------------------------------ -------- ------------ ------------ ----------- ----------- ------

510835 V YIM LAN WONG FICA -110.44 -125.00 -7.75 03/09/2005 ILV

510835 V YIM LAN WONG PAC5 -110.44 -125.00 -5.00 03/09/2005 ILV

510835 V YIM LAN WONG MED -110.44 -125.00 -1.81 03/09/2005 ILV

510835 P YIM LAN WONG MED 110.44 125.00 1.81 03/09/2005 ILV

510835 P YIM LAN WONG PAC5 110.44 125.00 5.00 03/09/2005 ILV

510835 P YIM LAN WONG FICA 110.44 125.00 7.75 03/09/2005 ILV
 
Upvote 0 Downvote
So your group structure should now look like:

State - grp#1
Check - grp#2
Check status - grp#3

To reiterate, in the running total expert, you are choosing sum of ILGW_VACATION.AMT, evaluate on change of group#3 (check status), reset on change of group#1 (state), and you are placing this running total in the state group footer.

This should work fine.

-LB
 
Upvote 0 Downvote
I have 3 groups

1. State (if {ILGW_VACATION.STATE} = " " then "Z_Other" else
{ILGW_VACATION.STATE}

2. CHECK_NO

3. CHECK_STATUS

On my running total
sum: amt
evaluate on change of group: Group3 (CHECK_STATUS)
Reset on change of group; Group1 (@state)
The total is under the G1
It shows duplicated amounts whenever there is a negative amt in the group

Thanks a lot for your help,
Ann
 
Upvote 0 Downvote
Is "amt" negative in your database or are you using some formula to make it negative? If so, then you need to use that formula instead of amt to sum in the running total.

-LB
 
Upvote 0 Downvote
amt is negative in the DB

THANKS FOR YOUR HELP
 
Upvote 0 Downvote
I see no reason for the running total not to work.
In your last sample, the rt should pick up -125 when check status is V and 125 when it is P, to zero out.

-LB
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ryanparks
  • Locked
  • Question
Grouping On Count of Records
Replies
10
Views
677
lbass
lbass
tsh1586
  • Locked
  • Question
grouping items that are within a certain amount
Replies
13
Views
280
tsh1586
tsh1586
Cores
  • Locked
  • Question
Make grouping depending on selected parameter
Replies
6
Views
263
lbass
lbass
smt2004
  • Locked
  • Question
HELP SORTING OR GROUPING
Replies
3
Views
176
lbass
lbass
meagain
  • Locked
  • Question
Conditional and Right Trim formula
Replies
4
Views
232
meagain
meagain

Part and Inventory Search

Sponsor

Back
Top