[Solved] Report with Data NOT in Rows

Discuss the database features

[Solved] Report with Data NOT in Rows

Postby levcal » Wed Aug 03, 2011 10:58 pm

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
levcal
 
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Postby Villeroy » Wed Aug 03, 2011 11:45 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report with Data NOT in Rows

Postby levcal » Thu Aug 04, 2011 3:34 pm

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
levcal
 
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Postby r4zoli » Thu Aug 04, 2011 4:00 pm

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
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Report with Data NOT in Rows

Postby levcal » Thu Aug 04, 2011 4:56 pm

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

Postby levcal » Thu Aug 04, 2011 7:34 pm

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
levcal
 
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Postby r4zoli » Thu Aug 04, 2011 7:49 pm

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
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Report with Data NOT in Rows

Postby levcal » Thu Aug 04, 2011 8:04 pm

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
levcal
 
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Postby r4zoli » Fri Aug 05, 2011 8:02 am

Try this modified query:
Code: Select all   Expand viewCollapse view
"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
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Report with Data NOT in Rows

Postby levcal » Fri Aug 05, 2011 3:38 pm

I assume you mean under Filter. With that query, I get a blank report again.
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

Postby r4zoli » Fri Aug 05, 2011 7:29 pm

Not to filter.
You must add to the report source query.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Report with Data NOT in Rows

Postby levcal » Fri Aug 05, 2011 7:47 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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
levcal
 
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Postby r4zoli » Fri Aug 05, 2011 8:01 pm

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
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Report with Data NOT in Rows

Postby levcal » Fri Aug 05, 2011 8:03 pm

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
levcal
 
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Postby r4zoli » Fri Aug 05, 2011 8:14 pm

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
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Report with Data NOT in Rows

Postby Villeroy » Fri Aug 05, 2011 8:19 pm

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".
report.SQL.Source.png
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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report with Data NOT in Rows

Postby levcal » Fri Aug 05, 2011 8:32 pm

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
levcal
 
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows

Postby Villeroy » Fri Aug 05, 2011 8:48 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report with Data NOT in Rows

Postby levcal » Fri Aug 05, 2011 8:54 pm

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

Postby levcal » Fri Aug 05, 2011 9:17 pm

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
levcal
 
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm

Re: Report with Data NOT in Rows [SOLVED]

Postby Villeroy » Fri Aug 05, 2011 9:20 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report with Data NOT in Rows [SOLVED]

Postby levcal » Fri Aug 05, 2011 10:09 pm

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
levcal
 
Posts: 12
Joined: Wed Aug 03, 2011 10:49 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 5 guests