[Solved] Changing source table/query of report

[shimonl]

Hi,
I created a report based on a query.
Now I would like to run the same report using the results of a different query,
without going through all the (considerable) hassle I had to get this report the way I want it.

I found a "solved" question with this topic, at:
https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=3591,
but it is quite old, and I was unable to find the things it says, in LO5.

Can someone please tell me how this is done?
Thanks very much!
Shimon

[F3K Total]

Hi,
copy/paste the report.
In reports edit mode, there is a Report Navigator in Toolbar Standard. Open it, rightclick the top icon in the tree -> Properties -> tab Data
Here you can change the datasource.
R

[shimonl]

Thank you F3K Total!
At first I was not sure I understood your instructions, but then I got it.
I will go through the steps here with screen shots, so that the next time I need a reminder I will find it
(And it may be useful to someone else as well)

1) go to reports edit mode:


2) find the Report Navigator found in Toolbar Standard (the icon with the little compass):


3) Open it, and right click the top line, the one with the word "Report":


4) select "Properties" (the bottom choice). That will pop up the Properties panel on the right,
with two tabs: General and Data. Select "Data", and in the "Content" line there will be a drop-down list
of available tables and queries. Select the one you want!


5) Save the report with the new settings!

Thank you again!!

[hman2]

On OO 4.17 (german) I cannot recreate this solution. I want to change the query that is underlying my otherwise fully functional report (with much effort spent on the layout, so just building a new report is not an option). I simply want to add a new sorting level 3 (sort by ID) after the existing levels 1 and 2.

I'm stuck at selecting the Report Navigator. There is no such thing. I even checked into the options that are presented in the standard toolbar, there is no Report Navigator either. There is, however, a Navigator. But that includes none of the options shown on the screenshot here, especially nothing to change the query. As there are some years between this solution and version 4.17 (and there is a 4.18, which I havent't installed yet), maybe the Report Navigator was removed?

Thanks
hman2

[UnklDonald418]

Make sure the Oracle Report Builder extension is installed and enabled
https://extensions.openoffice.org/en/project/oracle-report-builder
Open report for editing, the F5 key should toggle the Report Navigator.

[hman2]

Thanks. But one caveat: The link says "This extension was not updated recently. It might not work with latest versions of OpenOffice.". And this remark is dated 2007. A LOT of revisions of OO earlier than my 4.17, and even more so to the current 4.18... Can a tool that is almost one and a half decade older really work without causing problems?

[RoryOF]

The answer is "it all depends"; try it and see. Many old macros will run correctly, as the underlying code has not changed.

[shimonl]

I have not looked at this in a long time (I see it is dated Sat Jan 23, 2016 8:39 pm ), but I am pretty sure I never installed any extensions.
Maybe I will have a chance to look at it today, if I can help I will

[hman2]

I took this as a trigger to check how current my installation is. Apart from the recently deployed 4.18, I want to check my extensions. There is a button to check this - but it doesn't work. Server response is ".". Yes, period. Just that Maybe it tries to poll a server that no longer exists, and no one ever wrote a decent error message, but that's speculation.

