Summary based on running total, Possible? 2

[Tianjin]

I have a report include a running total field that is used for check qty balance base on Stock Qty, OrderQty and Purchase order qty for each product. In this way, purchaser can discovered those purchase orders are later than sales order. balance qty could be a positive number which means OK and it also could be a negetive number that indicates requirement can not be met.
However I don't want the products without negetive balance qty shows up in the report. for the detail section, I can check balance qty and suppress the record accorrdingly. but in Product GF, I think I can just simply make summary on balance qty, which is result of running total, and see if the minimum one is less than zero. if yes, show GF, if not, Hide GF. However, I found summary can not base on running total. any help?

TianJin

 

[IdoMillet]

You can't sum Running Totals. You can however use global variables to accumulate the desired quantities...

hth,
- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

If you want to suppress the group footer if ALL details are positive for that group, then you could use formulas like this:

//{@reset} in the group header:
whileprintingrecords;
numbervar neg;
if not inrepeatedgroupheader then
neg := 0;

//{@checkdet}:
whileprintingrecords;
numbervar neg;
if {#yourrt} < 0 then
neg := neg + 1;

//{@suppress} to be entered in the section expert->group footer->suppress->x+2 area:
whileprintingrecords;
numbervar neg;
neg = 0;

-LB

 

[Tianjin]

Hi Lbass,

That is super.
I struggled for days with suggestion from Idomillet and get similar result as yours, but you solution is more comprehensive one with "if not inrepeatgrouphearder then" statement in place that prevents a potential error in my report.

Thank you very much


Tianjin

 

[Tianjin]

Continue on my expected result on the report. Based on detected status of all records in the group(status could be having records with minus balance qty in a group or no minus balance qty at all in a group), I would like display all detail records if any record in that group has minus balance qty. The detail section expect to be evaluated twice by using formula
1. check all the balance qty while running total us formula.
2. accroding the result, supress or show the detail records

Is this possible? or has to look other approach.

Thanks

Tianjin

 

[lbass]

If you are checking for the negative value of a running total in order to suppress (or not) the entire detail section, I think the only way you can do this is to save the report as a subreport that is linked on the group field and placed in a group header section, and then pass a shared variable down to the main report to use in the section expert. You could use my formulas above in the subreport, but make them all shared variables. Then in the main report section expert->details->suppress->x+2 use:

whileprintingrecords;
shared numbervar neg;
neg = 0;//note no colon

You would need a reset formula in the main report group footer section:
whileprintingrecords;
shared numbervar neg := 0;

You should be able to suppress all sections within the subreport and format the subreport to "suppress blank subreport" so that the sub does not appear in the main report display.

-LB

 

[Tianjin]

Hi IBass,

I modified my report as you recommended. Theoretically, my report should work with subreport idea. However, My report is designed to simulate a MRP system where just complete a single run of this report will take about 3 to 5 minutes. So with subreport embeded in each Header, this lengthy process will likely be triggered many time and end up my screen is frozen.
I am capable of VBA programming. I wonder if some ways of programming effort could achive second formula evaluation on detail section.

Thanks

TianJin

 

[lbass]

I'm not familiar with that, but I think if you must use a running total, you are stuck with the subreport approach, because running totals are calculated sequentially and the results cannot be made available to suppress an earlier record.

-LB