Report filtering - v4 vs. v5

[CALocklin]

In version 4 we could set a report to "Prompt for Criteria at Print Time" then use the following command to preview (or print) the report:

Report.preview( "ReportName", "Approval_no"1000" )

If the user's criteria at print time was: Status="Approved", the result would be only those records with Status="Approved" AND Approval_no"1000.

In version 5, the result is ALL records with Status="Approved".

Version 5 ignores the filter that is sent with the Report.preview() command. As a result, one of my reports that was 1 page in version 4 is 261 pages in version 5 - oops!!

This made me guess that it might also ignore any filter built into the report but that was not true - built in filters are still used even when the report is set to "Prompt for Criteria at Print Time".

PLEASE NOTE: Putting the filter in the report itself is not an option. The above is just an example. In reality, the script sets the filter and it can be different every time:
filter = "some complex filter set via about 150 lines of code>
Report.preview( "ReportName", filter )

I've talked the customer into switching to v5 and now I need to get the same functionality as before because this feature was used a LOT and is one of the CFO's favorite features. If the filters were simple it would be no big deal for him to enter his own but the 'pre' filter is often very complex.

Anyone know of a way to get the same functionality in version 5 short of running a query on the set and copying the records to a temp table then appending to a 'report' table?

[Daniel Weiss]

RE: Report filtering - v4 vs. v5

This should work

query.filter = "(Approval_no

[CALocklin]

RE: Report filtering - v4 vs. v5

Thanks but that passes the query to the report the same as my example - it will still be ignored when the report is set to "Prompt for Criteria at Print Time".

[Tom Cone Jr]

RE: Report filtering - v4 vs. v5

Cal,

How is the user describing the print time filter that you want to .AND. to your pre-print time filter expression ?

I'm wondering if you could achieve what you need by creating a single filter expression you pass to the report without using 'specify add'l criteria at print time', by changing the input sequences a bit.

-- tom

[CALocklin]

RE: Report filtering - v4 vs. v5

I was almost convinced that v5 worked as I expected and my issue might be some kind of strange conversion issue since this app was originally designed in v1 then converted to v4 and now to v5.

However, I decided to run some additional tests of my own...

Attached is a demo app that shows how all filters are additive EXCEPT when a filter is passed via the Report.preview() command AND the report is set to Prompt for Criteria at Print Time.

**************************
Before creating the app, I wrote the following so I'll include it in case anyone is (a) really curious or (b) a glutton for punishment.

The script prompts for a number of variables and creates the 'script' filter that is used in the Report.preview() command. The user then simply uses the expression window that comes up with the "Prompt for Criteria at Print Time" option to add additional filter criteria.

In v4, the 'user' filter and the filter in
Report.preview("ReportName",filter)
were additive. The same as if the 'script' filter were included in the report itself. I'm not sure if v4 was doing something it wasn't intended to do and v5 has 'fixed' it or what? At any rate, it's causing me a real problem for the reasons mentioned previously.

Maybe this summary would help:

First some definitions...
"built-in" filter = the filter that is defined inside the report itself.
"script" filter = the filter passed to the report via the Report.preview() command or the Report.print() command.
"user" filter = any filter defined by the user through the "Prompt for Criteria at Print Time" window.

V4
- built-in_filter + script_filter = built-in_filter + script_filter
- script_filter + user_filter = script_filter + user_filter

V5
- built-in_filter + script_filter = built-in_filter + script filter.
- script_filter + user_filter = user_filter only; the script filter is ignored.

*********************
Here is the filter 'logic' for my app in greater detail:

In the scripted filter section the user has a choice of:

- Start date and End date. (i.e., 'between')
- A simple selection - one of 5 facilities or "All".
- All carriers or specific carrier. If 'specific', a list of carriers used between the Start and End Dates is determined and presented for selection. There are dozens and the names change throughout the year. (This can't be done in a 'normal' filter because you have no reasonable way get the list of carriers.)
- All customers or a specific customer. If 'specific', same issues as above - including the changing names.

Each of the above is included in every 'script' filter. The selected records must meet all of the criteria.

After creating the 'script' filter, the user then has the option to use the report's order/filter option to select, of course, any combination of criteria from the table. In this specific case, he could reasonably select any combination of the following additional criteria:
- a specific Approval Number.
- a sample ID or range.
- a track number.
- a specific driver.
- a min., max., or range of gallons.
- a specific Type or range of Types (1-13).
- an Item code or range of Item codes.
- a range of Oil, Water, and/or Solid percent.
- loads with some combination of any of the following: Halides, Hex, Corrosion, Viscosity, pH, Zinc, Nickel, PCB, and/or something called RAG.
- tansportation rates.
- surcharge rates.
- processing rates.
- total invoice amount.
- oil credits.
- other charges.

Plus, there are probably a couple other possibilities I haven't considered. There are 83 fields in the main table and most of them might be used for selection. I won't even attempt to code every possibility.

By the way, this issue involves 7 scripts and about 36 reports in one app. The scripts are all similar but they are 400-600 lines long to cover all the report and filter possibilities.

[Tom Cone Jr]

RE: Report filtering - v4 vs. v5

Cal,

First time through the previews:

1) report 5 does NOT display records that meet the combined filter expression. Records with Member_nof field values other than specified in the combined filter expression are showing up.

2) ditto report 6

