Passing sum from subreport to main report details, then hiding details based on that value 1

[NBVC]

I am trying to copy the sum of a field from a subreport to the details section of the main report and then I am trying to hide all the details based on that value being <0.

In the subreport I added formula: @TotalWIP

WhilePrintingRecords; 
shared numbervar x := x +  ({Jobs.jmpProductionQuantity});

Then in the main report, I added a detail section below the existing detail section with formula: @TotalWIP

WhilePrintingRecords; 
shared numbervar x;
x

The problem is, it is entering the cumulative sum from the previous sections instead of the total for each section.

How do I reset the value for each detail line?

Then, how do I hide all the entire detail section based on that value say being less than 0?

 

[lbass]

In what section is the subreport located? Is there a group in the main report? And what section are you referring to when you say “previous section”?

-LB

 

[NBVC]

The subreport is in "details a" section
Yes, there is a group in the main report
By "previous" section, I meant the previous details extracted in "details a"

I think I figured the cumulative summing problem by adding a formula in the subreport header to reset the variable

//@reset
WhilePrintingRecords; 
shared numbervar x := 0

I also added a suppress formula to the main details section to suppress all details based on that total coming from the subreport.

IF {PartRevisions.imrQuantityAllocated} < 0 then
    formula = True
else
    formula = false
end if

Did I do this correctly?

 

[lbass]

The reset in the sub is correct. But also as good practice you should add a formula in the subreport footer:

Whileprintingrecords;
Shared numbervar x;

The subreport should be in detail_a. In the main report, move your your current detail_b section to be detail_c, by going into design mode and dragging the section up one level. Now you have the sub in det_a, your {@TotalWIP} formula in det_b, and your main report details in det_c. You can now suppress det_c with the following (in Crystal syntax) assuming you meant to use the subreport total for suppression!:

Whileprintingrecords;
Shared numbervar x;
X<0

To make detail_b disappear, just suppress your {@TotalWIP} formula and format that section to “suppress blank section”.

To make detail_a disappear, you must NOT suppress the section or the subreport object itself, but instead, suppress every section within the subreport, and then in the main report, remove the subreport borders, format detail_a to “suppress blank section” and then go to format ->format subreport->suppress blank subreport.

-LB

 

[NBVC]

Hi LB

So there is no way that I can just go to the main "Details" in the Section Expert and apply a suppressing formula that will suppress all detail subsections based on a formula which includes the {@TotalWIP} formula?

The condition may become more complicated than just checking if less than 0.

 

[lbass]

Not sure what the issue is. Whatever your conditions are, you just build them into the suppression formula for detail_c. Detail_a and detail_b will never have to show. Or do you want the sub to show?

-LB

 

[NBVC]

Well, I unintentionally may have misinformed you a bit. I was just trying to make it less complicated.

My section "details_a" contains details about a Part Number.
My section "details_b" is the one that contains the subreport connected to the parent part number and it also contains a vertical line on the left side of the subreport.
My section "details_c" will contain the {@TotalWIP} formula

FYI: I am not grouping by Part Number. I am grouping by a higher marker "Part Type"

suppressing details_c would be easy enough by just suppressing the section as you mentioned.

If the subreport returns no data, then I still need to show my parent information in details_a. So I already have the subreport getting suppressed when no details retrieved, but I have to have the vertical line still showing up in the report as an indicator to the user that the report came up blank (that was their request just so there was some space before the next part).

Ultimately, I need to however suppress all detail sections when one of the fields is less than 0 (for now anyways, more conditions will be added later I'm sure).... currently the formula would be {PartRevisions.imrQuantityAllocated} < 0

 

[lbass]

The subreport MUST be above the section you want to suppress, so you should set up as I suggested earlier. Shared values only share downwards.

If you want a line to show, you could do that in a separate section that shows when the sub doesn’t. Also not sure why you wouldn’t just use a group on part number and then use a blank group footer as a separator.

-LB

 

[NBVC]

If you want a line to show, you could do that in a separate section that shows when the sub doesn’t.

I actually did try that at first, but was having trouble apply condition to say show if sub didn't

Also not sure why you wouldn’t just use a group on part number and then use a blank group footer as a separator.

Good point. I was reducing another more complicated report to this one insteading of starting from scratch and that hadn't dawned on me..

I will try your suggestions. Thanks very much for your assistance.

 

[lbass]

PS. I thought you wanted to suppress based on subreport data, but I see that you only wanted to display the subreport data in your visible detail section. Still my suggestions all apply, except you should use your suppression formula on the detail_c section, but NOT on the subreport section.

-LB

 

[NBVC]

I think I may have figured out a different way. I pulled the same total that the subreport would yield by creating an SQL Expression. Now I may be able to use that instead of the shared variables.

 

[lbass]

Yes, that could work, but since SQL expressions directly access the database, make sure you are getting the correct values—you might need to build in your selection criteria.

-LB

 

[NBVC]

Ok. Thanks again.