Hello- I have an invoice report.

[ksnielsen]

Hello-

I have an invoice report. I need to calculate postage deposits to display on the invoice. I cannot include the tables needed to calculate postage deposits in the main invoice report so I think I need a subreport.

I have added a subreport but I cannon figure out how to get the information I need.

The main invoice needed to pass the invoice-id to the subreport. The subreport needs to return the total postage deposit. The way I have it set up right now, it returns the invoice-id.

What do i have to do to get the calculation happening in the subreport back to the main invoice?

 

[MCuthill]

ksnielsen,

Generally, the means to move values back and forth from a Main Report into and/or back out of a Sub Report is to use Shared Variables. To set this up, please create the following formula fields - I have made some assumptions on placement, so there may be a little fine tuning needed. =)

In your Main Report, in the Invoice ID Group Header (or whatever group level you would like to "reset" this total, I presume since the total is for an invoice, that the reset would be at the start of each invoice) please the following:
{@VariableReset_InvoicePostage}

WhilePrintingRecords;
Shared NumberVar InvoicePostage:=0;

In your SubReport (in the footer) please place a formula like follows (this one will need to be changed to fit your field names).
{@VariableAccum_InvoicePostage}

WhilePrintingRecords;
Shared NumberVar InvoicePostage:= SUM({YourPostageAmountField});

To display this back on the main report, place the follwoing in a section below the subreport (any section below will work, it just cannot be "above" the subreport).
{@VariableDisplay_InvoiceTotal}

WhilePrintingRecords;
Shared NumberVar InvoicePostage;

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[ksnielsen]

Thank you Mike. Your explanation really helped to make it more clear to me.

It isn't working completely for me yet. The first formula setting the NumberVar PostageInvoice:=0 is working.

The NumberVar PostageInvoice in the subreport is working. It is calculating the correct total. However, I get a zero value returned to the main report. I have the subreport in GroupHeader#1 on the main invoice, and the return variable in Group Footer#3 so that should mean my subreport is above the return variable.

Formula in main invoice:
WhilePrintingRecords;
Shared NumberVar InvoicePostage := 0;

Formula in subreport:
WhilePrintingRecords;
Shared NumberVar InvoicePostage:=Sum({@Postage Deposits})

Formula to get return value from subreport:
WhilePrintingRecords;
Shared NumberVar InvoicePostage;

The Subreport Links has the following settings:
field to link to : @Invoice Number
Subreport parameter field to use: ?Pm-@Invoice Number
Select data in subreport based on field is checked.
Drop down below that is ARInvoiceHeading.Invoice-ID

Any ideas?

 

[MCuthill]

ksnielsen,

Hmm... that is curious. Nothing "jumps out" at me as an issue with the above formulas, so I am thinking it must be a placement issue. Where abouts do you have the {@VariableReset_InvoiceTotal} formula field located? The reason I ask, is I am thinking that the reset is happening somewhere between the sub-report and Group Footer 3 where the variable is being displayed. A quick test you could do is to place the Display formula in any other location (or several others) to determine if it is returning anything but zero anywhere on the main report (may also allow you to visually see where the reset to zero is triggered).

Alternately to both of the above, if you could provide a full structure layout of the report, it may assist should the above ideas not help in tracking down the issue. I presume that Group 1 is Invoice ID (as that is your link to the Subreport), if this is not the case, it could also be the cause.

Sorry if the above is about as clear as mud, but hopefully helps in narrowing down where the issue is with passing this Shared Variable around.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[ksnielsen]

Mike-

I have tried moving the reset formula and the final total formula to every section of the report and it is always zero. Do you think it is a problem to have the final total formula based on another formula in the subreport.

PostageDeposits:
IF {CashRecDetail.Postage-Deposit} = True THEN {CashRecDetail.Amount-Deposit-Applied} ELSE 0

Then I get the sum of PostageDeposits:
WhilePrintingRecords;
Shared NumberVar InvoicePostage:=Sum ({@Postage Deposits})

I tried changing the sum of PostageDeposits to the following and I still got zero:
WhilePrintingRecords;
Shared NumberVar InvoicePostage:=20

The reset variable is in the page header. The subreport is in Group header 1(invoice-id) and the total is in Group footer 3.

How would I do the following: "Alternately to both of the above, if you could provide a full structure layout of the report, it may assist should the above ideas not help in tracking down the issue.

 

[MCuthill]

ksnielsen,

I think it could be the location of your reset, as you are resetting it on the top of each page. I would make the presumption that your "InvoiceTotal" would reset on a new Invoice ID, or in your case in Group Header 1. As one more test to try, can you insert a new section at Group Header 1, presuming you have not split this prior, your report would then having headings that look something like:
[tt]Report Header
- Page Header
- - Group Header 1a
- - Group Header 1b
etc etc.[/tt]
(note: this is one means to show a report structure)

