Page 1 of 1
[Solved] Report with Data NOT in Rows
Posted: Wed Aug 03, 2011 10:58 pm
by levcal
I'm trying to duplicate a report I had in an old Access database. The query it's based on will never return more than one record, and I need to display a LOT of data, so I want to spread the data throughout a full-page layout. I can't seem to make it do anything but display data in table rows, which makes it impossible for me to fit everything I need to show. I've tried copying and pasting from the cells of the table the wizard gives me, and that doesn't work. I've tried copying and pasting the whole table and then removing all but one field from each copy, and that doesn't work either. I've tried looking for a way to add fields manually, but I don't see how to do it. The report creator in Access seemed to be a lot more flexible. I got through building the tables, queries, forms and other reports all by myself, but this report has me beat.
Re: Report with Data NOT in Rows
Posted: Wed Aug 03, 2011 11:45 pm
by Villeroy
Create a pivot table in Calc.
Calc menu:Data>Pilot>Start...
Create from registered data source, specify the source name and the name of the table or query.
The rest is similar as in Excel.
Re: Report with Data NOT in Rows
Posted: Thu Aug 04, 2011 3:34 pm
by levcal
Thank you, Villeroy, but that doesn't seem to solve my problem. First of all, it doesn't display any values. It seems to be trying to sum values, and I just get "#VALUE!". I'm not really familiar with the DataPilot, so I may be doing something wrong, but it doesn't really matter, because I need a user to be able to just click on something in the database to generate the report as needed. Maybe there's a way to generate a Base report from this that I don't know about, but involving Calc will make this too complex for my users.
Any other suggestions?
Re: Report with Data NOT in Rows
Posted: Thu Aug 04, 2011 4:00 pm
by r4zoli
Try
Oracle report builder, it is more flexible, than the report wizard, and more customizable, but it is not perfect.
Some documentation:
http://wiki.services.openoffice.org/wik ... umentation
Re: Report with Data NOT in Rows
Posted: Thu Aug 04, 2011 4:56 pm
by levcal
I do believe we have a winner. I just installed Oracle Report Builder, and it looks like exactly what I need. I'll play with it a bit later, and assuming it does what it looks like it will, I'll come back and update the thread as solved. Thanks so much, r4zoli.
Re: Report with Data NOT in Rows
Posted: Thu Aug 04, 2011 7:34 pm
by levcal
This is very close to being the answer. I love Report Builder. It gives me all the layout flexibility I need. The only problem is, while I can get it to run a report on a table, and I can get it to run a report on most of my queries, it will NOT run a report on a query that requests user input.
In this instance, the query asks the user to enter a four digit inventory number (LIKE :Enter_Inventory_Number). When I run the query directly or when I run a report based on it that I generated with the wizard, it does this just fine. With Report Builder reports, it asks me nothing, and gives me a blank page (which I guess is the right answer for no inventory number). Is there some trick to making it ask? I don't see anything related in the documentation.
Re: Report with Data NOT in Rows
Posted: Thu Aug 04, 2011 7:49 pm
by r4zoli
Check the report source. When the wizard creates ORB report converts query to SQL commands.
Switch back to query, Open report navigator with F5, selet Report, on properties Data Tab, set source query with parameter query. If you run report now the parameter window opens.
Re: Report with Data NOT in Rows
Posted: Thu Aug 04, 2011 8:04 pm
by levcal
No, that seems to be right. Content Type is set to Query and Content is set to the correct query. Analyze SQL Command is set to Yes, though I tried it on No, and it didn't have an effect. I also tried putting ( "Latest Transaction ID by Inventory Number"."Inventory Number" LIKE :Enter_Inventory_Number ) into Filter, but that returns "Failed to process the report."
Re: Report with Data NOT in Rows
Posted: Fri Aug 05, 2011 8:02 am
by r4zoli
Try this modified query:
Code: Select all
"Latest Transaction ID by Inventory Number"."Inventory Number" LIKE '%' || :Enter_Inventory_Number || '%'
Re: Report with Data NOT in Rows
Posted: Fri Aug 05, 2011 3:38 pm
by levcal
I assume you mean under Filter. With that query, I get a blank report again.
Re: Report with Data NOT in Rows
Posted: Fri Aug 05, 2011 7:29 pm
by r4zoli
Not to filter.
You must add to the report source query.
Re: Report with Data NOT in Rows
Posted: Fri Aug 05, 2011 7:47 pm
by levcal
Nope. I still get a blank report, but running the query directly still works with that change. If it's useful, here's the query...
Code: Select all
SELECT "Latest Transaction ID by Inventory Number"."Inventory Number", "Chairs"."Manufacturer", "Chairs"."Name", "Chairs"."Model Number", "Chairs"."Textile Name", "Chairs"."Textile Color", "Chairs"."Wood Finish", "Chairs"."Metal Finish", "Chairs"."Shell Color", "Chairs"."Notes", "Log"."Location", "Log"."Date", "Log"."Contact Name", "Log"."Contact Phone", "Log"."Requested By", "Log"."Authorized By" FROM "Log", "Chairs", "Latest Transaction ID by Inventory Number" WHERE "Log"."Inventory Number" = "Chairs"."Inventory Number" AND "Latest Transaction ID by Inventory Number"."maxid" = "Log"."ID" GROUP BY "Latest Transaction ID by Inventory Number"."Inventory Number", "Chairs"."Manufacturer", "Chairs"."Name", "Chairs"."Model Number", "Chairs"."Textile Name", "Chairs"."Textile Color", "Chairs"."Wood Finish", "Chairs"."Metal Finish", "Chairs"."Shell Color", "Chairs"."Notes", "Log"."Location", "Log"."Date", "Log"."Contact Name", "Log"."Contact Phone", "Log"."Requested By", "Log"."Authorized By" HAVING ( ( "Latest Transaction ID by Inventory Number"."Inventory Number" LIKE '%' || :Enter_Inventory_Number || '%' ) )
The query "Latest Transaction ID by Inventory Number" that that one calls looks like this...
Code: Select all
SELECT "Chairs"."Inventory Number", MAX( "Log"."ID" ) AS "maxid" FROM "Log", "Chairs" WHERE "Log"."Inventory Number" = "Chairs"."Inventory Number" GROUP BY "Chairs"."Inventory Number"
Re: Report with Data NOT in Rows
Posted: Fri Aug 05, 2011 8:01 pm
by r4zoli
The parameter query work only in UI mode, and not in direct SQL mode.
Re: Report with Data NOT in Rows
Posted: Fri Aug 05, 2011 8:03 pm
by levcal
Well, I built these queries with the UI. I was just using the SQL as an easy way of showing you what I was dealing with.
Re: Report with Data NOT in Rows
Posted: Fri Aug 05, 2011 8:14 pm
by r4zoli
You uses OOo 3.2.1 on debian from debian source?
Re: Report with Data NOT in Rows
Posted: Fri Aug 05, 2011 8:19 pm
by Villeroy
It does not matter how you created the query. The storage location makes the difference in this particular case. Do not store your parameter query as a query in the documents queries section. Store it in the report itself. Copy the SQL string from your stored query into the report builder's data source and change the source type from "Query" to "SQL command" with option "Analyse SQL command".
- Screenshot of an arbitrary report based on a parsed SQL statement.
Re: Report with Data NOT in Rows
Posted: Fri Aug 05, 2011 8:32 pm
by levcal
Villeroy: I'd actually tried that before, but I just tried it again to be sure, both with my original query, and the slightly edited one with r4zoli's suggested change, and both give me a blank page as before. I made this change to my existing report since when I try to make a new one with this method, the only thing that shows up in the Add a Field window is "Enter_Inventory_Number."
r4zoli: I'm not sure what you mean by "from debian source," but yes, I'm using 3.2.1 on Debian 6 (Squeeze) using XFCE. I installed from a disc I burned using an ISO from the Debian site.
Re: Report with Data NOT in Rows
Posted: Fri Aug 05, 2011 8:48 pm
by Villeroy
Your query returns rows when you test it but the report does not show anything when using the same query with the same parameters as tested? That could be a bug in the extension or in your office suite.
The office suite downloaded from
http://openoffice.org or libreoffice.org is different from what the Linux distributors put in their repositories. The latter compilations use to be more buggy than the original ones. I have no idea why the Linux guys can not distribute the office suite as is. Complex extensions like the report builder are developed and tested against the vanilla versions from the web-site. It may fail in the context of an "integrated" Linux suite. It is really hard to tell. I think that r4zoli is the person with the maximum experience in respect to the report builder on different platforms. I gave up using it. Nice looking but too complex for my needs.
Re: Report with Data NOT in Rows
Posted: Fri Aug 05, 2011 8:54 pm
by levcal
Yes, that's right. Running the query directly or via a wizard-generated report works fine, and I've been doing all my testing with the same inventory number so as not to introduce other variables.
I have access to a Windows machine running OpenOffice installed from the web site. I'll try out the file on that machine and see what happens.
Thank you both so much for the time you've spent on this.
Re: Report with Data NOT in Rows
Posted: Fri Aug 05, 2011 9:17 pm
by levcal
Unbelievable. It works fine on the Windows machine, so I have to assume you're right. The Debian folks broke OpenOffice in some subtle way. Very nice catch.
I suppose uninstalling and reinstalling should fix it, but either way, the problem that brought me here is "fixed." Thanks again, guys.
Re: Report with Data NOT in Rows [SOLVED]
Posted: Fri Aug 05, 2011 9:20 pm
by Villeroy
Just in case ...
[Tutorial] Installing OOo on Ubuntu, Debian and Co.
Oh, and the report builder version is bound to the office version. You've got to upgrade both.
Re: Report with Data NOT in Rows [SOLVED]
Posted: Fri Aug 05, 2011 10:09 pm
by levcal
FYI, for anyone reading this in the future, I just installed LibreOffice 3.4.2, which has Report Builder out of the box, and it works like a charm.