report(based on set) won't use xbasic query

[Jim Chapman]

Hello All,

Using xbasic, I have not been able to query a set, then run a report based on the set, and have the report use the query I created via xbasic without adding order and filter parameters to the :report.print() method. I'm assuming this is the way it is. We can do this with with a report based on a single table, but apparently not with a report based on a set. I know it's simple enough to just add the parameters to the report.print() method, but I hate to add to the overhead by running a query and then forcing the report to run the same query all over again (unless Alpha is smart enough to use the just created xbasic query).

t = table.open("mySet.set")
query.filter = "state = 'IA'"
t.query_create()
:report.print("myReport")

Any thoughts on this?

Thanks,
Jim

[Tom Cone Jr]

RE: report(based on set) won't use xbasic query

Jim,

This should be working for you.

Check the properties of the details region of your report layout. Leave the order and filter expression boxes empty, and be sure to set the report to:

Base Report On Current Selection of Records

If this has already been selected, I suspect something in the way in which you are calling the report is releasing the query, perhaps by selecting another index or something, just as the report layout is loading...

To confirm that this works, try this.
Using the Invoice app that ships as a sample with Alpha Five, there's an Invoice report, based on the Invoice Set.

If you do a QBF using the default form for the SET, and select a single customer_id, then close the default form, and immediately run the Invoice report you will see that it's scope is automatically limited to the selected customer_id.

-- tom

[Jay Talbott]

RE: report(based on set) won't use xbasic query

Jim,

I have sometimes found it easier to filter just on the parent of the set using

t=table.current(1)

from a form based on the set

Perhaps this will help.

Jay Talbott

[Jim Chapman]

RE: report(based on set) won't use xbasic query

Thanks for your replies Tom and James,

I'm doing this all from within xbasic. I'm not at a form that has anything to do with this. Try this with the sample invoice database that comes with A5. Create this script and you'll see what I'm getting at. The report shows all the records, but the query only has two. This report's query is defined to 'Base report on current selection of records'. Again, this is doable with a report based on a single table, but I've not be able to make it work with a report based on a set.


''XBasic
t = table.open("invoice.set")
query.filter = "sales_rep ='JSM'"
ndx = t.query_create()

:report.preview("invoice")

vnum = ndx.records_get()
ui_msg_box("Records in the query", str(vnum))

ndx.drop()
t.close()


Thanks for you help,
Jim

[Tom Cone Jr]

RE: report(based on set) won't use xbasic query

Jim, I know it's not a complete answer, but ...

Try this:

'XBasic
t = table.open("invoice.set")

:report.preview("invoice","invoice_header->sales_rep = 'JSM'")

t.close()


-- tom

[Jim Chapman]

RE: report(based on set) won't use xbasic query

Hi Tom,

This will work, but what I'm thinking is that it causes the report to do another query when I've already just done one via xbasic. I don't know if Alpha is smart enough to reuse the just created query or not. Also, it brings up the issue of concurrency. I want to be sure that the report and my query are one and the same.

Thanks,
Jim

[Tom Cone Jr]

RE: report(based on set) won't use xbasic query

You're correct, but in many contexts there's no need to run the antecedent query. Just supply the filter expression directly to the preview method.

As for why this is happening, I suspect it's because your xbasic script does not result in an MPX being saved to disk, as would a classic QBF. The recordset exists in memory, but is not on disk. I'm guessing the report can't see the query because it's neither a saved query operation, nor an MPX... Wonder if there's some way you can force the MPX to be created?

-- tom

[Jim Chapman]

RE: report(based on set) won't use xbasic query

Hi Tom,

>>but in many contexts there's no need to run the antecedent query>I suspect it's because your xbasic script does not result in an MPX being saved to disk, as would a classic QBF. The recordset exists in memory

[Tom Cone Jr]

RE: report(based on set) won't use xbasic query

Jim, have you tried opening a form based on the same set, running your code to query the set upon which the form is based (taking care to change absolute references, to relative references... i.e. tbl.current() instead of tbl.open()), and then calling your report while the form is open? It seems to me that this could be done while the form is hidden, so the user need not be aware of it...

-- tom

[Jim Chapman]

RE: report(based on set) won't use xbasic query

Good idea Tom, I'll try this. I'm not in love with the overhead of opening a form needlessly, but I'll give it a try.

Thanks,
Jim

[Tom Cone Jr]

RE: report(based on set) won't use xbasic query

Jim, the overhead need not be significantly different than what you're doing with table.open(). Don't put any fields on the form. Just use it as a device to open your set.

-- tom

[Finian Lennon]

RE: report(based on set) won't use xbasic query

Tom:

Just to confirm Jim's experience which I have been dealing with recently and no, having the xbasic query run on a form based on the set doesn't work either.

I think that you always have to do both (run the query first and then print/preview the report) since the return, if there are no records to print, is ugly. Either nothing happens if the report is sent directly to the printer or you must preview and the user has to wait and then click out of an empty preview.

Finian