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
I had to fix the data
WORKS NOW!!!
THANK YOU!
 
Upvote 0 Downvote
LB -
Another problem on the report:

I need to calculate the withholdings by state (etc NYS, MASS)

The formula does not work on a group where withholding codes are coded differently for the same state
For example, Georgia State withholding types in the DB could be "GASM" OR "GASS"

Here is the formula I’ve used to calculate the WH

Code: 
IF {ILGW_VACATION.WHT_TYPE} = "CNS" OR {ILGW_VACATION.WHT_TYPE} = "GA"

OR {ILGW_VACATION.WHT_TYPE} = "GAM" OR {ILGW_VACATION.WHT_TYPE} = "GAS"

OR {ILGW_VACATION.WHT_TYPE} = "GASM" OR {ILGW_VACATION.WHT_TYPE} = "GASS"   

OR {ILGW_VACATION.WHT_TYPE} = "MASM" 
OR {ILGW_VACATION.WHT_TYPE} = "MASS" 

OR {ILGW_VACATION.WHT_TYPE} = "NC" OR {ILGW_VACATION.WHT_TYPE} = "NCS"   

OR {ILGW_VACATION.WHT_TYPE} = "NCSS" OR {ILGW_VACATION.WHT_TYPE} = "NJ" 

OR {ILGW_VACATION.WHT_TYPE} = "NJS" OR {ILGW_VACATION.WHT_TYPE} = "NJSM"

OR {ILGW_VACATION.WHT_TYPE} = "NJSS" OR {ILGW_VACATION.WHT_TYPE} = "NY"

OR {ILGW_VACATION.WHT_TYPE} = "NYS" OR {ILGW_VACATION.WHT_TYPE} = "NYSM"  

OR {ILGW_VACATION.WHT_TYPE} = "NYSS" OR {ILGW_VACATION.WHT_TYPE} = "PA" 

OR {ILGW_VACATION.WHT_TYPE} = "PAS" OR {ILGW_VACATION.WHT_TYPE} = "PASM"  

OR {ILGW_VACATION.WHT_TYPE} = "PASS" OR {ILGW_VACATION.WHT_TYPE} = "SCSM" 

OR {ILGW_VACATION.WHT_TYPE} = "SCSS" OR {ILGW_VACATION.WHT_TYPE} = "VA" 

OR {ILGW_VACATION.WHT_TYPE} = "VAS"
THEN {ILGW_VACATION.WHT_AMT} ELSE
0

Is there a way to fix it? Or I have to have one WH_TYPE for STATE?

REALLY APPRECIATE YOUR HELP!
Ann.
 
Upvote 0 Downvote
Simplify the formula in this way:

IF {ILGW_VACATION.WHT_TYPE} in ["CNS","GA","GAM","GAS","GASM","GASS","MASM","MASS","NC",
"NCS","NCSS","NJ","NJS","NJSM","NJSS","NY","NYS","NYSM",
"NYSS","PA","PAS","PASM","PASS","SCSM","SCSS","VA","VAS"]
THEN {ILGW_VACATION.WHT_AMT} ELSE
0

This is a detail level formula, so that the fact that there are two different codes within a state is irrelevant. You would have to insert a summary on it to get a total for a state if there is more than one record per state.

-LB
 
Upvote 0 Downvote
did not work

It does not calc the totals if the group has MASS & MASM
I think i need to change the data
for all MASS MASM to be MASS?

THANK YOU!
 
Upvote 0 Downvote
What you are saying makes no sense. If the field results are not entered in a standard way into the database, you might have to use the trim() function to allow for extra spaces:

IF trim({ILGW_VACATION.WHT_TYPE}) in ["CNS","GA","GAM","GAS","GASM","GASS","MASM","MASS","NC",
"NCS","NCSS","NJ","NJS","NJSM","NJSS","NY","NYS","NYSM",
"NYSS","PA","PAS","PASM","PASS","SCSM","SCSS","VA","VAS"]
THEN {ILGW_VACATION.WHT_AMT} ELSE
0

This formula DOES NOT calculate totals, so to say it doesn't total is not helpful. How are you totalling this field?

-LB
 
Upvote 0 Downvote
I am doing the sum on the formula @MASS formula
THANKS A LOT FOR YOUR HELP
 
Upvote 0 Downvote
You might simplify by using the LEFT function, as in:

if left(trim({table.field}),2) in
["GA","NC","NJ","NY","PA","VA"]
or
if left(trim({table.field}),3) in
["CNS","MAS","MAS","SCS"]
THEN
{ILGW_VACATION.WHT_AMT}
ELSE
0

Adjust as required.

-k
 
Upvote 0 Downvote
Am I supposed to know the contents of {@Mass}? Please share.

-LB
 
Upvote 0 Downvote
IF {ILGW_VACATION.WHT_TYPE} in ["CNS","GA","GAM","GAS","GASM","GASS","MASM","MASS","NC",
"NCS","NCSS","NJ","NJS","NJSM","NJSS","NY","NYS","NYSM",
"NYSS","PA","PAS","PASM","PASS","SCSM","SCSS","VA","VAS"]
THEN {ILGW_VACATION.WHT_AMT} ELSE
0
THANKS FOR YOUR HELP,
ANN
 
Upvote 0 Downvote
Sorry, no reason I can see for this not to work. I think you must be leaving out some information.

This thread has gone on way too long for me, so maybe you should start a new thread and get some fresh perspective from someone else.

-LB
 
Upvote 0 Downvote
I had to clean up the report, everything look good now
btw, how did you learn CR?

THANK YOU VERY VERY MUCH FOR YOUR HELP!!!!!!
 
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