Alpha Video Training
Results 1 to 11 of 11

Thread: counts and summaries

  1. #1
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default counts and summaries

    As it is in life, the closer you get to the window at the end of the hall, the more you can see of the world of Alpha 5 around you.....

    I think that I am correct, (quietly hoping I am not), in the following premise:

    tablecount(), tablesum(), dbcount() and dbsum() ALWAYS and ONLY glean data from the ENTIRE TABLE. Separating some of the data into a query subset, and then using these functions would result in the same data as obtained from the entire table.

    Only Total() and Count() can select data from subsets of the entire table...

    Which would mean that the first group of functions would be used ONLY for a YTD report or summary and the second group would HAVE TO BE USED for summarizing weekly, monthly, quarterly, etc -- or any other subset of the data in the table.

    This 'gem of information' is what I am able to understand from the chm..

    I'm sure that many of you are aware of how painfully slow Total() and Count() can be for a report containing a large number of variables from a large table.

    Thanx for any comments
    D

  2. #2
    "Certified" Alphaholic
    Real Name
    Peter Wayne
    Join Date
    Apr 2000
    Posts
    1,728

    Default Re: counts and summaries

    Dik,
    Not sure what you mean by this:

    "tablecount(), tablesum(), dbcount() and dbsum() ALWAYS and ONLY glean data from the ENTIRE TABLE"

    These functions all take a filter (or an index with a key) and therefore only work on records that satisfy the filter. If you have appropriate indexes, they should work quickly enough. No?

    - Peter

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

    Default Re: counts and summaries

    I look at it a different way.

    tablecount(), tablesum(), dbcount() and dbsum() ALWAYS and ONLY glean data from the table specified according to the filter you specify. No outside influences are considered.

    Total() and Count() can select data from subsets of the entire table, only in the current context...


    If that subset is actually the whole table you get the grand total. If that subset is limited by the report's filter, you get a limited value return. You can't tell total() what records to consider.

    I often use both total() and tablesum() in a report. Suppose you are reporting on some data covering a month and limit the report to a month's records. Total() will give you the total for the month. Tablesum() can give you the total for the table, total for a quarter, total for a year. total for a salesperson, etc.

  4. #4
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default Re: counts and summaries

    Even when I think I am very specific, apparently I am not.

    The 'report' that is being generated is actually a 'form'. Client prefers a more visual representation than I (at least at this time) am able to generate with the report writer.

    Sooooo
    Form is sectioned into coherent, connected variables and then displayed. The form includes totals that are obtained from variables or calculated fields that are present on the form or obtained from data in the tables (both parent and child tables are used for data collection)..........

    Since I have solved my problems with the data presentation, I do not wish to get into zipping an example. I was just trying to get a better understanding of the named functions.

    However, it seems that I have been unable to properly filter the first group of functions in order to meet my needs, so when that occurred, I used the second group of functions. In some cases that required adding fields to the table, but resulted in an acceptable result.

    What I read from your responses is that I need more education in describing and creating filters. Most of my problems occurred when I needed to filter by more than one attribute AND only total() and count() allow that -- as far as I can determine. EG the report needs to display: the number of transactions that contain a group (some may contain more than one group) **** NOTE the word group just means a type of transaction **** then it must also display (for each group) the sales units, the sales dollars (both taxable and non-taxable and the sales tax collected)

    In short summary: some data from only the parent record; some data from only the child records, some data from the child records based on the parent record.

    I'm sure that there are those out there who are smiling now because they've been here. Also sure that I may smile later -- just not right now....
    D

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

    Default Re: counts and summaries

    Total() and count() accept no filtering. Outside of a report where you can create groups, the only way to modify these functions is to use the group parameter specifying

    GRAND (all records)
    The name of the current table
    The name of a table in the current set

    If you have some time, take a look at my attachment in this thread. It illustrates some usages of tablesum(), etc which I do not believe are possible with total(), etc.

    Additionally, if you are not aware, total() and its cousins are limited outside of the report writer context although they do have some capabilities in forms, letters, etc. From the webhelp for total()........

    Limitations

    This function is a report writer function, not intended for table level field rules or other expressions. While the function may perform in some areas outside of the report writer, its use there is not supported.

  6. #6
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default Re: counts and summaries

    Ok - Stan, thanx for the example...
    Not sure I understand just yet, but I'm working on it.

    To be as specific as I can -- In a table containing several numeric fields like charge, non-taxable, taxable, tax --
    I need to count the # of records that have a field (like non-taxable) with a value greater than 0. I really need to use the table....() and db....() functions because they are much quicker and I need to get these counts for 13 different categories, each with 4 to 5 fields to summarize.
    I am not able to determine 'the filter' to do that job. Everything I try gives me some error - most times it's 'too many parameters'.
    In an earlier version, I used total() and it did the job but took 5 or 6+ minutes to generate the data on the form.

    I may have more 'hangups' later, but this is the 'squeaky wheel' at the moment. If you can point me in the right direction, it would be appreciated.

    D

  7. #7
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default Re: counts and summaries

    Just spent a bit of time with my buddy (the chm)....
    Anyone care to tell me why the following codes do not give the same result?

    Code:
    filter="company = "+quote(thename)
    ?filter
    = company = "Dick Coleman"
    Code:
    xx="company = "+quote(thename)
    ?xx=.F.
    AND why is this filter expression for an index "month(inv_date)=1" is accepted but this filter expression for a field "tblfieldname>0" generates too many parameters as an error

    And please don't start the explanation with 'Everyone knows........"

    thanx
    D
    Last edited by dik_coleman; 01-10-2008 at 02:03 AM. Reason: added an example

  8. #8
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,307

    Default Re: counts and summaries

    Code:
    dim thename as C
    thename = "Dick Coleman"
    dim xx as C
    xx = "Company = " + quote(thename)
    ?xx
    = Company = "Dick Coleman"
    
    filter = "Company = " + quote(thename)
    ?filter
    = Company = "Dick Coleman"
    D, the code you present should return the same result assuming the antecedent conditions were the same. They were different. Can't tell you what it might be because you haven't shown us the entire script or given us the context in which the scripts were running.

    D, we'll be able to see the stumbling block and will more likely be able to offer specific suggestions if you furnish us a small data set to work with. May I suggest that we try to walk before running? How about preparing an example that handles one category and only a couple of fields to summarize? Explain what you want the form to show, and then zip the sample for posting here.
    Last edited by Tom Cone Jr; 01-10-2008 at 06:27 AM.

  9. #9
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,307

    Default Re: counts and summaries

    D, in the absence of a data set from you, maybe a couple of easy examples from AlphaSports will help.

    Open the Invoice form in design mode, create two new calculated values as follows:
    Code:
    pr_gt_20 = tablecount("invoice_items","Invoice_Items->Invoice_Number = "+quote(Invoice_Number)+" .and. Invoice_Items->Price > 20.00")
    
    q_lt_2 = tablecount("invoice_items","Invoice_items->Invoice_number = "+quote(Invoice_number)+" .and. Invoice_items->Invoice_Items->Quantity < 2")
    Then drop them on the form. Expand the format property of the display field objects to 4 characters, since by default this will be set to only 1 char wide.

    The first calc value (pr_gt_20) will display the number of items in the current invoice where the Price is Greater Than 20.

    The second calc value (q_lt_2) will display the number of items in the current invoice where the Quantity is less than 2.

    Hope this helps. -- tom
    Last edited by Tom Cone Jr; 01-10-2008 at 07:47 AM.

  10. #10
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default Re: counts and summaries

    Just got a back.... will look at your example and edit this to reply
    D

    OK Tom -- it works, but that is not the situation that needs to be resolved.
    In Alpha Sports, I would need to count all the invoices that contain "Alpha Sports Cap", I also need (for those invoices only) the total number of units sold and the total sales $.

    In my app, I have 13 categories some are taxed, some are not, some have both tax and no tax field values. The only reference to the child 'category' in the parent record is a flag - used for counting the parent. For each category, I need the total number of parent records that have children with 'that category' in the child record..... then the total units and $ taxable, non-taxable and the am't of the sales tax collected. It gets crazy trying to create a filter that can be used to reference both the parent and the child.

    by the way, check your q_lt_2 formula, you have dislexic fingers
    Last edited by dik_coleman; 01-13-2008 at 11:18 PM. Reason: complete answer

  11. #11
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,307

    Default Re: counts and summaries

    D, welcome back.

    It's clear I didn't understand what you're struggling with. Even now it's still very cloudy. "Categories" is not a term that is used in Alpha terminology so I was guessing...

    Using AlphaSports model, if you included a customer identifier in each invoice_items table record you'd be able to easily count specific items and total sales $ for each customer. You can generate count and totals for specific items (tablewide, i.e. for all customers) already. The tradeoff is a bit of redundancy in each items table record.

    An alternative would be to use an inverted set and a custom script that steps through the records accumulating various counts and totals as it goes.

    -- tom

Similar Threads

  1. Summaries
    By Mike Reed in forum Alpha Five Version 1
    Replies: 4
    Last Post: 11-09-2009, 01:32 PM
  2. Purging unused calculations and summaries
    By kumargroup in forum Alpha Five Version 5
    Replies: 2
    Last Post: 07-01-2003, 10:39 AM
  3. New Page Summaries
    By BRochford in forum Alpha Five Version 5
    Replies: 8
    Last Post: 08-31-2002, 02:56 AM
  4. Percent of Counts in Reports
    By Chris.Tanti in forum Alpha Five Version 4
    Replies: 14
    Last Post: 06-15-2000, 11:23 AM
  5. Conditional Counts
    By Jim VanGelder in forum Alpha Five Version 4
    Replies: 2
    Last Post: 05-17-2000, 04:34 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
  •