[Issue] Reports not updating with changes in a query

[TheGurkha]

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

[Villeroy]

http://www.openoffice.org/issues/show_bug.cgi?id=98163

[crowsgocaw]

Woo!

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

Bagkaw

[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.

[Rasputin]

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

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.''''''

[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.

[Rasputin]

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.

[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.

[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

[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.

[RPG]

Hello

Maybe this tutorial can help you.

Romke

Edit: I changed a not more working link

[Villeroy]

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: viewtopic.php?f=39&t=35905&p=164900&hilit=+power+filtering#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.

[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.