Finding the max of a group summary 1

[aaronburro]

I've got a report that I'm writing using CR8.5 on a MAS200 database. The report it as Item Usage report and it shows how much of each Item various customers use.

It has several groups and they are put into Crystal as follows:
1) ItemNumber
2) CustomerNumber
3) WarehouseCode (where the customer purchased the item)

The user has the option to show the detail section as well, which complicates things a bit later.

I'd like to put in the CustomerNumber Group Header the warehouse location that the customer goes to the most. IE, what location the customer has purchased the most units from.
The obvious method is to do a summation of units purchased per warehoused and then find the max of that, but this doesn't give me the the warehouse location; just the most number of units purchased from a particular warehouse.

Ideally, if I could find the max of the group summary and then iterate through the groups (and not the details) again between the warehouse group and the Customer group, I could just compare the max I have to the warehouse sum and adjust values accordingly. Unfortunately, I don't get to iterate through groups again...

I have solved the problem via a subreport but that is just too slow, especially since I have to use another subreport to always show the warehouse summaries contiguously(if the user shows the details, then the obvious choice for warehouse summaries, the WH footers, gets broken up).

The way I did the subreport was to initialize two variables in the report header: MostUnitsPurchased (MUP for short) and LocationMostUnitsPurchased (LMUP for short). The former is a number, the latter is a string. I then grouped the subreport by WarehouseCode. Then I created an "updater" function, updateWH, which was placed in the WH footer, which checks the sum of units purchased for the current WH and if it is greater than MUP, then I set MUP to the current sum, in addition to setting LMUP accordingly. Finally, I place another formula, with the whileprintingrecords flag, in the report footer which shows LMUP. Then, if I just set the subreport links accordingly to select data containing only the CustomerNumber and ItemNumber and I am good to go. Subreport works.

NOW, knowing what I know about Subreports, I am fairly confident that I should be able to eliminate it completely.

So, I tried, of course.

I took my three functions and placed them in the main report. The initializer and show-er both went into the customer group header, the updater went into the warehouse group footer. Didn't work... what am I doing wrong, here?

 

[aaronburro]

By "didn't work", I mean "works for only customers who purchase from only WH."

BTW, I set the show-er function to run after the updater via "EvaluateAfter"...

 

[lbass]

Do a topN sort of your warehouse group using "sum of {table.noofunits}". Then drag the warehouse group fields into the customer number header.

-LB

 

[aaronburro]

Welp, that might work, but lemme throw another wrench into the works, if I may...

I have two of these "max" functions that I need to show in the customer header. The second "max" is MostPurchases, as in most individual orders. Unfortunately, the result of these two are not always the same...

The subreport I mentioned earlier finds and displays both maximums.

 

[lbass]

If you want the maximums in the group header, then I think you need to use the subreports. Otherwise, you could use your formulas to show the maximums in the group footer for customer number.

-LB

 

[aaronburro]

thx for the advice, lbass. Not surprisingly, the formulas do work in the customer group footer... Guess I'll have to decide which one is more important...