Manual Running Total & Calculations 1

[Crystalyzer]

CR 11

I have a manual running total that I created using the 3 formula method that I want to use in the body of the report to calculate a % of a total (the ending value of the manual running total). I have used Evaluateafter(manual running total display) however this doesn't give me the % of the total. It does give me the % of the value of the manual running total at that point in the report which is obviously not what I want. Does anyone know of a way around this?



Thanks and best regards,
-Lloyd

 

[synapsevampire]

Try posting technical information.

Database/connectivity
Example data
Expected output

Stating body of the report ois meaningless as Crystal doesn't have a body section, please be specific.

An Evaluteafter probably isn't required, if you post where you want the total(s), from where it gets the totals, someone can probably help you.

-k

 

[Crystalyzer]

Sorry k,

This is a subreport that produces the Top 5 securities holdings by Investor across several funds with the remaining securities grouped as "other" so that the grand total of this subreport is total securities. What I have added to the total securities is the total cash on hand in each fund for the investor so that I get a grand total of securities and cash. I am using the three formula method to calculate the total cash (C&E reset, C&E accum & C&E display). I have also found that I can use a regular running total to achieve the same result, however, it gives me the same problem in the percentage calculation below.

The subreport looks like this:

RH    Security Name     Fair Mkt Value  % ofFMV
GH1   Investor (surpressed) @C&E reset
GH2   Security (surpressed)
GH3   Fund (surpressed)     @C&E accum
D     field = security (surpressed)
GF3   (surpressed)
GF2   ABC, Co.          100,000.00      need to calc
GF2   EFG, Co.           98,000.00      need to calc
GF2   HIJ, Co.           96,000.00      need to calc
GF2   KLM, Co.           94,000.00      need to calc
GF2   NOP, Co.           92,000.00      need to calc
GF2   Others            150,000.00      need to calc
GF1a  Total Securities  630,000.00      need to calc
GF1b  Cash               50,000.00      need to calc = @C&E display 
GF1c  Total Sec & Cash  680,000.00      need to calc = @TotSecC&E = Total Securities + @C&E display)

I want to calculate the percentage of each of the items displayed relative to the Total Sec & Cash number. As I stated earlier when I to calc the %'s (need to calc above) the formula returns the securities % of the total with the cash calculated at that point in the report and not the ending cash calculated by @C&E display. I have tried Evaluateafter(@TotSecC&E) to no avail.

Any help would be greatly appreciated. Please let me know if I need to provide more information.

Thanks and best regards,
-Lloyd

 

[lbass]

If you inserted a sum on your detail level amount field would you get a result of 680,000? If so, then use the following as the denominator in your formula for percent:

sum({table.amt},{table.investor})

Otherwise, you will need to add the subreport again in a section above the one in which you wish to calculate the percent, and create a shared variable that is equal to the running total, which you then reference in the percentage formula. The subreport would have to be linked to the main report on the group 1 field investor).

-LB

 

[Crystalyzer]

LB,

thanks for tackling this. The sum would not work as you described but you have inspired me to use your second suggestion. The subreport that would be place in the section above would only need to contain the amount I want to calculate stored in the shared variable correct?

Thanks again.

Thanks and best regards,
-Lloyd

 

[lbass]

I'm not sure what your question is. You don't have to display any of the subreport--you can suppress all sections of the subreport. You want the shared variable to contain your group 1 total, which you will then use in a formula in the second subreport in order to calculate the percentage. Since you didn't provide your formulas, I can't be more specific than that.

-LB

 

[Crystalyzer]

LB my apologies for not being more concise. Your patience is appreciated. I have place the following formulas in my report as follows:

In Report Header of Main Report
@TotSecCE

Shared NumberVar TotSecCE:= 0;

In 1st Sub-Report (used to calculate total for percentage calculation in 2nd sub-report) placed in GH3a of Main report
@TotSecC&E

Numbervar Cash; // used to perform manual RT of cash item
Shared NumberVar TotSecCE:= (Sum({Securities_.Fair Market Value},{Securities_.Investor}) + Cash);

In 2nd Sub-Report (the one described in previous post) placed in GH3b of main report
@Pct of FMV

Shared NumberVar TotSecCE;
Sum ({Securities_.Fair Market Value}, {Securities_.Deal})/TotSecCE*100

When I do this, I get errors that I am dividing by zero.
Am I doing something wrong?




Thanks and best regards,
-Lloyd

 

[lbass]

You showed specifically what was in each section of your subreport, but you didn't indicate how your main report was structured. However, maybe we don't need that yet. If you display the value in subreport 1, is it correct? And is it zero (correctly) in any cases?

-LB

 

[Crystalyzer]

Yes the value in 1st subreport is correct and not zero

Thanks and best regards,
-Lloyd

 

[lbass]

I should have asked whether it can be null. If it is never null or zero, then try adding "whileprintingrecords" at the beginning of each of your shared variable formulas. Also, I'm assuming that your shared variable formula is somewhere on the subreport #1 canvas and that you have NOT suppressed the group header section that it is in, but instead have suppressed the sections within the subreport.

You could also change the final formula to:

whileprintingrecords;
Shared NumberVar TotSecCE;
if TotSecCE = 0 then 0 else
Sum ({Securities_.Fair Market Value}, {Securities_.Deal})/TotSecCE*100

-LB

 

[Crystalyzer]

LB

Once again you have come through for me despite my feable explanations of my issues. I was surpressing the subreports and headers and therefore getting zero for the shared variables. Once I changed things around a bit your suggestion works like a charm!

Thanks again

Thanks and best regards,
-Lloyd