1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Crystal Reports chart percentage issues

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

Thread Status:
Not open for further replies.
Advertisement
  1. gurutech

    gurutech Thread Starter

    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

    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.
     
  3. gurutech

    gurutech Thread Starter

    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.
     
  4. Keebellah

    Keebellah Trusted Advisor

    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 %
     
  5. gurutech

    gurutech Thread Starter

    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.
     
  6. Keebellah

    Keebellah Trusted Advisor

    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?
     
  7. gurutech

    gurutech Thread Starter

    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.
     
  8. Keebellah

    Keebellah Trusted Advisor

    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}
     
  9. gurutech

    gurutech Thread Starter

    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

    slurpee55

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

    gurutech Thread Starter

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

    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....
     
  13. gurutech

    gurutech Thread Starter

    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

    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?
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/962000

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice