counts and summaries

[dik_coleman]

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

[Peter.Wayne]

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

[Stan Mathews]

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.

[dik_coleman]

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

[Stan Mathews]

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.

[dik_coleman]

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

[dik_coleman]

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?

filter="company = "+quote(thename)
?filter
= company = "Dick Coleman"

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

[Tom Cone Jr]

Re: counts and summaries

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.

[Tom Cone Jr]

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:

[COLOR="Red"]pr[/COLOR]_[COLOR="Red"]gt[/COLOR]_20 = tablecount("invoice_items","Invoice_Items->Invoice_Number = "+quote(Invoice_Number)+" .and. Invoice_Items->Price > 20.00")

[COLOR="Red"]q[/COLOR]_[COLOR="Red"]lt[/COLOR]_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

[dik_coleman]

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

[Tom Cone Jr]

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