Group Sum By

[postmanplod]

Hello,

I have a report whereby it lists parts with a stock valuation. I have been asked if this figure can be placed under a 'bucket' on the report depending on the last transaction date. There are 4 buckets - 0-6 months, 6-12 months, 12-18 months and 18+ months. Also, if data existed for 6-12, 12-18 and 18 +, it would only be displayed in the most recent bucket, which would be 6-12 in this example. Ok, now the code I am using to work out the stk value for each bucket is (for the 6-12 month bucket):

if {BAQReportResult.PartTran.TranDate} in dateadd("m",-12,currentdate) to dateadd("m",-6,currentdate) then maximum({BAQReportResult.StkValue},{BAQReportResult.Part.PartNum})

This works fine, as do all the other buckets. The problem is when I try to sum it. Each part belongs to a class, so the report is grouped by part class. I require totals for each bucket for both the part class and grand total figures. I have tried a basic sum function but keep receiving the message 'A Group Condition is not allowed here'. Also for some reason, the formula's used for each bucket don't appear as an option to select when using running totals.

Is anyone able to help?????

Michael

 

[lbass]

What version of CR are you using?

Your formula will return the highest stkvalue per item in the identified period--Is that really what you want? Or do you want the stk value that corresponds to the most recent trandate?

-LB

 

[postmanplod]

Hello,

Yes, I only want to return the maximum value. But then sum these.....

Michael

 

[lbass]

Okay, so as long as the most recent transaction date for a particular part falls into the specified period then you want to show the largest value for that part within the period, even if it is NOT the most recent transaction date. If this is the case, then insert a group on part and then on class (but NOT on bucket). Then you need a series of formulas like this:

//{@maxvalbkt} to be placed in a Group #2 (partno) section:
whileprintingrecords;
numbervar clmaxvalbkt612;
numbervar gtmaxvalbkt612;
if maximum({BAQReportResult.PartTran.TranDate},{BAQReportResult.Part.PartNum}) in dateadd("m",-12,currentdate) to dateadd("m",-6,currentdate) then
clmaxval612 := clmaxval612 + maximum({BAQReportResult.StkValue},{BAQReportResult.Part.PartNum});
gtmaxvalbkt612 := gtmaxvalbkt612 + clmaxval612;

//{@resetbkt} to be placed in the class group header #1:
whileprintingrecords;
numbervar clmaxvalbkt612;
if not inrepeatedgroupheader then
clmaxvalbkt612 := 0;

//{@displbkt612} to be placed in the class group footer #1:
whileprintingrecords;
numbervar clmaxvalbkt612;

//{@displgtbkt612} to be placed in the report footer:
whileprintingrecords;
numbervar gtmaxvalbkt612;

Repeat for the other buckets. You can just add additional variables to the reset and maxvalbkt formulas, but you will need separate display formulas for each bucket.

This doesn't address the issue of "displaying" only the most recent transaction per part. If that is the goal, then also go to report->selection formula->GROUP and enter:

{BAQReportResult.PartTran.TranDate} =
maximum({BAQReportResult.PartTran.TranDate},{BAQReportResult.Part.PartNum})

-LB

 

[postmanplod]

Ok thanks for this!! I will give it a try and let you know....

Michael