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!

  • 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

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]
 
It looks to me like you want to group on a field that appears in only one record within a check group. I'm not sure whether SQL expressions work the same way in 9.0 as they do in 8.0. Please try the following and let me know whether it works:

(select max(AKA.`taxcode`) from Table AKA where
AKA.`check` = Table.`check` and
AKA.`taxcode` = 'S')

Copy this into your SQL expression editor and then substitute the exact field names for "taxcode" and "check" and substitute your table name for "Table". Leave "AKA" as is, since it represents an alias table name.

Let me know if your SQL expression editor accepts this, and then we can move forward.

-LB
 
Did not work
may be I can derive the table with 2 an additional 2 columns (state desc if state code = S or blank) and then do the groping in Crystal?

Code:
SELECT  DISTINCT
ILGW_VACATION.MEMBER_SSN, 
ILGW_VACATION.CHECK,
ILGW_VACATION.CHECK_STATUS, ILGW_VACATION.ISSUED_DATE, ILGW_VACATION.AMT, 
ILGW_VACATION.WHT_TYPE, ILGW_VACATION.WHT_AMT, ILGW_WHT_DESC.WH_DESCRIPTION, WH_TAX_CODE

 FROM BASYS.ILGW_VACATION LEFT OUTER JOIN BASYS.ILGW_WHT_DESC ON
 ILGW_VACATION.WHT_TYPE = ILGW_WHT_DESC.WH_CODE     

WHERE YEAR(ILGW_VACATION.ISSUED_DATE) = 2005 
ORDER BY  ILGW_VACATION.MEMBER_SSN

THANKS FOR YOUR HELP!
Ann.
 
What message did you get when you tried the SQL expression? What is the table that the tax code field is taken from? It looks like it's from the ILGW_WHT_DESC table, but your table name is missing. If it's in the wht-desc table, then try to create {%state}:

(select max(A.`WH_DESCRIPTION`) from BASYS.ILGW_WHT_DESC A, BASYS.ILGW_VACATION B where
B.`CHECK` = ILGW_VACATION.`check` and
A.`WH_TAX_CODE` = 'S')

If this doesn't work, you can use "Add Command" as your datasource, and add the above expression into your select statement as in:

SELECT DISTINCT
ILGW_VACATION.MEMBER_SSN,
(select max(A.`WH_DESCRIPTION`) from BASYS.ILGW_WHT_DESC A, BASYS.ILGW_VACATION B where
B.`CHECK` = ILGW_VACATION.`CHECK` and
A.`WH_TAX_CODE` = 'S'),
ILGW_VACATION.CHECK,
ILGW_VACATION.CHECK_STATUS, ILGW_VACATION.ISSUED_DATE, ILGW_VACATION.AMT,
ILGW_VACATION.WHT_TYPE, ILGW_VACATION.WHT_AMT, ILGW_WHT_DESC.WH_DESCRIPTION, ILGW_WHT_DESC.WH_TAX_CODE
FROM //etc.

The reason I am suggesting this, is that you can place the expression in the detail section and it would appear for every record--in other words, you can group on it in a formula like:

if isnull({%state}) then "Other" else {%state}

You would insert a group on this formula and then insert a group on {ILGW_VACATION.CHECK}.

-LB
 
I was not able to put the SELECT or MAX statements on CR
I can add the criteria:

example:
{ILGW_VACATION.ISSUED_DATE} in Calendar1stQtr and
Year({ILGW_VACATION.ISSUED_DATE})=2005

& than CR generates SQL: (addded "DISTINCT" option on the menu)
Code:
SELECT DISTINCT
    "ILGW_VACATION"."CHECK", "ILGW_VACATION"."MEMBER_SSN", "ILGW_VACATION"."MEMBER_NAME", "ILGW_VACATION"."CHECK_STATUS", "ILGW_VACATION"."ISSUED_DATE", "ILGW_VACATION"."AMT", "ILGW_VACATION"."WHT_TYPE", "ILGW_VACATION"."WHT_AMT", "ILGW_VACATION"."AMT_NET"
FROM
    "BASYS"."ILGW_VACATION" "ILGW_VACATION"
WHERE
    DATE("ILGW_VACATION"."ISSUED_DATE") >= '2005-01-01' AND
    DATE("ILGW_VACATION"."ISSUED_DATE") <= '2005-03-31'
ORDER BY
    "ILGW_VACATION"."MEMBER_SSN" ASC

I think I'd have to add the state field to each record & than use this fomula.

BUT, even before I do it

I selected 2 records to create a simple report:
DATA:

CHECK CHECK_STATUS ISSUED_DATE AMT WHT_TYPE WHT_AMT

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

510821 P 01/14/2005 116.65 FEDM 0.00

510821 P 01/14/2005 116.65 FICA 7.23

510821 P 01/14/2005 116.65 NYC 1.17

510821 P 01/14/2005 116.65 NYS 7.00


510828 P 03/09/2005 182.83 FEDM 2.88

510828 P 03/09/2005 182.83 FICA 13.99

510828 P 03/09/2005 182.83 MASS 10.05

In the report I group by
1.CHECK, WITHIN THE CHECK GRP GROUP BY FORMULA:

FORMULA:
if {ILGW_VACATION.WHT_TYPE} = "MASS" OR {ILGW_VACATION.WHT_TYPE} = "NYS" then {ILGW_VACATION.WHT_TYPE}
else "Other"

REPORT RESULTS:

CHECK CHECK_STATISSUED_ AMTWHT_TYP _AMT _NET

NYS
510821 ROMA DEVI GOPIEP 1/14/20 6.65 NYS 7.00


Other
510821 ROMA DEVI GOPIEP 1/14/20 6.65 FEDM 0.00

NYC 1.17

FICA 7.23




MASS
510828 DAVID SARDINHA P 3/9/200 2.83 MASS 0.05


Other
510828 027428394DAVID SARDINHA P 3/9/200 2.83 FEDM 2.88

FICA 3.99



Grand Tot 2 7 0.00 2.83 2.32

==========================================================================

SHOULD BE:

CHECK CHECK_STATISSUED_ AMTWHT_TYP _AMT _NET

NYS
510821 ROMA DEVI GOPIEP 1/14/20 6.65 NYS 7.00

FEDM 0.00

NYC 1.17

FICA 7.23




MASS:

510828 DAVID SARDINHA P 3/9/200 2.83 MASS 0.05
FEDM 2.88
FICA 3.99

OTHER:
ANY OTHER RECORDS W/O STATE CODE WTH

Grand Tot 2 7 0.00 2.83 2.32

It should be something like this:
put the "STATE" only if there is a state code (in this case NYS of MASS) within a group else
put "OTHER" in the group label.

Can I put some code into the formula?

THANKS A LOT FOR YOUR HELP!





 
Using the SQL expression or Add Command method is, I think, the best solution, since as I understand it you do NOT have the state field in each record. You cannot group by a non-recurring record.

You could, however, create faux groups, by using a formula like the following {@state}:

if isnull({table.code}) or
trim({table.code}) = "" then "Other" else
if {table.code} = "S" then {ILGW_VACATION.WHT_TYPE} else "Other"

(I'm not sure anymore which code results in "S".)

Then insert a maximum on {@state} at the check group level. Then go to report->topN/group sort and select "Maximum of {@types}". This will order the groups by type.

To create a group header for the faux "state group" and to create a place to summarize your faux group, insert two additional group header sections for the check group and drag them so they are in the topmost position. Then to get your summary for the faux group and to create a variable to use for suppressing repeated group headers, create the following formula, which assumes you are using topN in ascending order:

whileprintingrecords;
numbervar amt;
stringvar state;

if onfirstrecord or
state < maximum({@state},{ILGW_VACATION.CHECK}) then
(state := maximum({@state},{ILGW_VACATION.CHECK});
amt := {ILGW_VACATION.AMT}) else
if state = maximum({@state},{ILGW_VACATION.CHECK}) then
amt := amt + {ILGW_VACATION.AMT}; //not sure if this is the right field
amt;

Place this formula in the detail section and suppress it. Then create a second formula and place it in GH#1a:

whileprintingrecords;
numbervar amt;

This will display the summary for the faux group. Drag the maximum of {@state} from the group footer and place it in Group header_b. Then go to the section expert->group header_a and enter:

whileprintingrecords;
stringvar state;
groupnumber = 1 or
state = maximum({@state},{ILGW_VACATION.CHECK})

Then highlight groupheader_b ->suppress->x+2 and enter:
whileprintingrecords;
stringvar state;
state = maximum({@state},{ILGW_VACATION.CHECK})

This will suppress the repeated faux group headers.

-LB
 
I added the state desc to every record in the progam that creates the file
THANKS A LOT
 
Hello LB -

THANKS FOR YOU HELP AGAIN!

I have one more problem on the report:

My data has duplicated rows.

I did the SUM distinct option on count (worked),
but for the amounts it does not work

I’ve used this formula & it generated ZEROS

Code:
WhilePrintingRecords;
   NumberVar Prior;
   NumberVar LYS;
   if Count ({ILGW_VACATION.AMT}) = Prior + 1 //which indicates a first record for this customer
   then LYS := LYS + {ILGW_VACATION.AMT};
   Prior := Count ({ILGW_VACATION.AMT});  //store the current count to compare with next  record
   LYS

thanks,
Ann.
 
I'm not sure what you are doing, but when you use the count function, you need to add a group condition--otherwise you are getting the count for the entire report.

if Count({ILGW_VACATION.AMT},{ILGW_VACATION.CHECK})//etc.

-LB
 
data:
-------

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

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

=====================================================================================================
count: 2 sum 356.14

tring to create a formula for the SUM to be 356.14
THANKS
 
Ann, did you use my faux group solution or were you able to insert an actual group on state because you were able to enter it on every record? If you added a group on state, then just use the running total expert where you select the amount field, sum, evaluate on change of group (check), reset on change of group (state).

-LB
 
I added the STATE on every record & used grouping on the formula:

1. if {ILGW_VACATION.STATE} = " " then "Z_Other" else
{ILGW_VACATION.STATE}
2. by check#
After I inserted the sum on the group it gave me the sum of all including dups.
How do I evaluate on change?

THANKS A LOT FOR YOUR HELP!
Ann
 
I already pretty much outlined that. Use the running total expert (field explorer->running totals), and the follow the steps in my last post. You would then place the running total in the group footer for state.

-LB
 
I added the running total on the group, but
I don't see where I can add the "evaluate on change of group (check) OR reset on change of group (state)"
conditions.
thanks a lot
ann

 
If you are in the running total expert, the first selection is a field and a summary of that field. The second section is called the evaluation section. You would select "evaluate on change of group" and choose the check group and then in the reset area you would choose "on change of group" and then select the state group. Are you sure you are IN the running total expert?

-LB
 
I've used the running totals
with the evaluate cond. - did not work
This is the last thing I have to do to complete this report

THANKS:)
 
There is no reason I can see for this not to work. Please explain exactly what you did and where you placed the running total (it needs to be in the state group footer).

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top