[Solved] How to change data source of a report

Discuss the database features

[Solved] How to change data source of a report

Postby Dukane » Mon Mar 17, 2008 2:23 am

I have a dynamic Base report that I created with the report wizard. I want to change the table or query that this report gets it's data from, but I cannot find any option or preference to change that setting. How can I change the data source of the report, created using the report wizard (not Sun Report Builder)?
Last edited by Dukane on Fri May 30, 2008 4:50 pm, edited 1 time in total.
Dukane
 
Posts: 14
Joined: Mon Mar 17, 2008 2:19 am

Re: How to change data source of a report

Postby DrewJensen » Mon Mar 17, 2008 3:40 am

Under the report wizard these details are stored in hidden controls on the report.

The new data source will need to have the same field names of course but with that caveat:

Open the report in EDIT mode.
Open the Form Design toolbar
Click on the tool button "Form Navigator" - the navigator window opens.
You will see that there is a dataform named "ReporSource" and under that a set of hidden controls.
You are concerned with the controls:
Command Type - 0 = Table, 1 = Query
If your CommandType is a 0 there is a control named TableName also, if it is 1 there is a control QueryName
You can change the name of either the table or the query in the respective control.
So if you built your report against "Query1", just click on the hidden control "QueryName" select properties and in the property editor change it to "Query2".

FYI
a command type of 2 = SQL statement. All reports have a hidden control named "Command"

Let's say you built the report against a table, "Table1":
The hidden control CommandType holds a 0
The hidden control TableName holds "Table1"
The hidden control Command holds "SELECT * FROM "Table1""
Now you can actually edit the report and change that CommandType to 2
Change the value in Command to "SELECT * FROM "Table1" WHERE "Column1" = 'foo'"
If you like you can delete the hidden control TableName as it will not be used any longer.
Save it an voila - a new report against just the 'foo' records.

I hope that answers your question.
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: How to change data source of a report

Postby Dukane » Mon Mar 17, 2008 7:13 am

Yes it does! Thank you very much!!
Dukane
 
Posts: 14
Joined: Mon Mar 17, 2008 2:19 am

Re: [Solved] How to change data source of a report

Postby MarkLj » Wed Jun 03, 2009 1:59 am

I have a dynamic Base report that I created with the Sun Report Builder not report wizard. I want to change the table or query that this report gets it's data from, but I cannot find any option or preference to change that setting. How can I change the data source of the report, created using the Sun Report Builder ( not report wizard )?
I notice that there are a lot of options not available in the report builder, the menus don't have the same depth and control. Is there anyway to "rough in" a report using the builder and and then edit it without it?
OOo 3.1.X on Ms Windows XP + Linux Ubuntu Gentoo
MarkLj
 
Posts: 8
Joined: Wed Jun 03, 2009 1:35 am

Re: [Solved] How to change data source of a report

Postby DrewJensen » Wed Jun 03, 2009 2:10 am

Two ways most likely - one for sure.

One is you could base the report on a query. I mean an actual Query object in the Base file, not a query embedded in the report specification.
You can change the query then before you run the report.

The second way is that you would need to open the report definition, via a macro, in edit mode. Change the settings in the data source property, via the script and execute the report you do not need to save the report before you run it.

hmm- then again you can adjust the record set by using named parameters in the query, a saved query or an embedded query, and OO.o will prompt for new values each time it is run.

Tell you what though since this is about the Report Builder and the original thread is about report wizard how about we start a new thread if you want to continue talking about it?
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: [Solved] How to change data source of a report

Postby buckwheatpie » Sat May 08, 2010 12:58 pm

I had the same problem as Dukane, and tried DrewJensen's solution of changing the QueryName in Form Navigator - however this alone didn't work for me - i also had to edit the Command, which was the same as in the report that i'd copied and pasted.

I'm posting this so that others in my situation don't spend as much time as i did scratching their heads!
OpenOffice 3.2 on Windows XP
buckwheatpie
 
Posts: 1
Joined: Sat May 08, 2010 12:54 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests