OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Help much needed: Data does not load in reports

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
query
Newbie
Newbie


Joined: 04 Feb 2007
Posts: 3

PostPosted: Sun Feb 04, 2007 1:17 pm    Post subject: Help much needed: Data does not load in reports Reply with quote

First of all, here is my problem:
When I try to create a report from a certain query, no data gets loaded into the report when I open it, it only shows the blank template. I do not get any error message. However, I am able to create reports for other queries.

I need your help really bad: What could I try? Where can I modify the SQL statements that Base uses to create the report or how can I find possible errors? I just don't get how Base creates its reports, I can't find any entries for data sources, etc. Does anybody know some details about the report features internals?

So here's some background:
I have a database with about 1,000 entries, which are categorized into regions, sections etc. which have their respective own tables. I then use views to merge the data, I write them directly in SQL because otherwise Base messes them up. Same thing for the queries.

The query that is not working is based on a view that has UNION statements, LEFT OUTER JOINs and a couple of other more sophisticated features, however so do some of the other views I have and those work well with the report wizard. It is also fairly complex, i.e. it is based on several views which in turn are based on other views.

I would really appreciate any kind of help how to get the report working, even if I have to change around with it's internals. Explanations of the internals of the Base report tool would also be much appreciated. I would even consider creating a report from scratch (without the wizard) if that is somehow possible.

Thanks a lot!
Back to top
View user's profile Send private message
query
Newbie
Newbie


Joined: 04 Feb 2007
Posts: 3

PostPosted: Sun Feb 11, 2007 2:36 am    Post subject: workaround Reply with quote

Well, I found a workaround myself, I'll post it just in case anyone is having the same problem:
I simply created a view from my query and based the report on that, it now loads all the data as it should. It also works if you then create another query on that view that simply uses SELECT * FROM name_of_view.

Well, it is working as expected now, but nonetheless I hope they'll soon be able to fix Base. I think it's got a lot of potential, but it seems that the developers are not putting enough effort into it. Writer seems to be pretty solid by now and so does Calc (I haven't gotten too much experience with Impress, etc.), so they should really be trying to put Base forward now.

Anyway, just wanted to say that OpenOffice is great, but Base could really need some improvement (especially because there are good cross-platform OpenSource replacements for it).
Back to top
View user's profile Send private message
Sembazuru
General User
General User


Joined: 25 Mar 2006
Posts: 10
Location: West Chester, PA

PostPosted: Fri Mar 16, 2007 11:57 am    Post subject: Partial data inport into report Reply with quote

I have a similar problem. On some of my reports, some of the data isn't imported into the report. I have the reports grouped on 1 field, and that field data is imported, but there is no data there. There should be, I can see the data when directly looking at the query.

I tried your method of creating a view from the query, and then creating a report based on the view, no change in the results.

The missing data always seems to be on the first page of the report, and lower on the page than my screen shows when the report is initially generated. Some forms do this, others don't. I really don't know what is going wrong here.

System Specs: OOo 2.1 running on WinXP SP2, JRE Sun 1.5.0_11
Back to top
View user's profile Send private message AIM Address
sean0michael
Newbie
Newbie


Joined: 20 Oct 2005
Posts: 2

PostPosted: Sat Dec 01, 2007 9:36 pm    Post subject: Same Problem Reply with quote

I am currently having the same issue with OO.o 2.3. My Query is fairly complex, but this post was the most helpful for me. My problem was the same -- my report returned no data from an error-free query. The query returns data just fine, but the report wouldn't pick it up at all. The report would contain just a single line with the Ut Wisi for text and 9876.54 for numbers

Some of the complex elements of my query:
    Sum
    Average
    (Column 1 + Column 2) Average
    Sum / Sum

In some cases, the query will return a 1.#NAN where I am dividing by zero in the Sum/Sum column. I thought perhaps one of those elements might be causing the hiccup. But creating a query to SELECT * FROM problematic_query. I'll post the query SQL here for anyone curious or with more knowledge than me. As you might guess, this is about a college basketball team statistics for the season.

Code:
SELECT "PlayerTbl"."PlayerName",
SUM( "StatsTbl"."STATTP" ) AS "Total Points",
AVG( "StatsTbl"."STATTP" ) AS "Points Per Game",
SUM( "STATREBO" + "STATREBD" ) AS "Rebounds",
AVG( "STATREBO" + "STATREBD" ) AS "Rebs Per Game",
SUM( "STATFG" ) / SUM( "STATFGA" ) AS "FG %",
SUM( "STAT3FG" ) / SUM( "STAT3FGA" ) AS "3-PT %",
SUM( "StatsTbl"."STATBLK" ) AS "Blocks",
SUM( "StatsTbl"."STATS" ) AS "Steals",
SUM( "StatsTbl"."STATTO" ) AS "Turn-Overs",
AVG( "StatsTbl"."STATMIN" ) AS "Minutes"
FROM "StatsTbl", "PlayerTbl"
WHERE ( "StatsTbl"."STATPLAYERNO" = "PlayerTbl"."PlayerID" )
GROUP BY "PlayerTbl"."PlayerName", "PlayerTbl"."PlayerID"
ORDER BY "PlayerTbl"."PlayerName" ASC, "PlayerTbl"."PlayerID" ASC[/i]
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group