# Crystal Reports chart percentage issues

Discussion in 'Business Applications' started by gurutech, Nov 12, 2010.

Not open for further replies.

Joined:
Apr 23, 2004
Messages:
2,960
I'm trying to generate a chart in Crystal Reports (version XI), and I can't get it to show the correct percentages.

I'm pulling the data from an Excel file and have created formulas where:
• One value is either "Yes" or "No"
• Second Value is either >= 70 or <70
• If the two values are "Yes" and >= 70, then it is a "Pass"
• If first is "Yes" and second is < 70, then it is "Fail"
• If first value is "No" it is not included in "pass" or "fail"

I have one formula set up to determine the first value (either yes or no), another to determine if it passes, and a 3rd to determine if it fails.

What I am trying to do is show the percent of pass and the percent of fail on a single line chart. Both values added together should equal 100% (since only "yes" records are included. "No" records are discarded.)

This should be divided out "weekly", but when I create the chart, it is showing me the same values for all 3 weeks of data I am testing with, which is 45% pass, 55% fail. But if I run the calculations manually, I get 50/50 for week 1, 42/58 for week 2, and 40/60 for week 3.

How can I get Crystal to show the correct numbers????

2. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
It sounds like you are getting averages (rounded off) and that is getting applied to all the weeks, rather than each week's data being calculated. But without seeing your report design (and probably not even then) I can't guess at much more.

Joined:
Apr 23, 2004
Messages:
2,960
Here are the formulas, each one is a single line. The first ( ) is the formula name, then the formula, then // and comments

(Is it) if {Sheet1_.Is this a Service Emergency?} = "Yes" then 1 //If Excel column contains "Yes" for each record, assign it the value of 1)

(Pass) if {Sheet1_.Overall Grade} >= 70 then 1 //If score is 70 or above, then assign it value of 1

(Fail) if ({Sheet1_.Is this a Service Emergency?} = "Yes" AND {Sheet1_.Overall Grade} < 70) then 1 //if (Is It) = "Yes" and score is less than 70, then assign value 1.

I then use a PctPass and PctFail formula to calculate the percent of each:

sum ({@Pass}) % (sum ({@Pass}) + ({@Fail}))
or
sum ({@Fail}) % (sum ({@Pass}) + ({@Fail}))

The chart is divided per week, as are the other charts, which do show the correct data, but this is the only chart that shows a percent. The other charts show a count of Pass/Fail, and the number of Emergencies or Non-Emergencies.

Joined:
Mar 27, 2008
Messages:
6,550
First Name:
Hans
All I see is a count', at leas based upon your formula

For each week I suggest: (the count of either fail or pass divided by the total count of fail and pass

sum ({@Pass}) / (sum ({@Pass}) + ({@Fail}))

sum ({@Fail}) / (sum ({@Pass}) + ({@Fail}))

Depending how you formatted the output it will show either 0.97 as value or 97% in %

Joined:
Apr 23, 2004
Messages:
2,960
I've tried that several times, but any time I have "sum({@Pass} + {@Fail})", it tells me that I need a field inside the ( ), which I have.

Joined:
Mar 27, 2008
Messages:
6,550
First Name:
Hans
I don't know what the exact syntax is for varaible, in MS Access it's [fieldname]
Another reason could be that the values in the fields are not seen as numerical, maybe convert them to numerical values?
What dos the value {@Pass} or {@Fail} show when put by themselves?

Joined:
Apr 23, 2004
Messages:
2,960
Each field will show either a 1 or a 0. For "pass", if the score is 70 or higher, it gives it a 1, if it is less than 70, it is 0. For the "fail", if it is less than 70, it is 1, if it is 70 or higher, it is 0.

Joined:
Mar 27, 2008
Messages:
6,550
First Name:
Hans
Wht if you leave out the sum and just add th fields as you would in Excel:

{@pass} + {@fail}

Joined:
Apr 23, 2004
Messages:
2,960
Divide by 0. <sigh>

I was able to do this with a pie chart, but my boss doesn't want that, he wants to show "trending" by week, which can't be done with a pie chart, as far as I know...

10. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
(sum ({@Pass}) + sum({@Fail}))
?

Joined:
Apr 23, 2004
Messages:
2,960
Tried that too!

12. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
(sum ({@Pass}) or only sum ({@Pass}) - either of those give a value? Obviously we need to know how to write the basic and then we can add several....

Joined:
Apr 23, 2004
Messages:
2,960
Values are either 1 or 0 for each record, hence the reason I am trying to "sum" them....

14. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Since you seem to be linked to an Excel sheet from which you are drawing the values, why don't you just do the calculations in Excel first (should be some simple IF formulas) and then draw in those calculated values?

As Seen On