Group on Dollar Totals

[knappm]

primary item up with secondary items of close to equal value. So, for example, if you had primary and secondary items as follows:

Type Amount
P $1,000,000
P $750,000
S $50,000
S $465,000
P $20,000
S $650,000
S $400,000
S $300,000

Then, ideally, it may group them as follows:
P $1,000,000
S $650,000
S $400,000

P $750,000
S $465,000
S $300,000

P $20,000
S $50,000

Of course, it may not always group perfectly. In which case the extra primaries or secondaries would just be listed at the bottom. I thought I had a good start. It does a rough grouping of the items, but whenever I refresh the report, it groups it incorrectly. When I modify the formula (like add and delete a space -- just so I can save the formula again), then it groups correctly again until I refresh. Does that make sense? This is the formula I group on now:

Global NumberVar Array nPrimaryAmount;
Global NumberVar Array nSecondaryAmount;
Global NumberVar Array nClosedGroups;
Local NumberVar i := 1;
Local NumberVar j := 1;
Local NumberVar k := 1;

If {Logbook.Forecast Status} = "P" Then (
    While nPrimaryAmount[i] <> 0 Do (
        i := i + 1;
    );
    nPrimaryAmount[i] := {Logbook.Amount};
    i;        
) Else If {Logbook.Forecast Status} = &quot;S&quot; Then (
    While nClosedGroups[k] <> 0 Do (
        k := k + 1;
    );
    nSecondaryAmount[k] := nSecondaryAmount[k] + {Logbook.Amount};
    If nSecondaryAmount[k] > nPrimaryAmount[k] And nPrimaryAmount[k] <> 0 Then
        nClosedGroups[k] := 1;
    k;
);

The arrays are initialized in the report header. It adds the secondary amount to the total secondary amount until it's greater than the primary. So, if the total secondary amount is only $20,000 under the primary amount and the next secondary amount is $800,000, then it just adds it in. It would be nice to be able to keep the primary amount and the total secondary amount as close to equal as possible.

I'm not sure what else to explain at this point. I'll answer question when/if they come. I could REALLY use some help on this. I appreciate it.

Thanks,
Mark Knapp

 

[knappm]

Let me modify my statement a little. When I refresh the report, it doesn't group as desired. I puts each primary in its own group, and then puts all the secondaries into a single group. If I add a line such as "WhileReadingRecords" to the top of the formula, then it groups correctly -- a primary with a few secondaries in each group. When I refresh, it groups incorrectly again. If I remove the WhileReadingRecords from the formula and save, then it groups correctly again.

And actually, there's another group above this group. When I remove that group, then the primaries and secondaries stay grouped correctly when I refresh. I don't know if that makes sense. It may be easier to ignore all of this and just address the question. I don't know. Thanks much, regardless.

Mark Knapp

 

[dgillz]

Change your WhileReadingRecords to WhilePrintingRecords.

What version of CR are you using? There are some known issued with CR V7 and formulas and variables. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[dgillz]

Change your WhileReadingRecords to WhilePrintingRecords.

What version of CR are you using? There are some known issues with CR V7 and formulas and variables. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[knappm]

Changing it to WhilePrintingRecords results in a "Group specified on a non-recurring field" error.

Am I even approaching this the right way? Is there a better way? Is this even possible?

Thanks.
Mark