Reporting revised query

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
STRowe
Posts: 2
Joined: Wed Nov 26, 2008 6:27 pm

Reporting revised query

Post by STRowe »

I have created a simple database; one table, no relationships.
I have created a report that illustrates the data in my table.
I have created a query that selects desired records from the table.
I have created a report that illustrates the results of the query.
I want:
- to use the same report to illustrate selections from the table, but apparently I have to recreate a report for each query. Can I save the report as a template? Can I copy the report an point it at a different query?
- to change the query and have the report show the effect of the revised query. The report shows only the results of the original query and never changes. How do you make the report respond to changes in the query? (report was created as a dynamic report)

Using OOo 3.0 in XP professional. Have not installed SRB. I understand that my problem is a common one. But, in my research of documentation, tutorials and community forum, I do not find a clear solution. Please help.
OOo 3.0.X on Ms Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Reporting revised query

Post by Villeroy »

Open the report for editing (right-click>Edit...)
Get toolbar "Form Design" and call the "Form Navigator" (5th button)
You see that the report has a form named "ReportSource" including 6 hidden fields. Hidden fields are for configuration entries used in macros and such. The property window reveals the values:
CommandType = 1
I think, this corresponds to http://api.openoffice.org/docs/common/r ... dType.html
Query name refers to the name of the used query if CommandType = 1
Command refers to the SQL string if CommandType = 2, I believe. Unfortunately it stores the query string at the report's creation time.
I tried to erase that value (only the SQL string not the hidden field itself) which seems to fix the issue.
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
seglea
Posts: 26
Joined: Tue Nov 18, 2008 1:04 am
Location: Exeter, UK

Re: Reporting revised query

Post by seglea »

I've just checked this and can confirm that it works. I assume that this is a Base bug, since with Command=1 it should not be looking at the SQL but at the Query. Thank you very much.
The only thing I'd add for the benefit of other newbies is that on my setup (oo2.4 on WinXP) there are TWO form navigator icons on the report form when you open it in Edit mode - the one you want is at the bottom of the form (you can check this because this is the toolbar that disappears when you uncheck Form Design in the Toolbars section of the View menu), not the more obvious one at the top of the screen.
OOo 2.4.X on Ms Windows XP
leviperrine
Posts: 1
Joined: Sat Jan 24, 2009 4:37 am

Re: Reporting revised query

Post by leviperrine »

Villeroy is awesome! This has frustrated me for a few months as I have been passively working on a project. Thank you!!! :D
OOo 3.0.X on MS Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Reporting revised query

Post by Villeroy »

Thank you. For the records (and reports): This is issue 98163: Hard coded SQL in reports. Feel free to vote for it. May be it will hit the first hurdle of being noticed and confirmed then.
[Tutorial] Reporting bugs or suggestions
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
Gavdal
Posts: 1
Joined: Sat Jan 24, 2009 7:51 pm

Re: Reporting revised query

Post by Gavdal »

I have reached a similar stage as ST.Rowe reported, 1Dec,2008.
I have created a simple database; 2 tables, 1 relationship.
I have created a query that selects desired records from the table.
I have created a report that illustrates the results of the query.
When I run my report, based on the query, I only get output on the query I first used.
I need to report on filtered or altered queries, which work fine as queries but do not follow through to the report.
I am using SRB and cannot find a Form Design or Form Navigator buttons to perform the work-around, suggested by Villeroy on the same date.
Very frustrating - So near and yet so far.
OOo 3.0.X on Ms Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Reporting revised query

Post by Villeroy »

Gavdal wrote:I am using SRB
This generates reports in a completely different way. I don't know anything about this tool.
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
medisinmannen
Posts: 1
Joined: Tue Feb 10, 2009 10:54 pm

Re: Reporting revised query

Post by medisinmannen »

How to change the source with Sun Report Builder (SRB)
When you make a report and use the wizard to start SRB it doesn't link to the query or table. Instead it writes a SQL-command, which doesn't change when editing the original query or table. To change the source from which the report gets its data in SRB do as follow:

Open your report in editing-mode.
Make sure you have a columnar pane at the right with information about the currently selected object. If you don't have this pane, click the "View" entry in the Report Designer menu, and click on "Properties" to turn on the Properties pane.
Click on the gray field outside the reportfield. To be sure you are in the right place, click on the General tab, and be sure that the properties you're looking at are for the object named "Report". If you are, go back to the "Data" tab.
The value of "content type" are probably SQL-command and the value in "content" is a SQL-command. At least it was so in my case. Change the "content type" to query or table, whichever is your type of source.
In "content" select your query or table and click on a random part of your report.
Save the changes you have made.

The report will now reflect changes you make, but if you add a field to your table or query you still have to add it to the report.
sdan
Posts: 9
Joined: Sun Sep 19, 2010 2:21 pm

Re: Reporting revised query

Post by sdan »

Thank you medisinmannen!
OpenOffice 3.2.1
Mac OS X 10.6.3
Post Reply