Anyways, once you have a new section inserted into Group Header 1, click and drag the new empty section above the section with your subreport and place the Reset Formula into the "GH 1a" section.
These areas would then look something like follows:
[tt]- - Group Header 1a | {@VariableReset_InvoiceTotal}
- - Group Header 1b | {Your Subreport Here}[/tt]

Just to be sure, in your previous post, you stated: [red]"The NumberVar PostageInvoice in the subreport is working. It is calculating the correct total."[/red] -- is the {@VariableAccum_InvoiceTotal} formula field showing the proper value, or is your "Summary of" {@Postage Deposits} is showing correctly?

Please advise and we can see about getting this sorted out yet!

Cheers!




Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[ksnielsen]

Thanks for you persistence Mike. I made the suggestions you recommended however I still get zero as the return value.

RH
PHa
PHb
PHc
PHd
PHe
PHf
GH1a - resent postage to 0
GH1b - subreport
GH2
GH3
Details
GF3
GF2a

Just to be sure, in your previous post, you stated: "The NumberVar PostageInvoice in the subreport is working. It is calculating the correct total." -- is the {@VariableAccum_InvoiceTotal} formula field showing the proper value, or is your "Summary of" {@Postage Deposits} is showing correctly?
GF2b

I have both values in my subreport and both are totaling correctly. The NumberVar PostageInvoices in what is set to return to the main report.
GF2c
GF2d
GF2e
GF2f
GF2g
GF2h - display postage deposit
GF2k
GF1
Rf
PFa
PFb
PFc
PFd
PFe

 

[IanWaterman]

Create a formula to display shared var in Subreport footer

WhilePrintingRecords;
Shared NumberVar InvoicePostage;

This will confirm its not being reset in sr before being passed to Main report

Split section holding SR and add display formula to section underneath subreport again to confirm not being reset.

If all OK, keep moving display formula to different sections until it disappears.

Ian

 

[ksnielsen]

IanWaterman-

I created another section under Group Header 1 and the value is still "-" or zero.

GH1a Reset Postage to 0
GH1b SubReport
GH1c DisplayPostage

If I go to the subreport the value is correct, but it isn't coming back to the main report through the DisplayPostage formula field.

 

[IanWaterman]

If the value is zero in GH1c DisplayPostage

Then you problem is in the SubReport

Some how it is being reset after its evaluated in SR after GF2h - display postage deposit
GF2k

Did you create a display formula and place in Rf of SubRep?

Ian

 

[ksnielsen]

This is what I have in my subreport.
RHb - nothing
Details ?Pm-@Invoice Number (to pass the invoice number to subreport)
Amount-Deposit-Applied (This is the field that needs to be summarized.
RFa @PostageSum
This is the formula: WhilePrintingRecords;
Shared NumberVar InvoicePostage:=Sum({@Postage Deposits})

If I preview the subreport, I have to enter in an invoice number and I can see the correct postage amount.

 

[IanWaterman]

Does this mean subreport is normally hidden?

Allow SR to grow and show RFa and then run report normally.

Is variable showing value?

If not it could be your links? Place fields being used to link in RFa too and see what values are bing returned.

Ian

 

[ksnielsen]

Ok, I started over following the instructions from Mike and not it works. Thanks for helping me know how to trouble shoot the problem Ian. That helped me know that the subreport was wrong.

Now my report is going to a new page after the heading fields. The whole report would easily fit on one page. Any idea why that is happening?

Thanks!

 

[MCuthill]

ksnielsen,

What you are seeing is possibly the white space created by the subreport (assuming I have read the above correctly, that a suppressed subreport was the cause of the zeros). In reporting where I have a subreport that needs to behave as though it was suppressed/hidden but couldn't be, as I needed to calculate a variable, like in your case -- I have made the SubReport as vertically small as able to have the variable still be set with the proper value(s).

To circumvent the "blank page" -- in your subreport, suppress all but the "Footer" where your Variable Set happens and change the font color of your Variable Set formula to white so that it is "hidden" against the page color. Then on the main report, change the vertical size of your subreport until it is approximately the height of a standard "row" on a report.

What this will do is create a small (one-line) white space where your variable is calculated, set, and passed back to the main report. As an aside, the width of these subreports can be narrowed so that in the event you had multiple variables to populate, multiple subreports can be placed side-by-side in the same "hidden" area of the report.

I hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[ksnielsen]

I got it. All is working and beautiful! Thank you both for helping me. It is greatly appreciated.
Karen