Group sum based on conditional formula field

[tkaz]

Using Crystal XI
SQL Server DB

I have been asked to modify a report that includes 4 groups:
Company, Location, Location2, Material...built on stored procedure.

Location A
ProdID LTMUsage EndInv UnitCost Value >12MosUsage Value
375 0 44 18.25 803 44 803
470 -1304 168.75 15.76 2659.50 0 0
510 0 249.60 18.25 4555.20 249.60 4555.20
645 -818 298 17.75 5289.50 0 0
LOC TTL -2122 760.35 NA 13307.20 NeedTTL NeedTTL

The >12MosUsage and Value are the two columns I have added based on conditional formulas:

if Sum ({@EndingQTY}, {orptOMGINSummInventory;1.Material})+Sum ({@ProdUsageQTY}, {orptOMGINSummInventory;1.Material})
<=0
then 0
else Sum ({@EndingQTY}, {orptOMGINSummInventory;1.Material})+Sum ({@ProdUsageQTY}, {orptOMGINSummInventory;1.Material})

AND

if {@12 Mos Usage MATERIAL}>=0
then {@12 Mos Usage MATERIAL}*{@UnitValue}
else 0

I placed these formulas at the Material level and they work fine. However, I can't figure out how to get a sum at the Loc group level.

I'm sure there is a way to do this, but I am totally blocked and have a deadline to complete. Can someone get me on the right track?

Thanks...

 

[lbass]

Use a variable, with four formulas:

//{@reset} for the location group header:
whileprintingrecords;
numbervar sumqty;
numbervar sumusage;
if not inrepeatedgroupheader then (
sumqty := 0;
sumusage := 0
);

//{@accum} to be placed in the section containing your conditional formulas:
whileprintingrecords;
numbervar sumqty := sumqty + {@your1stformula};
numbervar sumusage := sumusage + {@your2ndformula};

//{@displaysumqty} to be placed in the location group footer:
whileprintingrecords;
numbervar sumqty;

//{@displaysumusage} to be placed in the location group footer:
whileprintingrecords;
numbervar sumusage;

-LB

 

[tkaz]

Thank you! I'll see if I can make this happen!

 

[tkaz]

Lbass...you are the best! I don't think you've ever NOT been able to do something! Thanks again!

tkaz

 

[tkaz]

I am referencing LBass response to thread 767-1572376 regarding sums of running totals.

Your solution was perfect for my groups, but I must also include grand totals in for the report. (So, I did search before I asked another question.)In the thread referenced above it says that you have to have separate formulas for the group and the report footer.

Does that mean that I would need to create these same variables with the exception of the reset in group header? I do not have a very good understanding of variables and how they work and would appreciate any clarification you can offer.

Thanks....

 

[lbass]

Create a different name for the grand total variable, e.g., numbervar grtot;

You could add a line into the {@accum} formula:

numbervar grtot := grtot + <yourvaluetosummarize>;

Don't reset the variable; just display the result in the report footer:

whileprintingrecords;
numbervar grtot;

If you want to summarize two values, create another variable grtot2 and follow the above. What you name them is irrelevant, as long as you use the names consistently in the appropriate sections.

-LB