Queries not being applied to Reports

[ukmpoam]

Reccently I've discovered that when I run a report based on a query the filter settings of the query are not being reflected in the Report ie all data is being shown regardless of the filter criteria, when I look at the query it is running correcly Anybody have any idea what might be causing this & how I can fix?

Which version of OpenOffice.org are you using? 2.4
What Operating System (version) are you using? XP

Thanks Andy

[Safway]

I'm not sure how you created your report as you didn't say; but, I don't have this problem using OO.o 2.4 on Linux.

Here is how I create my reports:
1) Create the query as you normally would.
2) Right-click on the query and a menu comes up. On this menu one of the items is "Report Wizard."
3) Click on the "Report Wizard."
4) Go through the report wizard and MAKE SURE ALL your filter criteria is included as a field in the report.
5) During your groupings, make sure it makes sense to group it as you wish. For example, does your report work when you don't group anything?
6) Be careful how you modify the report. Does your report work when you don't modify it during or after the report wizard?

[ukmpoam]

Hi

I use XP.

I created the report as you described and the query works for the criteria set when the report is first created but if you go back & change the criteria of the query the report does not change ie it reflect the original criteria of the query.

Andy.

[DrewJensen]

Hello Andy,

When you use the report wizard to create reports the contents of the query ( the select statement ) at the the report is created is copied into a hidden control in the report document. This copied select statement is what is used to generate the report when it is run.

You have two options then if this is not what you want to happen.

1 - use replacement parameters in the query. This way the system will prompt for the values to use in the query when the report is run.

2 - use the SUN Report Builder to generate the report. This tool does not copy the select statement from a query object if that is the basis of the report, rather it uses the query each time it is run to get the select statement.

HTH

Drew

[Safway]

Along with what Drew wrote, I thought it might help you to know you can gain access to the hidden controls. Here is how:

* Open the report in edit mode by right-clicking on the report name and select "Edit"
* Ensure the form design tools are availble by View | Toolbars | Form Design
* Click on the "Form Navigator" in the form design toolbar
* This brings up the form navigator window
* Expand out all items in the form navigator window if necessary
* Right-click on the "Command" option and select "Properties"
* Here is where you will find your SQL command that performs the query for your report
Note: as you would expect, this query is tied to the report layout so don't go too wild editing the query/layout.

Have said this, the SUN Report Builder is a nice, upfront, graphical method of doing what I think you want. It can be used with OO.o/SO version 2.3 and above I believe. It can be downloaded from http://extensions.services.openoffice.o ... portdesign .

[ukmpoam]

Hi

Thanks ever so much for your help and advice, I'm now using the SUN Report Builder and that has solved my previous problem but unfortunately a new problem has emerged. The query now works OK and filters the report as I would expect but when I try to use group function on a report based on a query it returns a blank report. If I base the report on a table rather than a query the grouping function works correctly.

Any ideas on what I'm doing wrong & how I can fix?

Andy.

[DrewJensen]

yes - the last 2 releases of the Report Builder are flawed when run with OO.o 2.4 - you need to use Report Builder 1.0.2 for the time being

[ukmpoam]

Hi

Thanks I'm now using Report Builder 1.0.2 & reports run OK but when I close Open Office & re-start later I find that when I run a report it returns a blank report & I have to disable Report Builder 1.0.2 & then add it again for the reports to run.

Do you know how I can correct this?

Andy.

[DrewJensen]

Remove the extension one more time...and this time make sure you completely exit OO.o this includes Quickstarter if you are using it.
Then open up OO.o again and install the extension, that should clear your problem.

[ukmpoam]

Hi,

Thanks for the advice, I tried your suggestion but the problem still occurs, any other suggestions?

Andy.