[Solved] Report with Data NOT in Rows

Discuss the database features
Post Reply
levcal
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

[Solved] Report with Data NOT in Rows

Post 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.
Last edited by levcal on Fri Aug 05, 2011 9:18 pm, edited 1 time in total.
OpenOffice 3.2.1 on Debian Squeeze
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report with Data NOT in Rows

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
levcal
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Post 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?
OpenOffice 3.2.1 on Debian Squeeze
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Report with Data NOT in Rows

Post 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
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
levcal
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Post 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.
OpenOffice 3.2.1 on Debian Squeeze
levcal
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Post 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.
OpenOffice 3.2.1 on Debian Squeeze
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Report with Data NOT in Rows

Post 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.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
levcal
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Post 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."
OpenOffice 3.2.1 on Debian Squeeze
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Report with Data NOT in Rows

Post by r4zoli »

Try this modified query:

Code: Select all

"Latest Transaction ID by Inventory Number"."Inventory Number" LIKE '%' || :Enter_Inventory_Number  || '%' 
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
levcal
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Post by levcal »

I assume you mean under Filter. With that query, I get a blank report again.
OpenOffice 3.2.1 on Debian Squeeze
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Report with Data NOT in Rows

Post by r4zoli »

Not to filter.
You must add to the report source query.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
levcal
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Post 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"
OpenOffice 3.2.1 on Debian Squeeze
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Report with Data NOT in Rows

Post by r4zoli »

The parameter query work only in UI mode, and not in direct SQL mode.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
levcal
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Post 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.
OpenOffice 3.2.1 on Debian Squeeze
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Report with Data NOT in Rows

Post by r4zoli »

You uses OOo 3.2.1 on debian from debian source?
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report with Data NOT in Rows

Post 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.
Screenshot of an arbitrary report based on a parsed SQL statement.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
levcal
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Post 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.
OpenOffice 3.2.1 on Debian Squeeze
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report with Data NOT in Rows

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
levcal
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Post 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.
OpenOffice 3.2.1 on Debian Squeeze
levcal
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Post 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.
OpenOffice 3.2.1 on Debian Squeeze
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report with Data NOT in Rows [SOLVED]

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
levcal
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows [SOLVED]

Post 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.
OpenOffice 3.2.1 on Debian Squeeze
Post Reply