Subreport - Filter expression not logical

[EdTeves]

It's my first time to use A5v9, am using registered full copy already. I am trying to make a subreport as footer of a simple main report on HMO Member Payments, to get (a) count of members listed in report, per HMO Category (b) total of premiums paid, per HMO Category.

The basic Drag-and-drop options don't enable me to input the expressions that would accomplish these (as illustrated in Help files). So I am trying the subreport approach (referring to Help and internet samples as well). But after inputting expression like "Total(Table1->Premium,Grp->Category,Grp->CategoryCount:detail)", I always get "Filter expression not logical". Same result if I try the "Run_Total" function.

Is it a matter of syntax or am I using the wrong approach?

[Tom Cone Jr]

Re: Subreport - Filter expression not logical

Tell us more about where the data is stored. For example, is the report based on a single table? A set of tables? Is the data you wish to accumulate found in a single table?

First impression here is that you would not use a subreport for this. They are intended primarily to display records from one-to-many child tables in cases where you want to display records from more than one such child table in adjacent columns.

The message board supports file uploads, so why not post a zipped copy of the database for us to use. Include directions to the report in question if more than one is already defined in your database. To get to the "manage attachments" feature of the message board click the "Go Advanced" button, while editing a reply.

[Stan Mathews]

Re: Subreport - Filter expression not logical

With or without the outside quotes, quotes not proper in a report filter,

"Total(Table1->Premium,Grp->Category,Grp->CategoryCount:detail)",

does not yield a logical result (should be a numeric value) so I don't see how that can be used as a filter.

You might be thinking of something like

"Total(Table1->Premium,Grp->Category,Grp->CategoryCount:detail) > 10" which would, if everything else is ok, have a logical result.

Alpha doesn't precalculate such totals in a report so I don't believe you can use total() which is not available until the records are processed in a filter to determine which records to process.

[EdTeves]

Re: Subreport - Filter expression not logical

Sorry for delayed feedback - pressures of juggling deadlines and being in a time zone halfway round the globe.

Attached is zipped database TSM_HMO, which is primarily to keep track of the HMO premiums and plans for seafarers & dependents (family) in separate master files. Initial data entry is in Excel (emailed-in by a dozen supervisors at client side) and processed at central office of HMO broker with some formula-based sorting and value-derivation before being exported (save as DB4) to Alpha Five.

The basic regular report, with the subreport "TRAN BATCH RPT (PHP)" , is to just list members (Seafarers or Dependents) enrolled on a given day per vessel or batch, with bottom summary of how many belong to a particular HMO plan category and how much their premiums were, for the batch being reported on.

Tom, the source file for the report is just one transaction file, TRAN_NEW. It holds the fields CAT (alphanumeric field, for Categories) and PREMIUM (numeric). The report bottom summary, as done in my initial report layout, is just a text representation of the count and total per category that we need to print. (By the way, this transaction file is the only source for appending to several master files beginning w/ SFR and DEP - which I have formulated as separate Operations while I am learning how to integrate them into one sequence).

Stan, you said my formula is wrong. I saw a one-step example of using Total to get both count per item (in my case, CAT) and total for that item (PREMIUM). But using the normal drag-and-drop, I do not see (or could not get to) the right options to click on, to achieve that.

Iam trying the subreport approach (single-file source) where the full text of expression is "Total(Tran_new->Premium, Grp->Cat, Grp->CategoryCount:detail)" - which I interpret to mean "get total on field Premium for each Cat item found." Should I put in at the very end a particular given value ie. "='RP-120' ?" and do same formula for another calc field for CAT with condition "PW-60"? Or will Alpha Five detect how many unique item-values there are, and just list accordingly without my specifying the items?

Thanks for any enlightenment or showing the way.

Ed Teves

[Stan Mathews]

Re: Subreport - Filter expression not logical

I didn't say your expression was wrong, I said I didn't see how it could be a filter.

example of using Total to get both count per item

A count is not a filter. A filter yields a true or false result for each record. If the result is true, the record is included. If false, not included. Count or total should be a number. No filtering happens.

I believe what you are after is generally handled as a second report serving as a summary page. I added grouping on cat, inserted the group total and count, removed the detail section ....

If you print from a button you can add another action to print the summary at the same time.