How do I make a Formula Available to a Chart in CR11.0?

[Former Member]

Hi all.  I have Crystal 11.0.  There are some report examples that I installed when I installed CR11.0.  One sample report is with Charts.  I opened up the sample to confirm something.  I'm more confused than before I viewed the sample!    Specifically, I'm talking about the Gantt chart's usage of a computed formula field.

From my various "Googles" for how to do a Pareto Chart, I saw recommendations that I needed to do the chart inside a subreport because I want to pass in a formula that does a percentage computation.  The instructions were to pass in the formula as a subreport link and then the chart would "see" the formula because it was now a parameter.

I opened the chart example to see how it was done.  The charts were in group headers.  When I viewed Gantt chart in the chart expert, I saw all the formulas listed alongside the fields from the "Customers" and "orders" tables.

So I tried to emulate this.  I placed my formula in the details section and then suppressed printing of it.  I inserted a chart directly into the group header as I saw in the sample.  In the chart expert, my formula is not visible in the list of fields.  The sample report has formulas.  Why does my custom report not also have access to my own formulas within the chart expert?  How do I get my own report to behave like the sample chart report?

[abhilash_kumar · Accepted solution]

Hi Debbie,

The difference could be the functions you've used in the formula on your report.

Could you paste the code you've used please?

-Abhilash

[Former Member · Accepted solution]

Hi.  I have two formulas.  One I've called ZeroOut.  It is on my group header.  The content of that formula is :

shared currencyvar curCounter:= 0;

The percentage formula is defined like this:

shared CurrencyVar curCounter; 

Shared CurrencyVar curTotal; 

curCounter := curCounter + {Command.TOTALCOST};

if {Command.TOTALCOST} <> 0.0 then

curTotal := (curCounter/Sum ({Command.TOTALCOST}))*100

else

curTotal:= 0.0;

ToNumber(curTotal); 

It currently is on the detail section line as shown below.

I dropped a chart into the Group footer.  The chart expert opened like this:

Now, contrast that with one of the sample charts.

Thanks!

[abhilash_kumar · Accepted solution]

Hi Debbie,

Formulas with shared variables cannot be used with Charts.

You'd need a Subreport to be able to chart off of this formula.

Here's a sample doc that explains the steps.

-Abhilash

[Former Member · Accepted solution]

Ok.  So shared variables can't be used.  How would I format my formula so it doesn't need a shared variable?

This is what I'm trying to accomplish.  I have a commandtext.  The SQL inside it looks like this:

select @grandtotal = sum(totalcost) from @temp

SELECT *, @grandtotal as grandtotal  FROM @temp temp

join

(

select equipmentno, sum(totalcost) as grptotal from @temp

group by equipmentno

)sumtotal on sumtotal.EQUIPMENTNO = temp.EQUIPMENTNO

ORDER BY equipmentno

So I have a final grand total.  On each group break I want to compute a percentage.  Something like (grptotal \ grandtotal) * 100.  Is it possible to do this without the use of a shared variable?

[abhilash_kumar · Accepted solution]

OK. SO all the summaries are being calculated in the SQL Query, aren't they?

You can create a formula that looks like this:

Sum({grptotal}, {Group_field}) / {GrandTotal}

Insert a Chart and use this field as the 'Show Values'. Next, go over to the Options tab > Check 'Show Value' > Select 100%

-Abhilash

[Former Member · Accepted solution]

You're a genius, Abhilash.  Thanks so much!  I don't know why I couldn't see that.  You made my life so much easier!  Since I already have those fields in the Query, I'm just adding the percentage into the query too.  Duh!  Man, I'm kicking myself for wasting so much time on Crystal formulas.