Alpha Video Training
Results 1 to 15 of 15

Thread: Percent of Counts in Reports

  1. #1
    Member Chris.Tanti's Avatar
    Real Name
    Chris Tanti
    Join Date
    May 2000
    Location
    Cardiff, South Wales, UK
    Posts
    368

    Default Percent of Counts in Reports

    This should be quite simple but I am having trouble recreating a simple percentage value in A5 reports.

    In Alpha 4, you could get a count of a field then create a percentage by setting a calculated field like this:
    (SUMMARY->field3_GC/RECCOUNT())*100
    This worked great, as long as the report was based on the entire table, hence reccount().

    If I try a similar thing in Alpha 5
    (val(Calc->Cnt_Field3_For_Field3)/reccount())*100

    The report result just shows the "name" of the calculated percent field rather than the result!

    Is there a nother method I should be using, am I doing something daft.

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Percent of Counts in Reports

    Alpoha displays the name of the field on a report, or any layout, if it can't understand how to perform the calculation.

    Yours should be

    (val(Calc->Cnt_Field3_For_Field3)/count(field3,GRP->Grand))*100

  3. #3
    Member Chris.Tanti's Avatar
    Real Name
    Chris Tanti
    Join Date
    May 2000
    Location
    Cardiff, South Wales, UK
    Posts
    368

    Default RE: Percent of Counts in Reports

    Stan,

    Ime afraid this does not work for me,

    The count field is a character string and does not want to be converted to a numeric! If I use val I get an incorect argument type.

    If I just creat grp->grand by itself in a field and try and convert that to a number that does not work either.

    This is a pain!!

  4. #4
    Member
    Real Name
    Richard Drabik
    Join Date
    Apr 2000
    Posts
    136

    Default RE: Percent of Counts in Reports

    Chris,

    I suspect the problem lies in what you are trying to "Count". Normally, A5 distinguishes very well as to whether the result of a calculated field on a report is numeric or character.

    Are you just counting a field or the result of an expression involving that field?
    For instance, "count" should work just fine with each of these expressions

    Field3""
    Field3>0
    Field3=.T.

    depending on whether the field is character, numeric, or logical. "Count" seems to need the result of a conditional expression like Field3"", counting those records that evaluate to "True".

    Are you using the genie that pops up when you drag a onto your report? This is much more foolproof than trying to directly type in a calc field expression using "Count" or "Total". I have to create calculated fields for percentages all the time. I create the numerator and denominator as separate calc fields via the genie. After you drag the on the report and create the numerator or whatever, you can delete it from the report section to which you dragged it. It will still remain as a calculated field unless you delete it from the Calc dialog window itself. Once the numerator and denominator calculated fields are created, you can either

    1. create a third calc field in the Calc dialog window that derives the percentage as you did in Alpha Four, or

    2. take the expressions for your numerator and denominator and combine them in a third calc field that derives the percentage, then delete the original numerator and denominator calc fields.

    I usually do number 1 since it makes it easier for me to track what the calc fields are doing later, despite the fact that it gives me more calc fields than necessary. A5 never seems to mind doing hundreds of calculations, although with a lot of records, the report will not be instantaneous.

    If you haven't relied on the genie, you may want to give it a try.

  5. #5
    Selig Starr
    Guest

    Default RE: Percent of Counts in Reports

    I am not sure this is what yiu want but here it is.

    1. Set up a numeric field say N
    2. Assign 0 to it as a default.
    3. Create and save an update operation that changes
    all values N to 0
    4. Using a Xbasic script loop through records changing N
    to 1 based on some selection criteria.
    5. Create a global variable, say TOT
    6. Set TOt = to thr total of N (I am not sure what the total() is)
    7. Loop through again to compute your percentages based on TOT
    but only for records where N=1. The other percentages can
    be set to zero.

    Instead of 6. it may be simpler to compute TOT as you loop
    thru the records, ie TOT=TOT+1

    Caveats: You may want to creste a temp table, say TEMP, and
    append to it the records from your table where N=1.
    Then you can compute your percentages in that table and
    do your reports from TEMP

    This looks more complicated than it is.
    In any events, it may give you some ideas.
    The key here is to creste the field N and use the total()
    function instead of the count() function.

  6. #6
    Member Chris.Tanti's Avatar
    Real Name
    Chris Tanti
    Join Date
    May 2000
    Location
    Cardiff, South Wales, UK
    Posts
    368

    Default RE: Percent of Counts in Reports

    Wow,

    I am getting the impression it would be a good idea for A5v5 to take a few pointers from A4 when it comes to percentage counting. It was a two second excercise in Alpha 4!!.

  7. #7
    Selig Starr
    Guest

    Default RE: Percent of Counts in Reports

    On the other hand, Alpha5 is a far superior
    product. There is a learning curve but well
    worth it. If you zip up your app, including
    a description of what percntages you are
    trying to get and what kind of report(s)you
    need, someone will help you solve your problem
    and even give the Xbasic code (if necessary)

  8. #8
    Member Chris.Tanti's Avatar
    Real Name
    Chris Tanti
    Join Date
    May 2000
    Location
    Cardiff, South Wales, UK
    Posts
    368

    Default RE: Percent of Counts in Reports

    Thanks for the offer Selig,

    Its not that I am critisising A5, I think Its great and I ejoy creating apps with it. It just seems like a shame that a previous (infereior!) product could do something so simply and this was lost in the upgrade.

    I would still like to know why I cant change a count field that is a character type to a numeric type with val.

    This is all I need to do.

  9. #9
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Percent of Counts in Reports

    If the val() function returns an "incorrect argument type" error, then most likely the calc field is already a number.

    I assumed that your original expression was correct except for the "grouping" and thus the

    count(field3,GRP->Grand)

    that I suggested.


    Now I realize that

    val(Calc->Cnt_Field3_For_Field3)

    is like evaluating

    val(9), doesn't make sense.

    Sorry I didn't see that earlier.

  10. #10
    Member Chris.Tanti's Avatar
    Real Name
    Chris Tanti
    Join Date
    May 2000
    Location
    Cardiff, South Wales, UK
    Posts
    368

    Default RE: Percent of Counts in Reports

    Nope, sorry Stan,

    I am getting a count of a text based field, field3 as an example. The report has its grouping level set to field3, and on the footer of this group I drag field3 and genie it as a count (the count of the text field). If you look at the properties of the count field you will see that it is text based, you can only do case conversion on it, not the normal numeric formating like specifing whether its a curency field etc.

    Now if you go to the calculated field options and say add 100 to the count expression and evaluate it, it seems to be correct, implying that its a numeric field. Run the report and it comes up with and incorect argument error. Implying that its a character field.

    If you go to the calcualted field options and add a character string to the count expression then try and evaluate it it comes up with an error imeditally.

    What is the calcualted count, it can't seem to make its mind up whether its numeric or character based. If you used the genie to create a total of a numeric field this is correctly created as a numeric field and you can do all the usual numeric type functions.

    You should be able to do the same with counts.

    Or is it just me.

  11. #11
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Percent of Counts in Reports

    I can't duplicate your results. When I drag any field onto a group or report footer or header, specify "count", and right click, properties, in the dialog box, format, it defaults to right, top alignment, no wrap, width 8, decimals 0, "none" for style.

    Experimented with numeric, character, and logical fields. Is this the way you are approaching the problem?

  12. #12
    Member Chris.Tanti's Avatar
    Real Name
    Chris Tanti
    Join Date
    May 2000
    Location
    Cardiff, South Wales, UK
    Posts
    368

    Default RE: Percent of Counts in Reports

    Stan,

    Ok, now drop down the format style, were it says none, you should find the only options you have are all case based
    ie All Uppercase, All Lowercase etc.

  13. #13
    Member Chris.Tanti's Avatar
    Real Name
    Chris Tanti
    Join Date
    May 2000
    Location
    Cardiff, South Wales, UK
    Posts
    368

    Default RE: Percent of Counts in Reports

    Stan,

    Basically the "problem" occurs when you try and count a character based field in a group. The resulting calculated field is in a half way house, neither character nor numeric. If the same is tried, counting a numeric field, then the resulting calculated field is fully numeric.

    Doesnt any one else think this is odd!!!

  14. #14
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Percent of Counts in Reports

    Sorry, mine has the normal numeric formats with () $ etc, no matter whether the original field being counted is character, numeric, or logical, and the 'decimals shouldn't show 2 for a character field. I'll keep trying to see what I can find.

  15. #15
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Percent of Counts in Reports

    Still stumped. Whatever field or calculated field you drop into a footer and specify count, the resulting calc field should be numeric. The alternative defies understanding.

    Could you
    1. open your report in design mode
    2. from the drag drop list, select your "field3"
    3. drop it into a footer
    4. specify "count"
    5. right click on the new field in the footer
    6. copy the "expression" that appears in the dialog
    7. post it as a reply here?

    I feel we must be looking at two different things and thinking we're looking at one.

    Thanks,

    Stan

Similar Threads

  1. How to display a field as a percent
    By David Francis in forum Alpha Five Version 4
    Replies: 3
    Last Post: 08-12-2004, 06:49 AM
  2. percent field format
    By bruce in forum Alpha Five Version 5
    Replies: 0
    Last Post: 12-04-2003, 10:00 PM
  3. statusbar.percent()
    By Jeff Moses in forum Alpha Five Version 4
    Replies: 7
    Last Post: 04-04-2001, 10:23 AM
  4. Conditional Counts
    By Jim VanGelder in forum Alpha Five Version 4
    Replies: 2
    Last Post: 05-17-2000, 05:34 AM
  5. Printing group counts in order
    By Pete Cantele in forum Alpha Five Version 4
    Replies: 2
    Last Post: 04-13-2000, 10:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •