Sub totals based on group selection

[LFB]

Hi all,
I'm using crw 8.5, Oracle (not sure which version) and Win2k sp4 .

I want to extract some sub totals sorted by my second grouping instead of the first grouping. The complicating factor is the group selection formula.

I have two groups
Group 1: {ITEM.TITLE_NO}
Group 2: {ITEM.TECH_CODE}

I also have a group selection formula:

Sum ({@active pres}, {@Group1}) > 0.00 and
Sum ({@acquired video}, {@Group1}) > 0.00

The {@active pres} and {@acquired video} return a value of 0 or 1.

If the group selection formula returns a value of 1 for any of the items under the TITLE_NO then sum all the TECH_CODE's for that group (there's a field called NO_CARRIER that gets summed). This part of it works fine. The problem I'm having is that I get a list of the TECH_CODE's grouped by TITLE_NO. I need a list of the TECH_CODE's grouped by TECH_CODE, ie a total for each TECH_CODE.

For example I get the following:
Title A
Tech Code AA
Tech Code BB
Title B
Tech Code AA
Tech Code CC

What I need to see is:
Tech Code AA = 2
Tech Code BB = 1
Tech Code CC = 1

I then need to pass these values from a sub report to a main report. All I need in the main report is to know the TECH_CODE and the total from the sub report. In the main report I have a grand total of all TECH_CODES and I just want to deduct the values from the sub report.

The only way I've gotten this to work is to use a shared variable in the sub report that adds up all the totals for all the TECH_CODE's and returns it to the main report. Problem is I hardcoded the TECH_CODE's when in reality they can change.

This was the code that I did, with one variable for each TECH_CODE.

whileprintingrecords;
Shared NumberVar tc1B;
Shared NumberVar tc1C;
Shared NumberVar tc2H;
Shared NumberVar tcBA;
Shared NumberVar tcBC;
Shared NumberVar tcBCS;
Shared NumberVar tcDBC;
Shared NumberVar tcUH;
Shared NumberVar tcUS;
Shared NumberVar tcVA;
Shared NumberVar tcVS;
Shared NumberVar tcOther;


select {ITEM.TECH_CODE} 
case "1B" :  tc1B := tc1B+{ITEM.NO_CARRIERS}
case "1C" :  tc1C := tc1C+{ITEM.NO_CARRIERS}
case "2H" :  tc2H := tc2H+{ITEM.NO_CARRIERS}
case "BA" :  tcBA := tcBA+{ITEM.NO_CARRIERS}
case "BC" :  tcBC := tcBC+{ITEM.NO_CARRIERS}
case "BCS":  tcBCS := tcBCS+{ITEM.NO_CARRIERS}
case "DBC":  tcDBC := tcDBC+{ITEM.NO_CARRIERS}
case "UH" :  tcUH := tcUH+{ITEM.NO_CARRIERS}
case "US" :  tcUS := tcUS+{ITEM.NO_CARRIERS}
case "VA" :  tcVA := tcVA+{ITEM.NO_CARRIERS}
case "VS" :  tcVS := tcVS+{ITEM.NO_CARRIERS}
default   :  tcOther := tcOther+{ITEM.NO_CARRIERS}

I thought a possible approach could be to create a two dimensional array, populate the first dimension with the TECH_CODE, the second dimension with the NO_CARRIER (this is the field I sum) loop through it and pass the value back to the main report. This is probably getting a bit too complicated though.

Anyway, I'm open to any suggestions and would appreciate any ideas.

Thanks

 

[satinsilhouette]

Crystal does not support multi-dimmensional arrays at this time.

 

[LFB]

Just in case anyone is wondering. My solution to this was to build the main report using just the group heading TITLE_NO. Next I created sub-report with the grouping that I wanted, independent of the TITLE_NO. I linked by TITLE_NO and dropped this into the main report footer hoping that for each valid title it would return the correct recordset - it didn't. I created a variable that stored each title and I passed the variable to the sub report and that worked.

Luckily only a few titles fell into this variable otherwise crystal would max out on the 254 character string limit.

 

[dgillz]

Actually if you upgrade to crystal XI (crystal 8.5 is 6 years old) the limit is 64,000 instead of 264.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[synapsevampire]

Your solution is OK for now, but if things change you may max it out.

I'd suggest that you write it out in SQL instead of trying to use your presentation layer (Crystal) to manipulate data.

Either a series of Views or a Stored Procedure will make much more sense here. Remember, Crystal sucks as a database engine, but it's the best at presenting data.

-k