Shared variable yields 0

[gbuchman1]

Hi,
I created a subreport that calculates a value using a formula. I then shared this value with my main report by making it a shared variable. However, the shared variable always shows as 0 in every area of the main report. It shows correctly however on the subreport.

Here are the details: the main report yields account values for 24 cost centers for each of 100 account numbers. So the report is a large grid. The subreport is a mini version of the main report in that it gives account values for 24 cost centers for one account number. Then it calculates the percentage of the total for each cost center. It is this percentage figure that is my shared variable that I want to use on the main report in formulas. There is one formula for each of the 24 cost centers and I actually will have 24 shared variables; one for each of the cost centers.

The subreport is in the group header (the group is account number) and the reports are linked by account number. I did create a shared numberVar for each of the 24 percentages from the subreport. But it always shows as zero and therefore doesnt do the calculation I need done.

Even if I show the account from the subreport on the main report, the formula STILL shows as zero. I declared the first variable on the main report like this:

whileprintingrecords;
shared numberVar A;
SUM({%A Edison AT4009}, {tblGLBLBalance.sCodeIDf_0}) +
A * {@AmtAllocbyGL}

I declared the variable in the subreport like this:

whileprintingrecords;
shared numbervar A := {@PercentEdison}

{@PercentEdison} is the formula that yields the desired percentage.

Please assist me with this annoying problem. Thanks!

 

[lbass]

If you mean to use the subreport percentage in calculations in the main report, then the subreport needs to be in GH_a, and the calculation needs to be in GH_b to work. If you need to (for alignment), you can format GH_a to "underlay following sections."

-LB

 

[dgillz]

This formula:


whileprintingrecords;
shared numberVar A;
SUM({%A Edison AT4009}, {tblGLBLBalance.sCodeIDf_0}) +
A * {@AmtAllocbyGL}

Is not assigning a value to the variable. If you wish to assign the variable the value use := to do so.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[gbuchman1]

There are two group headers and I tried putting the subreport in both of them and still got zero. I also put a formula declaring the shared variable as:

shared numberVar
A := A

and placed this just after the subreport and just before the formula which does the calculation. Still got 0.

If I put
shared numberVar
A := .0676

which is the number that is calculated in the subreport, then the main report works fine. But of course, I do not want to hardcode this number as that defeats the purpose of the shared variable. Actually if I could have the value of the shared variable from the subreport converted to a constant and used as such in the main report, that would be ideal, since I want that number to be the same for every record in the main report. But for now, I still cannot get this to work - still always shows 0. Please help me find a solution to this problem. Thanks.

 

[lbass]

Your description is too vague. Please identify your groups by the fields you are grouping on, and identify sections using the full section description GH1a, GH1b. Then tell us in what section you have put the sub, and what section you have put the formula (and show us the content of that formula, so we know which one you mean). Also, in the sub, in what section have you placed the shared variable formula (and show us its content). Also identify how the sub is linked to the main report.

This issue is not a complex one--we just need to understand your report layout better.

-LB

 

[gbuchman1]

Ok. In the main report,there are two group headers.

GH1 groups by @Category. This groups account numbers into categories.

GH2 groups by account number which is tblGLBLBalance.sCodeIDf_0.

GH2a contains the subreport.

GH2b contains the formula @EdisonSharedPct which is:
shared numberVar A := A

GF2 contains the formula @AmtEdisonbyGL which is:
whileprintingrecords;
shared numberVar A;
SUM({%A Edison AT4009}, {tblGLBLBalance.sCodeIDf_0}) +
A * {@AmtAllocbyGL}


In the subreport,
GH1 groups by @Category (same as main report)

GH2 groups by tblGLBLBalance.sCodeIDf_0 (same as main rpt)

GF2 contains the formula @AmtEdisonbyGL-1 which is:
SUM({%A Edison AT4009}, {tblGLBLBalance.sCodeIDf_0})
Only one acct is selected for the subreport whereas many accts are selected in the main.

GF2 also contains @PercentEdison which is:
ROUND (({@AmtEdisonbyGL-1} / {@All499901-Allocated}),4)

{@All499901-Allocated} is the sum of all the @AmtbyGL formulas across 24 columns representing 24 sites, of which Edison is just one. So the formula is finding what % Edison is of the whole.

I then want to share this percent with the main report so that it can be used to allocate expenses to the 24 sites in the main report.

RF contains @PercentEdison-1 which is:
whileprintingrecords;
shared numbervar A := Abs ({@PercentEdison})

I also tried this for @PercentEdison-1:
whileprintingrecords;
shared numbervar A := A

Both of these yielded zero on the main rpt.

I tried linking the two reports by tblGLBLBalance.sCodeIDf_0 with value of 499901 (an acct number). I also tried removing the link. Both times, I still got 0 in the main report.

 

[lbass]

