[Issue] Reports not updating with changes in a query

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
crowsgocaw
Posts: 2
Joined: Tue Jan 19, 2010 3:15 am

[Issue] Reports not updating with changes in a query

Post by crowsgocaw »

Hi there,

I know this problem has been posted several times before, but I have tried all the solutions I can find on the web and nothing has worked so far :(

I have a small database, with a query and a report displaying the contents of the query and it all seems to work fine. But when I change the query's criteria the report does not automatically change.

I have trawled through the forums and tried numerous things (editing the reports content type, content etc) but to no avail. Can anyone help me? It's doing my head in.. :crazy:

Thanks a lot

Bagkaw
Last edited by TheGurkha on Tue Jan 19, 2010 10:20 pm, edited 3 times in total.
Reason: tagged as [Issue]
OpenOffice 3.1 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Reports not updating with changes in a query

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
crowsgocaw
Posts: 2
Joined: Tue Jan 19, 2010 3:15 am

Re: Reports not updating with changes in a query[Issue]

Post by crowsgocaw »

Woo!

That seems to have worked perfectly, thanks a lot for your help :) It is a very irritating bug!

Bagkaw
OpenOffice 3.1 on Windows Vista
User avatar
TheGurkha
Volunteer
Posts: 6482
Joined: Thu Mar 13, 2008 12:13 pm
Location: North Wales, UK.

Re: Reports not updating with changes in a query[Issue][Solved]

Post by TheGurkha »

For those who come searching, the workaround is:

Open the report in edit mode.
Get the form navigator. It shows 6 hidden controls (named values as configuration).
One of them is "Command". Get the property window for "Command". It's value is the SQL string of our query at creation time.
Delete that SELECT query, save the report, load it, change query, reload report, works as expected.
Ubuntu 14.10 Utopic Unicorn, LibreOffice Version: 4.3.3.2
Gurkha Welfare Trust
Rasputin
Posts: 21
Joined: Thu Feb 11, 2010 11:04 pm

Re: [Issue] Reports not updating with changes in a query

Post by Rasputin »

crowsgocaw wrote:Hi there,

