Help - Inserting Formula into a chart 1

[TheShookster]

Hello,
I have a report than ot its x axis I hace certain tests, and on the y-axis I need to display the % (which is a formula field defined by me).
The problem is the Chart Expert does not show formula fields, therefore you cannot implement it on the chart.

I have tried to create seperate command on the database that will give the same value of the formula, but it seems to crash Crystal Reports.

 

[lbass]

Please show both the contents of both the formula and the command you tried, and also tell us a little more about the chart.

-LB

 

[TheShookster]

Thank you for the quick response,
the formula is the following:

{#numOfTests}/{TESTCAPACITYPLANNER.CAPACITY}*100

#numOfTests - is actually a count on the testcode field and is a Running Total Field.

The command I have tried is the following:

select Count(ORDTASK.testcode)/max(capacity)*100 as percent
from ORDTASK,TESTCAPACITYPLANNER
where ORDTASK.DEPT= TESTCAPACITYPLANNER.DEPT and ORDTASK.TESTCODE= TESTCAPACITYPLANNER.TESTCODE
and TS='Logged' and ORDTASK.DEPT='LAB1'
group by ordtask.testcode

I have tested the command on my database and it is fine, however - it is crashing Crystal whenever I add the "percent" field from the command to the report.

 

[TheShookster]

The chart has 2 columns:
1) Scheduled tests - which is the running total field #numOfTests - for that I had to use "Group By" testcode.
2) capacity - which has one value per testcode.

As far as the rows:
As written above, display the information per testcode.

 

[lbass]

I just did a test on your command and found that I could not use the word "percent" since it is a reserved word. When I changed it to "pc" the command worked fine. I think you would need to add the testcode field into the select part of the command, and use that to link to the table.

-LB

 

[TheShookster]

I have implemented your suggestions but with no success.
When I run the command on my database it is working just fine.
However, when I am trying to insert the pc field (from command) to the Group Footer, Crystal is crashing.
Is there any other way to insert a formula to a chart?

 

[lbass]

The issue is that in your original formula you are using a running total, which requires a different approach. But, if the following formula gives you the correct result, you can chart on this:

Count({ordtask.testcode},{ordtask.testcode})%{testcapacityplanner.capacity}

Add this as your summary field in the chart (do not summarize), and use test code as your "on change of" field.

-LB

 

[TheShookster]

In the chart expert, you can either use field from a table, Total Running field or command.
It does not let you edit or insert a formula.
In that case, how can I edit your recommendation (below) to the chart?

Count({ordtask.testcode},{ordtask.testcode})%{testcapacityplanner.capacity}

Thank You for you help,

The Shookster

 

[lbass]

Create the formula in the formula expert and then it will appear for use in the chart expert.

-LB

 

[TheShookster]

Thank you for your response,
I already had the formula before posting this thread.
The problem is that the chart expert in CR 10 does not let us use formula fields in it.

The Shookster

 

[lbass]

The original formula you showed used a running total. The one I suggested doesn't. You might be right--I don't have CR 10, but it does show up in CR XI and can be used for charting, as I tested this.

Even in earlier versions, you could use a formula, though not one's which used summaries.

If you can't do this in CR 10, then you might be able to use the method described in "Charting on Print-time Formulas" which can be found in the Knowledge Base on the Business Objects site.

-LB

 

[TheShookster]

LB - You are the man.
Thank you!