The sub should be linked on both group fields: category, and code ID. Does the percent display correctly in GH2a and GH2b? You didn't suppress GH2a or the subreport itself, did you? Because that will cause the shared variable not to display. And did you also make sure the 0 was formatted to % with decimals?

-LB

 

[gbuchman1]

I did all that you said. I did not suppress GH2a or the subreport. The percent still shows as 0 in both GH2a and GH2b. I want this to show as a decimal so I dont want to format it to %. For now I just want the proper decimal number to at least show up.

 

[lbass]

Well, if you run the sub on its own, does the figure show up correctly?

Also, the GH2a cannot be hidden either.

-LB

 

[gbuchman1]

Yes, if I run the sub on its own, the proper figure shows up. This is the figure that I am trying to share with the main rpt that I cannot share. GH2a is not hidden.

When I tried to link the sub to the main using the two groups in main, @Category and {tblGLBLBalance.sCodeIDf_0}, then the subreport yields all zeroes. If I unlink the sub from the main, then the sub runs fine.

 

[gbuchman1]

Something that might be impacting this: on the main report, I exclude tblGLBLBalance.sCodeIDf_0 = 499901. On the subreport, that is the only account that I include. So in a way, the reports are mutually exclusive. However, if I add acct 499901 into the record selection for the main rpt, the shared variable still doesnt show up on main.

 

[lbass]

The subreport MUST have a corresponding match in the main report. A subreport acts like a left joined table, where there may or may not be a value in the sub that matches the value in the main report, but there must always be a value in the main report for a subreport value to appear.

Make sure the values in the main report and subreport account are of the same datatype and formatted the same. Does ANYTHING in the sub appear when you include 499901 in the main report? If not, there is something wrong with your selection formula or there is still a mismatch with the linking field in some way.

-LB

 

[gbuchman1]

When I include 499901 in the main report, I still do not get anything from the sub. I am convinced now that the reports do not link right because they do not contain the same accounts. The sub contains 499901 and the main does not. Also the two main groups in main are both dependent upon the account code. Since that appears to be the problem, is there any other way that I can get these percentages to be on the main report? I guess if the user needs to run the sub to see the percents and then has to key them in as parameters, we can do that, altho that is what I wanted to avoid.

 

[lbass]

If you don't link the sub on the account number, it will show up, but I'm not sure that's what you need. If you don't link the sub at all, then the subreport will show you the same value throughout the report--is that what you want?

You should at least TRY running the report with the sub unlinked--if only to see if the sub ever returns any values.

-LB

 

[gbuchman1]

Is there a way that I could make the subreport totals go to a different main report and then use that one as the sub for my original main report? Would like not have to manually enter the percentages in the main report.

 

[lbass]

Did you see my last post?

I think we just don't have enough information. In the sub, what is the content of {@percentEdison}--please show the exact formula. Does it require a calculation that includes OTHER accounts besides 499901? If so, then you should NOT link on the account number when linking the sub to the main report. I don't know how category fits in. Maybe you should explain how the calculation relates to the groups.

-LB

 

[gbuchman1]

Actually, I have tried running it with the subreport unlinked, but still I get zeroes in the main report. I definitely would like to get the same value through the report - I dont want it to change on the main report. If this could work, that would be the answer.

 

[gbuchman1]

The {@percentEdison} formula is:
ROUND (({@AmtEdisonbyGL-1} / {@All499901-Allocated}),4)

The {@AmtEdisonbyGL-1} is:
SUM({%A Edison AT4009}, {tblGLBLBalance.sCodeIDf_0})

The {%A Edison AT4009} is:
CASE
WHEN (tblGLBLBalance.sCodeIDf_1 = '0078' AND tblGLBLBalance.sCodeIDf_2 = '0017') THEN
tblGLBLBalance.curDebit - tblGLBLBalance.curCredit
ELSE
0
END

Each GL acct has three segments. They are
sCodeIDf_2 - sCodeIDf1 - sCodeIDf0. The above SQL expression is looking for all GL accounts with first segment = 0017, second segment = 0078. Then I set record selection of sCodeIDf_0 = 499901. So this account only includes 1 GL acct: 499901.

 

[gbuchman1]

Is there a way that I can pass my percentages from the subreport as constants to a spreadsheet, and then pull them from the spreadsheet into the main report? The user will not mind doing a couple of steps to make this happen.

 

[lbass]

If all you want is one constant figure from the sub (then why do you refer to percentages (plural) though? And why bother with grouping?), then don't link the sub to the main report. You could place the sub in the report header then.

If you get the correct percentage in the report header, you should be able to reference the variable in any other section for the report below the report header.

Make sure all formulas have the variable identified as "shared"--it's easy to forget that.

Make sure you have not suppressed the subreport or suppressed or hidden the section the subreport is in. Other than that I see no reason why this wouldn't show up.

-LB