I know this problem has been posted several times before, but I have tried all the solutions I can find on the web and nothing has worked so far :(

I have a small database, with a query and a report displaying the contents of the query and it all seems to work fine. But when I change the query's criteria the report does not automatically change.

I have trawled through the forums and tried numerous things (editing the reports content type, content etc) but to no avail. Can anyone help me? It's doing my head in.. :crazy:

Thanks a lot

Bagkaw
When I open report in write there is a general control that is open called 'General'
It has two tabs : General and Data.

The follows selections are made under the Data tab:

Content type : SQL Command.

Content : SELECT "Date" AS "Date", "Meal" AS "Meal", "Food" AS "Food", "Calories" AS "Calories", "Carbs" AS "Carbs", "Remarks" AS "Remarks" FROM "menu Table 2" AS "menu Table 2"

Analyse SQL : Yes

Filter : There is a drop down that brings up a query based on the table Variables
I insert what ever new date I want.

Report Output: Choose OFR or Spreadsheet.
I prefer using ODF.

When I use Calc I import from Calc in a preformatted tymplate.

Save report.

Hit Run

Works for me.

As you can see I didn't use spell checker.''''''
OpenOffice29SQ 3.2 on Windows XP Pro

Yes, I do carry a life insurance policy.
It's a Glock Model 19 9mm.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Issue] Reports not updating with changes in a query

Post by Villeroy »

Rasputin,
You are running the Sun Report Builder extension. The original topic is about a bug in the old report wizard which is used to create very simple reports when the extension is not installed.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Rasputin
Posts: 21
Joined: Thu Feb 11, 2010 11:04 pm

Re: [Issue] Reports not updating with changes in a query

Post by Rasputin »

Villeroy wrote:Rasputin,
You are running the Sun Report Builder extension. The original topic is about a bug in the old report wizard which is used to create very simple reports when the extension is not installed.

Ops., Sorry about that.
OpenOffice29SQ 3.2 on Windows XP Pro

Yes, I do carry a life insurance policy.
It's a Glock Model 19 9mm.
lawrm
Posts: 5
Joined: Sun May 17, 2009 11:05 am

Re: [Issue] Reports not updating with changes in a query

Post by lawrm »

I have created a query and then a report using Sun Report Builder and cannot see how to print a new report when I change the filters in the query.
OOo 3.1.X on MS Windows Vista
evwool
Volunteer
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: [Issue] Reports not updating with changes in a query

Post by evwool »

Villeroy posted a workaround for this but I can't find his example - sorry Villeroy:(
The workaround is to base your report on a query that is filtered by a table
Use Villeroy's ever-useful Filter Table idea - a single row table which is used only to filter things, add the Filter table to your query and link the field in the filter table to your data table. You can update the filter table's data in various ways, including list boxes.
Here's a really simple example of using a filter table to filter a report
Attachments
FilterReportSimple.odb
(30.05 KiB) Downloaded 506 times
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
MFC
Posts: 3
Joined: Thu Dec 23, 2010 12:07 am

Re: [Issue] Reports not updating with changes in a query

Post by MFC »

Just wanted to say thanks for your "tip" to make the "Dynamic" reports actually work. I spend about a month trying to get it to work and it never did; all the reports were "static."

I'm not sure of all the hidden menus and programming needed, but your tip to go into the Form properties and adjust the "command" sequence worked.

I sure wish there was a BASE Guide that went into more detail than the Getting Started with OpenOffice Guide which is fairly simple in nature.

In any case, thanks, you made my life at work so much easier now.
OpenOffice 3.1 Windows 7
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Issue] Reports not updating with changes in a query

Post by RPG »

Hello

Maybe this tutorial can help you.

Romke
 Edit: I changed a not more working link 
Last edited by RPG on Tue Jun 07, 2011 11:30 pm, edited 2 times in total.
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Issue] Reports not updating with changes in a query

Post by Villeroy »

evwool wrote:Villeroy posted a workaround for this but I can't find his example - sorry Villeroy:(
The workaround for the bug which prevents the old style reports adjusting to an edited query is mentioned in http://www.openoffice.org/issues/show_bug.cgi?id=98163.
It forces the program to re-read a slightly modified query before it dumps the query result into the report's Writer table. "Slightly modified" means that the structure of the query result and the field names still need to match the structure of the Writer table. Refer to the example in the bug description based on the "Bibliography" data source. For a query with new field names, modified order of fields, field count or types of fields you have to build a new report anyway.

The filtering by criteria stored in a table can do the job as well as stated by evwool. This technique has been documented together with all the other filter techniques by Arineckaig: http://user.services.openoffice.org/en/ ... ng#p164898 [example database included]

Parameter queries offer another option when different filter criteria for the same fields are needed
(SELECT...WHERE "Field X"= :Something_Else)

My BerlinStreets.odb filters a huge list of street names by a given string of first letters (show all street names starting with "Elisa")
"Query_Filter" should better be named "Param_Query" or something since it uses a parameter query. Open the query, fill out the query prompt ("StartsWith") and get the row set filtered by the criterion you provided. From this query I created a report which is also named "Query_Filter". Running this report will also prompt for the criterion and dump the query result into the Writer table contained in that report.
"Query_Report" should better be named "Query_FilterForm" or something since it uses the parameter you entered into the form "FilterForm". Anyway, play with the form, run the report and see how the report shows the same data as the form every time you run it.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
erf9201
Posts: 20
Joined: Wed Jun 22, 2011 2:04 am

Re: [Issue] Reports not updating with changes in a query

Post by erf9201 »

Just wanted to contribute...

For those of you who use Sun Report Builder (SRB) (and this should probably work on the built-in Report Wizard too) here is a workaround to change the query(or table) on which you are basing your report.

Once your report is created and you need to change the query, close Base, find your database file (.odb), unzip it (or use automated archivers such as 7zip). Once inside the file navigate to reports\Obj#\content.xml

Obj# is the automatically created directory for your report - you may have many depending on how many reports you have created. I think they are in order so just try them one by one.

Open content.xml with any text editor or buit-in text editor if you are using 7zip.

Search for SELECT (or whatever your query starts with).

Change needed parameters, but make sure that you do not damage surrounding xml tags such as """ e.t.c

Save the file.

Zip it back to odb (or tell 7zip to do it automatically)

Go back to Base and you should have your report now running with the updated query.


Hope this helps.
OpenOffice 3.3 on Windows 7 HP 64bit HSQLDB 2.2
Post Reply