[Issue] Reports not updating with changes in a query
-
- Posts: 2
- Joined: Tue Jan 19, 2010 3:15 am
[Issue] Reports not updating with changes in a query
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..
Thanks a lot
Bagkaw
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..
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]
Reason: tagged as [Issue]
OpenOffice 3.1 on Windows Vista
Re: Reports not updating with changes in a query
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 2
- Joined: Tue Jan 19, 2010 3:15 am
Re: Reports not updating with changes in a query[Issue]
Woo!
That seems to have worked perfectly, thanks a lot for your help It is a very irritating bug!
Bagkaw
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
Re: Reports not updating with changes in a query[Issue][Solved]
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.
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
Gurkha Welfare Trust
Re: [Issue] Reports not updating with changes in a query
When I open report in write there is a general control that is open called 'General'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..
Thanks a lot
Bagkaw
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.
Yes, I do carry a life insurance policy.
It's a Glock Model 19 9mm.
Re: [Issue] Reports not updating with changes in a query
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Issue] Reports not updating with changes in a query
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.
Yes, I do carry a life insurance policy.
It's a Glock Model 19 9mm.
Re: [Issue] Reports not updating with changes in a query
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
Re: [Issue] Reports not updating with changes in a query
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
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
Re: [Issue] Reports not updating with changes in a query
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.
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
Re: [Issue] Reports not updating with changes in a query
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
Re: [Issue] Reports not updating with changes in a query
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.evwool wrote:Villeroy posted a workaround for this but I can't find his example - sorry Villeroy:(
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Issue] Reports not updating with changes in a query
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.
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