Filtering a Report on Calc Fields

[Doug Briggs]

(Sorry if this is hashed out elsewhere. I couldn't find it with a search. . . .)

Is there any way in a report to filter records based on a calculated field.

I have a set with bio data in the parent table, loans in one child, and loan payments in another. The set has a few calc fields which total the loan amounts and payment amounts (among other things) and displays them correctly on the data entry forms which the users, um, use.

They want a report showing people with outstanding balances (ie, no entries where the loan is fully paid off), but whenever I specify that calc field in the report filter, I get no records (the calculation doesn't appear to be made properly before the filter is applied), even though I know many records satisfy the criteria.

And yes, I've checked for errors in typing and defining; there don't appear to be any.

Isn't this what multi-pass reporting was all about? Or was that just an A4 thing?

Any help appreciated!

Doug

[whanigsberg]

RE: Filtering a Report on Calc Fields

Doug,

It sounds to me like the calculation is defined in the report. If so, you cannot filter on it as the calculation only becomes available as the record is fetched.

But if you were to create a temp table and populate it as part of the print process you could store the balances in a real field which you could filter on. Base the report on the temp table.

And yes the multipass report is an A4 thing.

Bill

[Doug Briggs]

RE: Filtering a Report on Calc Fields

Bill,

Thanks for the reply. Actually, the calc fields were defined at the set level as global calculated fields (so they'd be available on forms and reports without rekeying). I guess it's really a limitation of the report writer that they can;t be used for filtering.

Doug

[Tom Cone Jr]

RE: Filtering a Report on Calc Fields

Doug, you might post a working model of your database here for us to examine. Maybe we can see something you're missing. At worst we can confirm that the behavior you're seeing is also happening for us. -- tom

[Tom Cone Jr]

RE: Filtering a Report on Calc Fields

Doug,

Here's an easy example that disproves your thesis.

Unzip the attachment to an empty folder.

You'll find a simple database with one table and one report.

The report is filtered to display only records where the value in the 'Extended' field is greater than or equal to 1,000

The 'field type' for the 'Extended' field is defined in field rules as Calculated, using a simple expression:

Unit_price * Qty

Alpha Five has no trouble filtering the report using values stored in the table in the Extended field.

-- tom

[Tom Cone Jr]

RE: Filtering a Report on Calc Fields

Doug,

You use the phrase 'global Calculated field'. This suggests to me that you've defined a calc field for the table and given it a global scope. If so, the value stored in this 'field' is not stored with each record in your table.

Calling it a 'field' is a bit of a misnomer. It's more useful to think of them as special types of variables that exist in RAM and are automatically recomputed whenever you fetch a new record. They can be real handy, but you can't use them to sort or filter reports. Why? Well, they aren't stored in the table, so the report engine can't 'see' them all at one time. If they cannot all be seen then they can't be sorted or filtered. The one 'field' exists in memory and is recalc'd each time a new record is fetched. As soon as this happens Alpha Five can't remember what the last value was, and has no way to know what the next value will be. So filtering or sorting is impossible.

-- tom

[Doug Briggs]

RE: Filtering a Report on Calc Fields

Tom,

Thanks for the replies. Per your example "fordoug.adb", it isn't the same, in that my calc fields are defined in the set but calculating on child table fields, not fields from the parent (which is in effect what your sample does).

I use the phrase "global Calculated field" because that is the terminology employed by Alpha, a program I have been singularly devoted to for lo these many years. Since the calculations occur properly when each record is fetched (as in a data entry form), I had hoped that there was some way that they could occur in the process of a report, as in A4 days.

This is not a slam against the program, just an acknowledgment that a certain piece of desirable functionality isn't there (yet?).

Doug

[Tom Cone Jr]

RE: Filtering a Report on Calc Fields

Doug,

I understand. The purpose of my example was to illustrate an alternative approach. I'm not surprised you find it different from your own. That was sort of the point.

The fact that my example filtered a field in the primary table makes little difference. If the calculated value is stored in the child table a cross-level query will return the desired records in the report.

I did not mean my description of the differences between fields that are defined in field rules to be of type 'calculated' and 'calculated fields' defined in the structure of a table, or in the layout of a form, to be critical. Was just pointing out something that others have found confusing from time to time. The terms are virtually identical, and in popular use they 'ARE' used interchangeably. But, there are big differences.

Anyway, mind telling me (mechanically) how you went about defining the global calculated field ?

-- tom

[Doug Briggs]

RE: Filtering a Report on Calc Fields

Tom,

Now I know why this thread died. The ball was in my court!

No criticism was taken, thanks. In the few databases I've set up in A5 the last couple years, I don't think I've used "calculated" field types (in the field rules) very much. I guess I've used it where calculations between constants is all that's needed (someone's age, for instance, based on a constant birthdate subtracted from todays date, etc.).

On the other hand, I used global calculated fields for keeping running calculations of child data in sets, which is what I did with this sorry Loans database (I'll attach a skeleton with this). I know they can be defined at the form, report, and label level, but I tend to define them globally so they can be reused in all these areas without recreating them for each layout.

When you ask how I defined it, my answer is I 1) edited the set and 2) defined the global calc fields using the menu option under the "set" menu choice. Is that what you mean by the mechanics? Otherwise, you can see the byzantine calcs that are going on for yourself (the set is "student loans").

Anyway, here's the database (please don't laugh at the mess). You'll see the calc fields work on the main form ("loans1"), but not if you want to base a report filter on them. The report that shows this best is "inactive outstanding"; it currently filters out inactive loans, but if you add the condition that it also omit "calc->Loan_Outstanding>0", it gives an error.

Am I right in thinking this was a function of the "multi-pass report writer"? In effect, the report writer would do the calculations first (which you rightly indicate do not otherwise store any value) in one pass, and then print the report with wnother pass. Is this something that can be added in future versions of the program. I know it'd make my life easier. . . . (Alpha users are spolied that way - always looking for an easier solution.)

Thanks for your kind attention to my conundrum.

Doug