Using shared number variable from main report in subreport

[random621]

I am using crystal reports 8.5 to query an access database.

Our company helps farmers sell their crops (commodities) for the best possible value. The report I am creating shows each farmer the individual sales we have made for each of their commodities on a separate page in the report.

I show the actual sales for each commodity in a subreport called contract sales.

Currently I am trying to pass the Average state basis (basis is what a farmer pays an elevator to process the commodity) from the main report to the sub report.

I calculate the avg state basis in the main report Group Footer #1A. The Subreport is in the main reports Group Footer #1B.

Using shared number variable I can easily get the avg state basis to display in the sub report. However when I try to use the shared number variable for the avg state basis in a formula within the subreport, I get the error message “a Running total can not refer to a print time formula”.

I have tried setting the formula in the main report to “whileprintingrecords” where I initially create the shared numbervar, but that didn’t seem to help. I have also tried setting the formula in the subreport to whileprintingrecords where I bring the shared numbervar into the subreport. Again, no help.

I’m guessing I should be able to accomplish this one way or another using the whileprintingrecords statement, but can’t seem to get it right.

 

[lbass]

Please share the content of the shared variable formula in the main report and the content of the formula you are using to calculate in the subreport.

-LB

 

[synapsevampire]

Judging by the error, you're not using a formula, you're using a Running Total, is that true?

You might use the 3 formula method within the subreport to make use of the shared variable, but you've posted nothing technical, if you provide example data and the expected output it tends to make things clear.

Also try to avoid subreports in groups as they are slow, whenever possible use Views or stored procedures on the database.

-k

 

[random621]

Shared Variable formula from main report:
shared NumberVar SharedStateBasis := {#RT Avg State Basis}

Formula in subreport using Shared Numbervar;
If isnull({EOMContracts.Basis}) then Shared Numbervar SharedStateBasis else
cdbl({EOMContracts.Basis})

 

[random621]

In the main report, yes I am using a Running Total. What is the 3 formula method?

Also - see my previous post, are the 2 formulas I listed sufficient, if not what other information could I provide?

-RM

 

[lbass]

I think your formulas should work unless there is a problem with the contents of {#RT Ave State Basis}. How did you create that? Did you use an evaluation formula? What is it?

Is the subreport formula you showed the calculation that doesn't work?

-LB

 

[random621]

-LB, first off thanks for taking the time to help me on this.

I'll try to answer your question best I can.

I created the {RT Avg State Basis} field by right mouse clicking on {@State Basis} field in the detail section of my main report and choosing -> Insert -> Running total. The running total evaluates each record and then resets on Change of group 1.

{@State Basis} formula says this:
.01 * {EOMBasis_By_State.basis}

Group 1 is grouped by a formula that says this:
{EOMCommodity.Lastname}&{EOMCommodity.Firstname}&{EOMCommodity.AccountNumber}&{EOMCommodity.CmdtyYear}&{EOMCommodity.Commodity}&{EOMCommodity.Sp Mh Commodityid}

Other note - I found some other posts where synapsevampire describes the "3 formula method" - I'm trying to figure out if that is what I need to be doing to create the avg in the first place?????

-RM

 

[lbass]

I can't recreate your problem. I can use a running total from the main report in a shared variable and then use it in a calculation in the subreport.

How are you using the following formula in the subreport?

If isnull({EOMContracts.Basis}) then Shared Numbervar SharedStateBasis else
cdbl({EOMContracts.Basis})

-LB

 

[random621]

I think I figured out what is causing my problem.

I use the formula called "Contract Basis" - If isnull({EOMContracts.Basis}) then Shared Numbervar SharedStateBasis else
cdbl({EOMContracts.Basis}) as part of another formula to calculate contract value. Contract Value = Contract Futures + @Contract Basis.

The contract value ends up being used in the subreport as part of another running total. Using "whileprintrecords" I add up these running totals (reset at the group 1 footer in subreport) and then display them back on the main report as a Total contracts value.

My guess is I have created some kind of loop. It gets complicated to explain the various hoops I am jumping through (probably due to poort report design, but I am self taught and learning all the time from mistakes). Is their a "kosher" way for me to send you the actual report?

 

[lbass]

How did you do the running total in the subreport? I'm guessing this is a manual running total, but I can't figure out how you would get the error message in that case. I've recreated your problem up until that point, so please provide the remaining running totals so we can figure this out.

Just a thought here. I can't see why you would need a running total in the main report in the first place. You should be able to right click on {@State Basis} to get the average. This makes me wonder whether you need the running totals in the subreport. Some of these issues might go away if you simplify your approach, but maybe there is a reason for using the running totals that you haven't shared.

-LB