[Solved] How to change data source of a report

[Dukane]

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)?

[DrewJensen]

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.

[Dukane]

Yes it does! Thank you very much!!

[MarkLj]

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?

[DrewJensen]

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?

[buckwheatpie]

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!