Passing Shared Variables from Subreport to Main

[debunky]

I have a subreport that is linked to my main report via a product code. I am doing the SUM of a quantity field in the subreport. I use a shared variable to pass that quantity to my main report in order to do calculations. Everything works fine when there is a match between subreport and main report on product code.

The problem is when there is no product code match on the subreport but there is a record in the main report. The main report is using the PREVIOUS quantity in my calculations. I can't seem to initialize the quantity field that is being passed to the main report. I've tried setting the variable to zero in the subreport formula @StoredQty but that doesn't work.

Here is what is in my subreport formula:

@StoredQty
Shared NumberVar SubQty;
if {wascon.prod_code} = {?Pm-wamprd.prod_code} then
SubQty := Sum ({wascon.qty}, {wascon.prod_code});

Here is what is in my main report:

@MainSubQty
Shared NumberVar SubQty;
SubQty

And my calculations:

@Excess
WhilePrintingRecords;
if {@MainSubQty} > {@MainSubMax} then
Int(({@MainSubQty} - {@MainSubMax}) / {@Case/Pallet})

The @MainSubQty formula has the previous value in it when there is no match between main and subreport.

I have literally been playing with this for days!!!! Any help would be appreciated.

Thanks!

 

[Ngolem]

this is simple once you get the hang of it

First the subreport will execute ONCE even though the report returns no records...and that is a good thing.

What you need is an initialization formual in the report header that is suppressed.

@initialization
Shared NumberVar SubQty := 0;

this will reset your SubQty value every time and create a value that can be passed back to your main report

Now you have to modify your calculations in the Subreport

@StoredQty
Shared NumberVar SubQty;
if {wascon.prod_code} = {?Pm-wamprd.prod_code} then
SubQty := Sum ({wascon.qty}, {wascon.prod_code});

I would change the subreport so that only the correct prod_code data is presented in the subreport....so I would remove this test to the Record Selection Formula

{wascon.prod_code} = {?Pm-wamprd.prod_code};

then I would keep my group on prod_code in the subreport and change @StoredQty to the following:

@StoredQty
WhilePrintingRecords;
Shared NumberVar SubQty;
if not isnull{wascon.qty} then
SubQty := SubQty + {wascon.qty};

then in the report footer I would put a display formula (this may not be necessary but I do it for completeness

@display_StoredQty

WhilePrintingRecords;
Shared NumberVar SubQty;

SubQty;

suppress all sections of the subreport of course to render it invisible on the mainreport

Hope this works for you

Jim

 

[debunky]

Thanks! I'll give it a try right now and let you know.

 

[debunky]

I get an error on the @StoredQty formula. It's looking for a 'then' between isnull and {wascon.qty}.

 

[rogar]

You need to put the field in parentheses:
if not isnull( {wascon.qty} ) then

 

[debunky]

It does not work. I have to have the {wascon.qty} = {?Pm-wamprd.prod_code} in the record selection formula in the subreport because if I don't I don't get any of my data for the main report.

When I show both the subreport value and the @MainSubQty value side by side on the main, this is what I see:

For a record where there is a match on both main & subreport:
prod code 1006 @MainSubQty = 20.00 SubReport = 20.00

For no match:
prod code 1007 @MainSubQty = 20.00 SubReport = blank
(The @MainSubQty value should be blank also because there was no quantity on the subreport).

I suppress the @MainSubQty but not the subreport.

Any ideas?

Thanks!

 

[Ngolem]

Rogar is correct about my bug in the formula it should be:

@StoredQty
WhilePrintingRecords;
Shared NumberVar SubQty;
if not isnull({wascon.qty}) then
SubQty := SubQty + {wascon.qty};

I also made a mistake in the record Selection formula....Soory but it was early morning here

that formula should be:

(isnull({wascon.prod_code}) or {wascon.prod_code} = {?Pm-wamprd.prod_code});

that should do it...sorry

Jim

 

[debunky]

Still doesn't work. Is there anyway I can check the subreport value in the main report without passing any values back and forth? Like I said before, when I show both the values for the @MainSubQty (which is coming from the subreport @StoredQty) and the subreport value itself, the subreport value is showing blanks. That tells me the SubQty is not getting initialized and @MainSubQty is using the previous value.

I'm working with data from two different warehouses, but they both have the same product code. The subreport is warehouse 410, the main report is 412. The problem is when there is no matching product code in warehouse 410 which is the subreport.

It makes sense to me about the initialization formula in the subreport but it doesn't seem to be passed to the main when there is no match.

I appreciate all your help.

Thanks.

 

[Ngolem]

does it work when there is a ProductID match?

Where is the subreport placed in your report?

It should be on the same line as the rest of your data...or better yet...create two subsections of the detail

Both are unsuppressed

In Detail(A) place the subreport , make it as think as possible...you cannot suppress this even conditionally or the subreport won't execute

In Detail (B) put the data that you wish displayed (including the shared variable returned from the subreport

doing it this way...forces the subreport to get the data on time

If you want to see the value that is gotten in the subreport....just unsuppress the report footer which has the @display formula

Jim

 

[debunky]

Yes it works just fine when there is a product code match.

My subreport is in the Group Footer #1A - Product Code section and I have the @MainSubQty formula that has the passed variable in the Group Footer #1B - Product Code section with all the other fields. I was told I needed to have the formula with the shared variable in a section under the section where the subreport is located.

I have the @MainSubQty suppressed via the 'format field' option and the subreport is not suppressed.

I did unhide the @display field from the subreport and it shows blank so I'm not sure why it's not using the SubQty initialized to zero.

Thanks, I'll keep trying.

 

[Ngolem]

run the subreport by itself....feed it a productID when the parameter asks for it....both for a valid and not valid value...you should get the expected result or zero.

Is there another link that needs to be done??

to confirm:

1. the Record Selction formula of the subreport is:

(isnull({wascon.prod_code}) or {wascon.prod_code} = {?Pm-wamprd.prod_code});

2. All sections of the subreport are suppressed and there is one group based on ProductCode

3. The initialization formula is placed in the report header

@initialization
//Add this as well
WhilePrintingRecords;
Shared NumberVar SubQty := 0;

4. the @StoredQty formula is in the detail section

@StoredQty
WhilePrintingRecords;
Shared NumberVar SubQty;
if not isnull{wascon.qty} then
SubQty := SubQty + {wascon.qty};

AHHH!!! I know the problem....we are grouping on a NULL

Change the group to a formula

@group1
if not isnull({wascon.prod_code}) then
{wascon.prod_code}
else
"null"; // I assume Prod_code is a string...if not
make it zero for the else condition

Now it should work.

Jim

 

[chelseatech]

I find it easier to reset the shared variable in the main report prior to executing the subreport. Then there is no problem with IsNull's etc.

The subreport should be in a section priot to other formulas using the shared variables, so the reset can be in the same section as the subreport, as it will always execute first. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[debunky]

BINGO! I think the initialization in the main report worked. I'm now getting blanks and it's not picking up the previous value.

Thanks to Ngolem & rogar & chelseatech for all your help. I learned a lot from this forum and I will come back again for answers, thanks again!

debunky