Queries not being applied to Reports

Getting your data onto paper - or the web - Discussing the reports features of Base

Queries not being applied to Reports

Postby ukmpoam » Wed May 07, 2008 11:40 am

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
ukmpoam
 
Posts: 9
Joined: Tue Mar 04, 2008 1:56 pm

Re: Queries not being applied to Reports

Postby Safway » Thu May 08, 2008 11:29 pm

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?
If someone posts a fix for your question, then please post a quick thank you and then go to your first post, use the edit button to add [Solved] as the first word of the title so other people can rely on the fix as well.
LibreOffice 3.3.3 on Fedora
Safway
Volunteer
 
Posts: 347
Joined: Thu Apr 24, 2008 4:35 pm

Re: Queries not being applied to Reports

Postby ukmpoam » Tue May 20, 2008 5:46 pm

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.
ukmpoam
 
Posts: 9
Joined: Tue Mar 04, 2008 1:56 pm

Re: Queries not being applied to Reports

Postby DrewJensen » Tue May 20, 2008 5:51 pm

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
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Queries not being applied to Reports

Postby Safway » Tue May 20, 2008 8:15 pm

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 .
If someone posts a fix for your question, then please post a quick thank you and then go to your first post, use the edit button to add [Solved] as the first word of the title so other people can rely on the fix as well.
LibreOffice 3.3.3 on Fedora
Safway
Volunteer
 
Posts: 347
Joined: Thu Apr 24, 2008 4:35 pm

Re: Queries not being applied to Reports

Postby ukmpoam » Wed Jun 04, 2008 3:48 pm

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.
ukmpoam
 
Posts: 9
Joined: Tue Mar 04, 2008 1:56 pm

Re: Queries not being applied to Reports

Postby DrewJensen » Wed Jun 04, 2008 4:04 pm

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
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Queries not being applied to Reports

Postby ukmpoam » Thu Jun 05, 2008 11:51 am

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.
ukmpoam
 
Posts: 9
Joined: Tue Mar 04, 2008 1:56 pm

Re: Queries not being applied to Reports

Postby DrewJensen » Thu Jun 05, 2008 11:54 am

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.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Queries not being applied to Reports

Postby ukmpoam » Thu Jun 05, 2008 6:32 pm

Hi,

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

Andy.
ukmpoam
 
Posts: 9
Joined: Tue Mar 04, 2008 1:56 pm


Return to Reporting

Who is online

Users browsing this forum: No registered users and 0 guests