Your comment on both report 5 and 6 is that these are working as expected. ????

3) in the message box preceding report 7, user should press Ctrl-V to paste the optional criteria at print time. (Not Ctrl-C, of course).

Now, back to the example to dig into details.

-- tom

[Tom Cone Jr]

RE: Report filtering - v4 vs. v5

Cal,

Reports 5 & 6 work fine the second time I run them. I think A5 is using the earlier query list. I also think these reports displayed the wrong records the first time because the report ran before the query lists were finished being built.

See if you can replicate this by deleting your query lists and then running reports 5 & 6 again. In each case the reports I get are not limited to member_nof = 100001.

-- tom

[Tom Cone Jr]

RE: Report filtering - v4 vs. v5

Cal,

Regarding reports 7 & 8 are you 'pasting' the optional user supplied filter criteria into the 'order expression' for the report, or into the 'filter expression' for the report ?

I ask, because first time through I stupidly pasted the expression into the first dialog that appears..., instead of using the new dialog box to select the right way to supply the supplemental criteria.

-- tom

[Tom Cone Jr]

RE: Report filtering - v4 vs. v5

Cal,

One solution to the problem presented by your Report 8 is to avoid hard coding a specific filter expression into your report layout, and to avoid using a filter expression in the report.preview() statement. This can be achieved by hardcoding a global character type variable in the details filter property of the report.

If you then set the report to permit the user to specify additional filter criteria at print time, the additional filter criteria are additive to the filter specified in the global variable embedded in the report layout specifications.

Here's an example, using your sample database.

Unzip the attachment to an empty folder.

Examine report "Filter_Test_prompt2". You'll see how the filter expression embedded in the report has changed. Note that additional user filtering criteria are permitted at print time.

Now examine script "Toms_Filter_test". You'll see how the report is called.

Then run the script specifying Member_Nof = "100003" as the optional user supplied filter criteria at print time.

-- tom

[CALocklin]

RE: Report filtering - v4 vs. v5

See the instructions:

HOWEVER, IF YOU WANT TO TRY USING A FILTER
- Make sure you enter a FILTER not an order.


Just couldn't resist the jab. I know there's a lot to read and it's easy to miss the details. In fact, I missed a few details in my original application. See below for the explanation and revised app.

[Tom Cone Jr]

RE: Report filtering - v4 vs. v5

Oops!

Sorry 'bout that. -- tom

[CALocklin]

RE: Report filtering - v4 vs. v5

You are right about the problems with reports 5 and 6 - see below for more details. In fact, there was also a problem with report 3 because I forgot to include the filter in that part of the script.

The problem with reports 5 and 6 has been corrected. When I built the sample app, I forgot that the script had to be called from a Menu form attached to a different table than the one I was reporting on. The script attempts to load a form (invisible) and set the 'current' records via a query. However, if the form you are in (the visible one) is based on the same table, the 'current' records are the ones relating to the visible form. (I expect you may have to read that a couple times before it's clear.)

The same thing could be accomplished by using 'parentform' instead of opening a new form but most of these reports run from one menu form so the user doesn't have to figure out which form to go to when running a report. The preferred method just depends on where you are to start with and I forgot about this when building the first app. (There are now two scripts if you want to see the difference. If you open the 'filter_test' form which is based on the donations table, the button there uses the 'parentform' method.)

***********************

As I was writing this, I went back and re-read your last message more carefully and I think you have the solution - and a very simple one at that. It appears I was just stuck in the old rut and forgot there might be another rut on the other side of the road that was going the same way.

When creating a report that only used the 'user' filter plus the 'script' and/or 'report' filter, everything will be essentially the same as far as the user is concerned.

On the other hand, for what it's worth, the idea of setting the Current Selection of Records via the script was something I discovered in v4. It was possible to define a limited query that used LQO on the form and then 'add' the additional criteria that would make the total filter too complex for LQO. When I used this method in v4, the report only seemed to look at the records in the current selection - if the current selection was only 01/0103 - 01/31/03, v4 seemed to completely ignore everything from 1997 through 2002 and after Jan 03. This increased the speed of certain complex reports by as much as 20:1. (Although the most time saved was 14 minutes on a report that originally took about 15 minutes.) When running the same thing in v5, the report seems to re-run the query used for the Current Selection of Records. This increases the report time but, luckily, v5 runs queries faster than v4 so it's not too bad - just not as good as it was. I guess with v5 there is no longer any point in pre-selecting the 'current selection of records'.

I suspect this re-running of the query is intentional since I've seen postings in v4 about current queries not being up-to-date. In my case, a few milliseconds wasn't enough to matter.

Thanks for all your help. Now I just have to go change all those scripts!

[CALocklin]

RE: Report filtering - v4 vs. v5

By the way, I hope you don't think I added that note because I made the same mistake you did. I would never make a mistake like that :)