How do I group by one field and sort by another?

[SteveBell]

I have an inventory report that looks at every inventory transaction and sums the transaction quantities in a complex formula. The details section shows every inventory transaction, and it's suppressed. Group 3 is the item number, and the sum of the transaction quantities is displayed in that footer. Group 2 is by Item Type (e.g. 132), and Group 1 is by Item Type Family (e.g. 1xx).

My customer now wants me to sort the data by the formula descending -- Group by the item field, but sort by the quantity formula.

I can't figure out how to do this. Any suggestions?

 

[kenhamady]

You can't sort on every formula. Post the complex formula and we can tell you if it is possible, or if you can approximate the same result. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[SteveBell]

Here's the formula (Excess Value) that I want to sort descending:

Local NumberVar     ExcessValue;
Shared NumberVar    ExcessValue_G1;
Shared NumberVar    ExcessValue_G2;
Shared NumberVar    ExcessValue_T;

ExcessValue := {@W53-104- Group 3} + {@W>104- Group 3} + {@No Use- Group 3} - 
               {#S.Stock Value- Group 3} - {#Allocated Value- Group 3};

// negative excess values aren't allowed
if (ExcessValue < 0.0) then
    ExcessValue := 0;

// increment the running totals
ExcessValue_G1  := ExcessValue_G1 + ExcessValue;
ExcessValue_G2  := ExcessValue_G2 + ExcessValue;
ExcessValue_T   := ExcessValue_T  + ExcessValue;

// display the current excess value
ExcessValue;

Here's formula {@W53-104- Group 3}:

Local NumberVar Start_Week;
Local NumberVar End_Week;
Local NumberVar Previously_Used_Value;
Local NumberVar Remaining_To_Use;
Local NumberVar This_Interval_Value;
Local NumberVar Twelve_Month_Value;
Local NumberVar One_Week_Value;

Shared NumberVar    Remaining_To_Use_53_104;
Shared NumberVar    Remaining_To_Use_53_104_G1;
Shared NumberVar    Remaining_To_Use_53_104_G2;
Shared NumberVar    Remaining_To_Use_53_104_T;

Start_Week              := 53;
End_Week                := 104;

Twelve_Month_Value      := {#12 Month Use- Group 3} * {tiitm001.copr};
One_Week_Value          := Twelve_Month_Value / 52;
Previously_Used_Value   := (Start_Week - 1) * One_Week_Value;

if (One_Week_Value < 0) then
    One_Week_Value      := 0;

if (Previously_Used_Value > {#On Hand Value- Group 3}) then
    Previously_Used_Value   := {#On Hand Value- Group 3};

Remaining_To_Use        := {#On Hand Value- Group 3} - Previously_Used_Value;
This_Interval_Value     := (End_Week - Start_Week + 1) * One_Week_Value;

if (This_Interval_Value <= Remaining_To_Use) then
    Remaining_To_Use    := This_Interval_Value;

// increment the running totals
Remaining_To_Use_53_104     := Remaining_To_Use_53_104    + Remaining_To_Use;
Remaining_To_Use_53_104_G1  := Remaining_To_Use_53_104_G1 + Remaining_To_Use;
Remaining_To_Use_53_104_G2  := Remaining_To_Use_53_104_G2 + Remaining_To_Use;
Remaining_To_Use_53_104_T   := Remaining_To_Use_53_104_T  + Remaining_To_Use;

// display the result
Remaining_To_Use;

The rest of the formulas are similar. I use a combination of manual running totals and CR running totals.

 

[dgillz]

So it is grouped by item, but htey want it sorted by one of your subtotaled fields? If that is the case use the Top-N expert. Report, TopN/Sort Group expoert, and sort by the Grouped totals (not the group-by field).

You can include the Top N and exclude all others, include all others on a line called "others" or include all groups on your report.

Let me know if this is what you are looking for. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[kenhamady]

You can't TopN on a running total, but at first glancen I don't see anything in these formulas that requires a running total as opposed to several simple summary fields. Do you know why they decided to use running totals. Usually that is only done if you need to summarize formulas that use summary operations, or if you are summing from subreports. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[SteveBell]

The report calculates the number of dollars of inventory on hand spread over the number of weeks to consume it based on the last twelve months history. It looks like this:

Item # Value Use # On Hand $ On Hand W1 W2 ... W52  W>52 Excess
------ ----- --- --------- --------- -- -- ... --- ----- ------
123456    $1 100      5000     $5000 $2 $2      $2 $4896  $4896
654321    $1 100         2        $2 $2 $0      $0    $0     $0

The Excess column is the value of all the inventory that won't be used for more than a year. Suppressing zero values makes a nice horizontal bar chart for each part.

I couldn't use summaries for each column because they're all formulas.

 

[kenhamady]

You CAN summarize formulas as well as field, as long as the formulas don't themselves use summary functions, Next, Previous or a few other odd items. If you can't use a summary, then you can't order the groups by a subtotal. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[SteveBell]

I tried using summaries at first, but the totals grew too fast. The detail section contains every inventory transaction for a part (it's suppressed). The next group level represents a part number. I only want to evaluate my formulas in the group footers. When I used summaries, the formula was evaluated for every inventory transaction instead of just for the part number in the group footer.

 

[kenhamady]

You might be stuck. The only way to summarize once per group is to use a running total, and you can't organize the report based on a running total. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[SteveBell]

Well, it was worth a shot. Thanks for trying.