Normally I wait a little longer before moving up a version number, but now I want to migrate to 4.18 faster, because there is an annoying, although otherwise harmless display bug: You can scroll down on a database table. But not up. At a certain point when scrolling up one and the same line gets repeated over and over (only on display, at least so it seems. But I want to be sure and leave this window without saving. Better safe than sorry I had a corrupt database once and do not want to go through that again (Luckily I did have a PDF output from it).

[UnklDonald418]

Open Office hasn't changed much since the ORB extension was released and the Base module not at all, so that old ORB continues to work even with OO version 4.1.8.
Several years ago the developers of Libre Office made the decision to make the ORB the default report writer, so it is built in to LO Base.

[hman2]

Ah, thanks. And when I interpret shimoni's screenshots correctly, he/she is using LO, which would explain why no extension was installed there. Now I hope the display bug gets rectified in 4.1.8. So far I did not check whether a bug report was opened for that (not by me).

[shimonl]

Hi,
Yes, I am using LibreOffice, on PCLinuxOS:



I was not aware of this difference between OO and LO, sorry.
The screenshot I prepared for you shows how easy it was for me (using LO) to get to the Navigator
(although the icon has changed since my previous post):



Sorry about the confusion,
Shimon

P.S "he"

[hman2]

Thanks guys for the help so far.

But the report builder doesn't seem to work. I have downloaded it, I installed it (from hard disk, not directly from then net), it then shows up in the extensions manager. At first I thought "maybe OO needs a restart", so I closed it, started it again, to no avail.

I open my DB, right click on the report, choose "edit", the edit dialog opens, and there is still only a "Navigator", no "Report Navigator". I then checked whether just its icon was deactivated on the standard toolbar, but nothing. I check all the menus, there is no trace of a Report Navigator anywhere. Except for the extensions manager. It's still listed in there.

Btw., just for having a look at the web way, I did launch "download additional extensions from the net", which loads https://extensions.openoffice.org/getmore?cid=920794, and there the Oracle Report Builder only has a score of 75% on compatibility with OO 4,x...

I installed the "latest" version, 1.2.1. Btw, my OO is the 64 bit build in Debian file format (.deb).

[hman2]

After checking the entirety of settings and preferences, I found that peculiarly no Java was activated. There is only one Java on my Mint 18, and it was correctly identified (OpenJDK 1.8.0_275), but for reasons unknown the radio button (a single radio button? What kind of GUI is that supposed to be?) was not activated.

So I activated it, which prompted me that OO needs a restart, so I restarted it. Nothing changed. Nothing AT ALL.

[Villeroy]

Linux Mint comes with Libreoffice preinstalled wich works very well with the preinstalled Java and the preinstalled report builder and the preinstalled Python3. Oh, and the Java selected in the listbox works even when the associated option button remains unchecked. OpenOffice has too many unfixed bugs.

[hman2]

I know that LO comes with Mint, but I do prefer the OO GUI. I have used that stuff for an eternity, right after the first version of the predecessor (Star Office) was released... I know that technically they are all different, but I like to stick to a GUI I got accustomed to. Btw., Mint 18 has Python 2.7.12. but I would expect an "Oracle Report Builder" to run in nothing else than Java

[Villeroy]

Come on. You can easily adjust to the LO menues. I agree, they are stupid. However, adjusting a massive chunk of unmaintained software to recent computer systems is a life project. The "Oracle Report Builder" is a "Sun Report Builder" actually and LibreOffice has the man power to maintain even this code. Side note: Charts in reports do not work since many years. however, there is still Calc as alternative report engine.

[UnklDonald418]

That is one of the reasons I still use OO, but when using LO, select Tools>Options>Libre Office>View and you will find an option to choose an Icon style. The General style appears to be closest to the Icons used by OO.
LO has more menus and menu choices than OO and a few items have new locations, but most are the same.
There are a few head scratchers, for instance LO Writer has a Form menu where you can select Form controls and even the Form Navigator. Not needing the Form Control and Form Design toolbars when designing a Base Form might be handy, but if you open a Base Form in the Design mode that menu is missing! You still have to enable Form Control and Form Design Toolbars.

[Villeroy]

I tend to ignore all this and work with the form design toolbar. There are many active LO supporters who never used the product on a sophisticated level. There are bugzilla maintainers who never saw a user-defined StarBasic function in a spreadsheet. Another one could not tell a table control from a listbox. That's OK because they contribute, on the other hand it is somewhat surprising. The old OOo product was maintained by Sun employees mainly had a strict quality managment rejecting most of the "brilliant ideas" that tend to be working around the actual functionality trying to be somewhat "intuitive" (more like Microsoft!).

[hman2]

At my employer's office I am forced to work with the current MS Office, and ever since they had the "brilliant" (NOT) idea with ribbons I hate MS Office. Ribbons have halved my productivity in Office. I am fond of hierarchical menus where options have LOGICAL locations, because this means, if you need a rare function you can make an educated guess where it SHOULD reside. In old MS Office (pre-ribbons) almost all options were in logical locations. Now I have to SEARCH for functions. Therefore, I despise everything that tries to be closer to (new) MS Office. And besides that, I dislike most "modern" GUIs that have stripped away pseudo-3D buttons, rounded corners and COLOR. I have been in the business since the late 1980s, and I can remember the days when we cherished the industry embracing color. 16 in the beginning, then 256, then true color. So when any software presents itself to me as "modern" with a mostly monochrome GUI I feel catapulted back to the 1980s... For me, monochrome is not modern, its outdated...

[Villeroy]

LO does not use ribbons by default and the icon themes always include something colorful. They only messed up the hierarchical menues to some degree and they offer some type of ribbon GUI, just in case you really want that.

[hman2]

I have now migrated to 4.1.8, and I still do not get a "Report Navigator", only the normal Navigator, that doesn't deal with reports at all. Again, I have checked whether it's just a missing icon on the standard toolbar, but even the dialogs to customize toolbars do not carry a Report Navigator, and it shows in no menu or preference/preset. Except for the Extensions Manager. There it is: Oracle Report Builder 1.21. That is the ONLY difference is see when I compare with my OO before adding the report builder. It seems to do nothing, therefore I opened a new topic for it: viewtopic.php?f=42&t=103743&p=502131#p502131

[Villeroy]

OpenOffice 4.1.x is the same since 2014. No major changes at all. Nobody did any work on the Base component. It is very unlikely that you note any changes after upgrading from 4.1.x to 4.1.y unless you suffered from one of the very few bugs that have been fixed since 2014.
If you have old style reports, just replace them with report builder reports. You may edit them after deactivating the extension and restart. Old style reports are difficult to edit anyway. It's always easier to replace them.

[hman2]

If you have old style reports, just replace them with report builder reports.

I will NOT throw away one week of labour that went into layouting them. This is NOT an option. Also I dislike the idea to call reports that were created just two weeks ago with the built-in wizard, that came with a code dated 2018, "old", especially when comparing them with reports created with code dated 2010 that is merely an extra that has to be added later-on!

[Villeroy]

Disable the report builder, restart OpenOffice and follow the advice of Drew Jensen (2008). The report is a Writer document with a table. Data settings are stored in hidden form controls. You have to edit the report settings like you would edit the properties of a form.

Edit: It is not necessary to disable the extension. You can still manually edit the report design. It is just a Writer document

[Villeroy]

I just found a database with an old style report based on a query with 4 columns where I added one additional column.
I open the report for editing with LO 7.
Append a column to the placeholder table.
Create an additional user field for the new column. See Insert>Fields... tab:Variables > User Fields

The form navigator shows several hidden fields where I added the additional name to the list of field names.
CommandType = 1 (means "Query")
Query = qReport (name of my query)
Command = <empty> (would be the place for a SELECT statement with command type 3)
GroupFieldNames = <empty> (no groups in this report)
FieldNames = Date;Person Name;Category;Value;VAT (where VAT is the new field)
Sorting = <empty> (no sorting in this report)
RecordFieldNames = Date;Person Name;Category;Value;VAT (not sure why there are two field lists, may be one for the query columns and the other for the respective report labels. They are identical in my case)

[hman2]

Thanks!

But how do I access the Report as such? I don't see a standalone document with it, it is part of the .odb database file. When I open that (most of the time through the picklist, but a double click on the file does it, too) I see a tab with the table, an empty one with queries, another empty one with forms, and one with the report...

Thanks for your help.
hman2

[Villeroy]

Right-click>Edit lets you edit the design of a table, query, form or report

[hman2]

Yes, but there I can only change the layout of the report, not the query lying underneath...

[Villeroy]

Well, then right-click>Edit the query.