[Solved] Sorting and grouping with Sun Report Builder

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] Sorting and grouping with Sun Report Builder

Postby ptoye » Mon Dec 29, 2008 6:38 pm

I've been trying to get sorting to work with the SRB with not much success. I want to sort on one field, then (under the sort) group on another 2 (also sorting). And the results I get seem to be quite variable. Sometimes the sorting works, sometimes it doesn't. Sometimes the grouping works, sometimes it doesn't. I know this is a bit vague, but I can't work out any obvious correlation between my sorting/grouping conditions and the results. Sometimes I even get duplicate records, so it looks as if it's getting the SQL a bit wrong.

Any pointers would be most helpful.
Last edited by ptoye on Sun Jan 11, 2009 4:52 pm, edited 1 time in total.
Peter
OO 4.1.1 on MS Windows 7 64-bit
ptoye
 
Posts: 225
Joined: Mon Apr 21, 2008 11:07 am
Location: Reading, England

Re: HOw does sorting and grouping work with Sun Report Builder

Postby QuazzieEvil » Wed Jan 07, 2009 1:46 am

I was going to suggest the Sorting and Grouping Dialog, but obviously you have already tried that. is the data source a Table? Query? or SQL command? you have a query, try sorting at the query level. if using table, try using a query or SQL Command. select SQL command and click on the button with elipsis to open the query builder, so you can build the SQL command easily.
QuazzieEvil
Volunteer
 
Posts: 283
Joined: Tue Dec 04, 2007 6:38 pm
Location: Houston, TX

Re: HOw does sorting and grouping work with Sun Report Builder

Postby ptoye » Thu Jan 08, 2009 1:31 pm

Thanks for the help.

As you suggest, I've been using the "sorting & grouping" menu.

The source is a query (which combines some linked tables, so I can't really flatten it into a single table).

I've tried your suggestion of doing the sorting in the query. It's interesting as the results differ, but none of them are as I want them.

I 'd like to attach a test database, but even a small one is too large (159 Kb zipped) for this BB to accept - if you've a private email address I could send it to you.

Any help welcome. I've flagged it as an issue, but not had even an acknowledgement yet.
Peter
OO 4.1.1 on MS Windows 7 64-bit
ptoye
 
Posts: 225
Joined: Mon Apr 21, 2008 11:07 am
Location: Reading, England

Re: How does sorting and grouping work with Sun Report Builder

Postby ptoye » Sun Jan 11, 2009 4:50 pm

I think I've solved it. There are two separate issues, both undocumented.

Firstly, as pointed out, it seems that the underlying query has to be sorted in the same order as the group/sort on the report.

Secondly, I found that there's a bug in the query mechanism (I suspect in the database machine) which gets very confused if you have two fields with different aliases, but one of the aliases is the same as the name of the other field. Quite legal SQL, but you get the wrong answer! I'll be flagging that as an issue.
Peter
OO 4.1.1 on MS Windows 7 64-bit
ptoye
 
Posts: 225
Joined: Mon Apr 21, 2008 11:07 am
Location: Reading, England

Re: How does sorting and grouping work with Sun Report Builder

Postby TheGurkha » Sun Jan 11, 2009 6:45 pm

ptoye wrote:I'll be flagging that as an issue.


If the error is inthe database engine, it is maintained by the developers: HSQLDB.
Ubuntu 14.10 Utopic Unicorn, LibreOffice Version: 4.3.3.2
Gurkha Welfare Trust
User avatar
TheGurkha
Volunteer
 
Posts: 6482
Joined: Thu Mar 13, 2008 12:13 pm
Location: North Wales, UK.

Re: [Solved] Sorting and grouping with Sun Report Builder

Postby ptoye » Sun Jan 18, 2009 5:52 pm

Apparently there's a bug in the SQL that it generates in certain circumstances. See issue 97560 for details.
Peter
OO 4.1.1 on MS Windows 7 64-bit
ptoye
 
Posts: 225
Joined: Mon Apr 21, 2008 11:07 am
Location: Reading, England


Return to Reporting

Who is online

Users browsing this forum: No registered users and 2 guests