Existing Base reports won't open in LibreOffice 3.5
I have many (painstakingly designed) reports originally done in Oo 3.3 using the ('old style') report wizard (which I used because I could not get the result I wanted with the Report Designer). All my reports run fine in LO 3.45, but NOT in LO 3.5.
In LO 3.5 when I click on a particular report to run it, the expected window saying "Connecting to data source.... Importing data...." opens for a brief instant, then disappears, leaving just the window with the report template layout showing...but no report is ever generated. Note: All the queries my reports are based on run perfectly (in LO 3.5) so it doesn't seem to be a database connection problem. As a further test, I set up a new report (again using the 'old-style' wizard) but this time using LO 3.45, and it runs fine, but it still does not run in 3.5.
Does anyone else have this problem? (I'm using LO on Mac OS 10.6)
1. Open your legacy report for EDITING in the usual way (right-click and choose 'Edit').
2. Now open the FORM NAVIGATOR (button).
3. Under the 'Report source' item you should see these controls - CommandType, QueryName, Command, GroupFieldNames, FieldNames, RecordFieldNames. If there is NOT an item named 'Sorting', you must add it. I found it 'missing' in my legacy reports (created in OO), and when I added it, the reports run properly! (Perhaps this 'Sorting' hidden control has been added at some stage of LO development?)
4. To add the 'Sorting' item, just do these steps:
- Right-Click on the enclosing item 'ReportSource'
- Under the NEW item pop-up menu select 'HiddenControl', and a new control will be created in the end of the list of existing controls (probably under the item 'RecordFieldNames').
- Now, change the name of this new control to 'Sorting'.
- To do this, right-click on the new item, and choose PROPERTIES from the pop-up menu.
- In the resulting window/pane, change the (Hidden Control) name to 'Sorting' (type exactly - no quotes, and with a capital 'S') and close the window.
- You should now see an item called 'Sorting' at the end of your list of hidden control for your report source item.
- Close the Form Navigator window and SAVE the report.
5. Your legacy report should now run/open as it used to under OpenOffice.
Brilliant solution!
My 'old' reports are running again.
Thanks, you saved my day!
New user, fresh from Access. My old report did not transfer across, so I have created a new one in Base, using the Report Wizard. This would not open from the Reports screen - it produced a blank box and Base crashed - but could be achieved from menus. Eventually I managed to produce something like the layout I wanted, which uses data from a query (which does work). However, the report will not work; requesting it to run also produces a blank box and crashes Base (and Libre).
Do Base reports actually work in Libre 3.5? I have no experience of earlier versions which, according to other users of this forum, appear to work better than 3.5
Thanks for this, Frofa, but I'm no further forward. I can copy the command line into (I assume) the 'Value' box under Command, but even though I save the file, running the report still crashes LO and when I go in again the value field is once again empty.
I have previously used the edit facility - it was the only way to get the layout I wanted as the wizard did not offer that possibility - could this be the problem? Maybe I should build a new report using only the wizard and, if that runs, then try to alter the layout. Does that sound logical?
Chris
When you say 'crash', do you mean LO actually crashes, or simply that the report is not generated (as in my original description of the problem)? If it's an actual crash, it might mean you have some other kind of problem with your LO installation -- and, for example, if an actual crash occurred BEFORE you saved the ODB container file (even though you did save your report) might explain why the PROPERTIES/VALUE field is still blank when you review this control-field. Have you tried making a very simple report, say with just a single data field to see if that works? Do let us know how you go.
As far as the second part of your comment goes, I mostly use the LRD (I guess that's what you mean by the 'report wizard') because I find it offers more flexibility for the fairly complex reports I need than the Report Designer Extension. I usually create a report using the LRD wizard first, save the report, then open it in EDIT mode (I guess as you have done also) and make incremental changes till I get the report I want. Interestingly, any reports I create using the LRD wizard in the current version of LO 3.5.2.2 run perfectly WITHOUT the work-around described in the bug report above (see link in my previous posting) and I also note the PROPERTIES/VALUE field for hidden control COMMAND (under the ReportSource in the FORM NAVIGATOR list) correctly shows the QUERY the report is based on (i.e. it is not blank). So it looks like the bug does not affect my installation. Exactly what version of LO are you using?
Fred
Hi Frofa
Many thanks for the effort you are putting into this. I am using LO 3.5.2.2. Not being very familiar with LO yet, I am just a tad bemused by some of your acronyms. To create a report, I am only offered the Report Wizard, which, as you say, does not give you much scope for layout, but I did indeed create a basic report with the wizard and then edited it using the edit command and by right clicking on it. I think I tried to run it before I edited it, but may be mistaken there.
When I say LO crashed, it merely stops responding when I try to run the new report, leaving an empty box showing on the screen. To get anywhere I have to close LO, reopen it and then recover the database.
I will try out your suggestions (which I should really have thought of myself - slap wrist) and come back with the results, but it may be a day or two as evenings are a bit busy at present.
Thanks again
Chris
LO does in fact have 2 report generating systems (aka 'wizards'):
1. The newer Report Builder Extension (RBE) - see menu Tools>Extension Manager.
2. The older/legacy Report designer (LRD) which is DISABLED by default in LO 3.5, but can re-enabled by disabling the default RBE - see here http://en.libreofficeforum.org/node/49
Reports created/saved from these 2 systems can both be made to run properly in LO 3.5, but the No. 2 method requires the work-arounds as outlined above because of bugs.
The first work-around (I described above) is needed for legacy reports made in OO. The second work-around is (seemingly only sometimes?) needed for NEW reports generated with the No. 2 method under LO 3.5.
Maybe you've seen these Bug Reports....
https://bugs.freedesktop.org/show_bug.cgi?id=47325
and also...
https://bugs.freedesktop.org/show_bug.cgi?id=47473
I use the older/legacy report system because I find it more flexible. However, I note you seem to be using the No. 1 method. So in theory, your reports should run OK without additional fiddling. (At least those done with the RBE do run without tinkering on my setup.) So there still seems to be a mystery about what is causing your problems. If you have a large database, and your underlying query takes a while to run, your report may not be generated for a while and LO may seem to 'hang' during that period. One of my reports takes 4 minutes to run, but it eventually appears! I would also recommend, when you create a report from scratch, that you save it first, then save your DB and exit LO. Then open your DB and run your report and see what happens.
If all else fails, there is yet another alternative - one can make reports by populating external Writer and Calc docs directly from the database (but that's a whole other topic and I haven't tried much of this to date). See here, for example:
http://nabble.documentfoundation.org/How-do-I-link-data-from-a-LibreOffi...
http://user.services.openoffice.org/en/forum/viewtopic.php?f=75&t=18511
One final point, you might want to consider running your LO database in 'file mode' (if you aren't already) which will protect your data in the event of a LO crash. There are well-known risks of data corruption when using the default 'embedded' mode in OO or LO - where your data are stored inside the .odb container file. See here:
http://www.oooforum.org/forum/viewtopic.phtml?t=104415&postdays=0&postor...
http://www.oooforum.org/forum/viewtopic.phtml?t=94068
Let us know how you go. Onward and upward!
Fred
adding "Sorting" to old reports work fine. But what to do, if i need an new report, done with the "old" legacy report designer.
whenever i make a report without sorting, it works. if i make a report with sorting, i get no data at all, only the report template (like frofa is explaining at the beginning).
I use 3.5.2.2 (ubuntu 12.04).
I had to do the Sorting work-around with documents, which i have done before with ubuntu 11.10 (i think it was 3.4.). They are working now fine.
if i make new reports with lrd, the sorting item is here. but i have the problem, that the reports have no data, only the empty template is shown. if i delete the sorting-item, data are there, but they are not sorted ...
Okay, the work around ist working. But the sorting, which i did by making the report, does not work. i have to sort the query, to get the report sorted.
That is really not usefull. So i have to do for every diffrent report a diffrent query ...
before i made a report and deleted it, after i did not need it longer. now i have to make a query for every report.
i hope there is any other solution, or even an other, working software
Thanks to everyone working on this one! I am now slightly clearer about what works and what doesn't. Using the wizard provided I can make a working report showing all the fields I want (i.e. all the ones in the query). What I have not been able to do is to sort the fields into the order I want - in this case the report runs, but does not ask me what key the query should use, and merely shows the report layout in similar format to the edit mode. Also, I cannot alter the layout so that certain fields (those relating to the key that the query uses) appear once in the heading and not many times in the table content. If I try that, LO stops responding!
Now I shall try the RBE. Wait, out; as we used to say in the Army.
Chris
When I insert a chart in a report to give a graphical representation of some query result I can see the chart correctly in edit mode but when I try to run the report I get the following Error:
-SQL Status: S1000
-An error occured while creating the report.
The information of the error reads:
-An exception of type com.sun.lang.WrappedTargetException was caught
-Failed to parse the report
I have used the wizzard to generate the initial report with showing the data in the Header and Detail section.
I then modified the report making Header and Detail sections invisible and by adding the Report Header and Footer.
The chart I inserted in the Report header which shows fine in edit mode.
The report does not run however.
I tried to save the report before running but when I leave the edit mode and run the report the report just shows empty pages.
In version 3.4.5 this was not an issue.
Both 3.5.2 versions (amd64 and i386) of LO behave the same.
This is what I found works using the LRD (legacy report wizard) in LO 3.5.3:
STEPS
1. Create your primary SQL query showing all the fields contained in your report. This is the QUERY the report 'feeds off'.
2. In your query above, make sure the primary sort order (using the ORDER BY command in the SQL) is the one you are using for the GROUP BY (header) in the REPORT.
3. If you don't do this, I found, like you (where you say '…Also, I cannot alter the layout so that certain fields - those relating to the key that the query uses - appear once in the heading and not many times in the table content….'), that the report GROUP HEADER text gets repeated instead of occurring once at the beginning of the GROUP as it should. Of course, you can also add secondary sorts as usual in your query.
4. When you first create your report in the LRD, even though you will select the relevant QUERY you created in step 1 above, you will probably need also to paste the SQL for this query (step 1 above) into the hidden COMMAND field in the report FORM NAVIGATOR window. This is due to the bug mentioned in the discussion above. See this:https://bugs.freedesktop.org/show_bug.cgi?id=47473
5. Hopefully, your report should now behave properly.
I can give you more details if you need. Let us know if this helps you
- Fred
Many thanks Frofa. I got my sorting to work by sorting the query, pasting the query into the Command field and sorting the report (had to sort them both on the same field, which is not ideal), which does not help Judis!
I hadn't thought of using Groups to get my heading organised - as I only have one group - but I'll try it.
Tried to get the RBE into use but it would only load if I logged in as an administrator; I was unable to put any text into a text box (in fact I was unable to enter anything ), and when I went back to a normal user account it wouldn't work!
Chris
Doing a bit more investigation today, this is a summary of what I've found (so far):
1. All LO reports need to be based on an SQL query (whether pre-existing, or generated 'on-the-fly' by the Report Designer Wizard)
2. When building a Report using the LRD, there are 2 situations (specified in the first step of the LRD):
a. Select a TABLE from your database
b. Select a (pre-existing) QUERY from your database (referencing the TABLE above)
3. In the first case (2a above), using LO 3.5, the wizard DOES generate the appropriate query (with the fields and sorting the user has selected), and stores it in the hidden COMMAND control field. But NOTE, there is still a PROBLEM in the case where the Report needs to have GROUP HEADERS (i.e. GROUPED on one of the database fields). In this case, the auto-generated QUERY is incomplete, and the GROUP BY term is omitted (*see NOTES below). This can be fixed by simply editing the QUERY in the COMMAND control field and adding the GROUP BY term. This is effectively another BUG, in my opinion.
4. In the second case (2b above), the wizard either does NOT generate the query and/or does NOT copy it into the COMMAND control field - leaving it BLANK. So it has to be entered 'manually'. This is the 'Blank COMMAND field' bug I mentioned above and elsewhere.
https://bugs.freedesktop.org/show_bug.cgi?id=47473
* NOTES:
1. When I use the LRD in OpenOffice v.3.3 to generate the same report from the same database, the wizard correctly generates the SQL command, as shown in the hidden COMMAND control field, with the correct GROUPING term, and so the report runs properly in OO. (It also also runs properly in LO 3.5 once the hidden 'SORTING' control is added - that's the OTHER bug!).
2. OO also correctly copies the query code into the COMMAND control field in the SECOND case (2b) above (so there is no 'blank Command field' BUG in OO).
I'm not a programmer, and don't have any deep technical understanding of LO and BASE, but it looks to me that how the LRD wizard works has been changed somewhere down the line in LO development, leading to these 'bugs' (which, from what other users have reported, don't seem to affect LO 3.3 - not sure about LO 3.4).
Hope this helps you and Judis - Fred
Frofa; you are really a hero with all the work you have put in. Many thanks. I have finally got the report to work the way I want it - even to managing a heading in a Text box! I managed it last night and, guess what, when I tried it again this morning, it failed. Since then I have done nothing significant to it (change spacing, font size, etc) but now it works again. At last! And, touch wood, consistently.
I have to say that some fairly trivial procedures, like changing the content of a data field, or finding a record with a certain value in a field, are not nearly as easy or quick as in Access, but seem to work eventually. There was a time when I wondered if I was going to have to spend £450 after all, but all seems well now and I'll probably stop hogging this forum.
Mind you, if I could work out how to write a macro to run the report and print it......
Chris
Thanks eremmel for the info and the link. Interesting reading.
Guess I will have to wait until a new revision or go back to 3.4.5.
Sadly, despite the work-arounds for running reports described above, I have had to revert to using LO 3.4.6 because of a serious problem with the display of table information (in the 'data grid') in LO 3.5.X - see here:
http://nabble.documentfoundation.org/Repeating-Display-of-BASE-Table-ent...
http://en.libreofficeforum.org/node/1267
Hopefully, this will be fixed soon.
Hi Fred
Sorry to hear that you are still having problems. I thought I was too, but read on.
I was away for a week. On return, not only was my hard won report not working, but my database appeared to have lost a field (which had happened when I renamed that field). I had re-entered the data, but somewhere it had got lost again. However, all was not lost as my back-up version (thanks to my new external hard drive) still had that field. After eventually working out that not only did I have to copy back the .odb file, but also the .mdb file it was working from, my database was restored. But my report still did not work, and I could not work out why.
I went back to my back-up version to see if that still worked. It didn't, so I thought my only recourse was to create a new report (now having a fair idea of how to do it). I created one, not in the print layout that I wanted, but at least with the right data, but it didn't work. When I saved it, for some reason it got the same name as the query from which it got its data. Now comes the really strange bit. The old report (I only found out because I hit the wrong button) now worked! I renamed the new report 'Rubbish' to make sure I never used it again, but the old report then stopped working again. Renaming the new report to the queryname caused the old report to work again.
Then I realised I had done all this on the back-up copy, so I went back to the primary copy, and now the old report works!
In the days when I used to teach computing, I sometimes recommended to my students that they should spend five minutes a day banging their heads against a brick wall, while repeating 'Computers are completely logical', but now I'm not so sure!
Chris
Hi Chris,
Wow - it seems the Uncertainty Principle reigns! Is THAT what they mean by Quantum Computing?!
Just one thing I need to clarify. When you re-named your field, did you do so in the 'edit table' window (right click on the table name in the table listing) or in the report template itself? That might explain why the report stopped working.
The report document/template itself stores the query (it is based on) INDEPENDENTLY in its hidden command DATA field. So, if you change database field names or the 'original' query itself (in the main database QUERIES window), that does NOT automatically change the query 'embedded' in the report (stored in the hidden COMMAND field). Therefore, if you change the DB field names or the original query, the report may not run or may report the wrong results. Put another way, changing your query in the QUERY WINDOW does NOT dynamically change the report query (a bit counter-intuitive, perhaps?)
Also, a separate 'quirk' I 'discovered' when starting to use the report wizard in OO (applies also to LO) is that if any of the field NAMES (in the column header line) in the report template window (i.e. in edit mode) are deleted, the report will not run. You can rename them by double-clicking on the name and changing the name VALUE in the 'Edit field: Variables' window that opens up. Maybe that's what you are talking about when you said you 'renamed the field'. Of course, it's possible that you have other quite unrelated problems with your database and/or report.
I recall many times having to re-do reports because unexpected things happened (or reports stopped working) after my trial-and-error changes. I soon learned to make a copy of every working report (use copy and paste in the main report pane) and edit the copy, so if things go wrong, I could revert to the last working version. I also scrupulously made a copies of the database before making major changes (so I would have older 'versions' of the DB to go back to if things went awry). Multiple backups are also essential if you are using the default 'embedded' .odb database (as you probably are) because of the real chance of catastrophic data loss. (I have converted most of my databases to 'file mode' to avoid this problem but that's another topic).
Fred
Fred
Fired up today and report not working again! (and crashes LO as before). Went to backup version and report did not work there either, but I fiddled with my non-working dummy report, and, lo and behold, now the report works again despite my not having actually done anything to it! Went back to the primary DB, report still did not work, so I created another dummy nonworking report and now the report works again!
However, I tried opening another DB also transferred from Access. I can open the tables all right and, I think, create queries if I want to, but I am unable to amend field entries or add more records. Any suggestions?
I assume that the default mode is to retain the Access database and access it through LO Base. Does 'file' mode mean that the whole thing is transferred to LO? If so. how does one do that?
Chris
Just a final (?) post on this topic. It looks like the 'legacy reports...absence of Sorting hidden control' bug (see the beginning of this topic) will be fixed in LO 3.5.5
See here: https://bugs.freedesktop.org/show_bug.cgi?id=47325
Thanks Eremmel for this. but have you any suggestions how I can achieve this (or even check it) as, having disposed of my old computer, I can no longer access the Access database.
Chris
Eremmel
Many thanks. My problem is that there is no field in the existing table suitable as a PK, as there are duplicates in all fields. I can create another field as a PK, but, being unable to enter a value, LO will not save the change as a PK cannot have a null value!
Chris
This is getting out of context. But any way:
Try the following: make an other table with the same fields, field-names, field-types as the 'troubled table'. Add an Identity column to it and make it PK. Now insert all data from the trouble-table into this new one and rename both.
500 records later, all is now working nicely! But I am thankful that I did manage to give my biggest database (that with some 50,000 records) a primary key to start with!
Chris
fofa, I just sent this message to chrisgrove....
Interesting I have finally found this solution.
I tried using the Oracle Report Builder but need multiple columns for a continuous table.
Then I went and followed the instruction but it didn't completely work.
I created a new report after uninstalling Rpt Builder. that worked.
Then I realized I was putting the SQL code in the Report Source \ Command - I took out this SQL and pasted it to the Form Properties \ Data tab\ Content type: SQL Command
Pasted to Content field - this works fine.
Funny thing is after changing this and removing the Report Builder.... some of the reports work fine as they were in OO. Now I only needed to put a valid Query in the FormNavigator\Forms\ReportSource\QueryName.
Oh... I can now change the CommandType = 3 (SQL Command)
Then I can paste SQL Commands
into the FormNavigator\Forms\ReportSource\Command Valued
and this works.
I am having this same issue with my Base reports after upgrading to LO 3.5. Symptoms are exactly the same as noted above.
However, I am running Win7 64-bit with Java 1.6.0_31-b05.