Bug 50132 - Provide as-is order for groups in Reports
Summary: Provide as-is order for groups in Reports
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.5.3 release
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Reports
  Show dependency treegraph
 
Reported: 2012-05-19 15:41 UTC by Ulf Zibis
Modified: 2020-10-22 15:46 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Sorting reports in the GUI (51.89 KB, application/pdf)
2012-07-12 08:45 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Ulf Zibis 2012-05-19 15:41:31 UTC
- let's have a query where rows are already in correct order:
SELECT "Categories"."Name" AS "Category", "Projects"."Name", "Projects"."Description" FROM "Projects", "Categories" WHERE "Projects"."CatID" = "Categories"."ID" ORDER BY "Categories"."ID" ASC
- group the report form above query by "Category"
--> the report can only be ordered alphabetically from "Category" which is wrong order.
Comment 1 Robert Großkopf 2012-07-08 10:35:53 UTC
If you want to order a report you have to group the report with the field you will have ordered. Your filed must be "Categories"."ID" - which must be a filed in the SQL-code.
You could put the field "Category" in the group-area of "ID". Then you have ordered by "ID" and shown only the "Category".
I have set the Importance to "enhancement", because it works in this way.
And set the Status to "new", because it can only be ordered by the field of the group.
Comment 2 Ulf Zibis 2012-07-09 19:01:55 UTC
(In reply to comment #1)
> You could put the field "Category" in the group-area of "ID". Then you have
> ordered by "ID" and shown only the "Category".

Thanks for the advice. But what to do, if theoretically there is an order, which can not be expressed by 'ascending' or 'descending' of an existing field?
In that case, an enforced order IMO is a defect.

> And set the Status to "new", because it can only be ordered by the field of the
> group.

I would say: ... because post/re-ordering by the report-builder can not be disabled.
Comment 3 Robert Großkopf 2012-07-11 06:02:55 UTC
How would you make a sort statement without a field, which must be sorted? You only have to show this field in the query or SQL-Code. Ist must not be used to display anything in the report. It must only be used for sorting.
The only problem I see is, that you can not get a report with non-graded groups. But I don't know, if anybody would need such groups.
Comment 4 Ulf Zibis 2012-07-11 13:47:01 UTC
(In reply to comment #3)
> How would you make a sort statement without a field, which must be sorted?

Yes, you can't, but one could have a data source with no ordering field, but wants to create a report, ordered as is. Examples:
- table with given order
- imported/bind spread sheet
- query form external db where user has no access to create additional query with ordering field

Regardless of above, in any database operations, the "order by" facility is optional, why should it be mandatory in report builder?

> The only problem I see is, that you can not get a report with non-graded
> groups. But I don't know, if anybody would need such groups.

See above + why should user create a "duplicate" query additionally to a still needed query without the ordering field?
Comment 5 Robert Großkopf 2012-07-11 19:35:45 UTC
Have you tested the sorting with "analyse sql: no"?

https://bugs.freedesktop.org/show_bug.cgi?id=33240

Seems to be the same idea in another way.
Comment 6 Lionel Elie Mamane 2012-07-11 20:55:54 UTC
This is a reasonable request from an "advanced user's" point of view, I've at times wished for a similar thing.

I'm not sure how to present it UI-wise, though.

1) Separate "grouping" and "sorting" in different dialogs?

   I think that is a bad idea, too confusing for non-expert user,
   that one has to put the same field in sorting *and* in grouping.

2) A boolean (yes/no) option on each group/sort field: sort yes/no?
   In that way, one can do grouping on a field without requesting sort
   on that field, and the UI is relatively clear.

   But what would happen if one enters the following:

   GROUP ON field foo / no sort
   SORT BY field bar

   Does it do the same as:

   SORT BY field bar
   GROUP ON field foo / no sort

   because the whole query is sorted by "bar"?
   I'm not too eager, confusing.

   Does it first group by "foo" and *within* each group,
   it sorts by "bar"?



Indeed a work-around now is to include the ID in the result query, but sim
Comment 7 Lionel Elie Mamane 2012-07-11 21:00:01 UTC
This is a reasonable request from an "advanced user's" point of view, I've at
times wished for a similar thing.

I'm not sure how to present it UI-wise, though.

1) Separate "grouping" and "sorting" in different dialogs?

   I think that is a bad idea, too confusing for non-expert user,
   that one has to put the same field in sorting *and* in grouping.

2) A boolean (yes/no) option on each group/sort field: sort yes/no?
   In that way, one can do grouping on a field without requesting sort
   on that field, and the UI is relatively clear.

   But what would happen if one enters the following:

   GROUP ON field foo / no sort
   SORT BY field bar

   Does it do the same as:

   SORT BY field bar
   GROUP ON field foo / no sort

   because the whole query is sorted by "bar"?
   I'm not too eager, confusing.

   Does it first group by "foo" and *within* each group,
   it sorts by "bar"?
   IMHO, it seems natural from the user's POV,
   but it would be delicate to implement,
   unless LibO does the sorting itself;
   I don't think it would be possible to delegate
   the sort to the database engine in this scenario.

3) Global switch "do not sort, only group"?
   Not sure how to present it UI-wise.
   Feels too ad-hoc.



Indeed a work-around now is (in the given example)
to include the ID in the result query,
not show the ID anywhere in the report,
sort & group by ID,
show the Category (name) in the "group by ID"
header / footer / whatever.
Comment 8 Robert Großkopf 2012-07-12 08:45:38 UTC
Created attachment 64135 [details]
Sorting reports in the GUI

I have added some ideas where to change the GUI to get the wished behaviours. But the best way could for easy programming the sql-code could be:
Put away "Analyze SQL command", make a temporary table, when "Content type" isn't "Table". Then you can add filter, sorting … It will make it easyer to enhance the report-builder for 
https://bugs.freedesktop.org/show_bug.cgi?id=51807
Comment 9 Ulf Zibis 2012-07-12 12:38:03 UTC
(In reply to comment #8)
> Created attachment 64135 [details]
> Sorting reports in the GUI
>
> Sorting should have three possibilities: Ascending, Descending and No.

+1

Imagine following data source as corner case:

Town   | Name
Town1    Name1
Town1    Name2
Town1    Name3
Town2    Name4
Town2    Name5
Town1    Name6
Town3    Name7
Town3    Name8

I would say, the report, unsortet grouped by Town, should then print:

Town: Town1
   Name: Name1
   Name: Name2
   Name: Name3
Town: Town2
   Name: Name4
   Name: Name5
Town: Town1
   Name: Name6
Town: Town3
   Name: Name7
   Name: Name8
Comment 10 Robert Großkopf 2012-07-12 14:51:19 UTC
(In reply to comment #9)
> (In reply to comment #8)
> > Created attachment 64135 [details]
> > Sorting reports in the GUI
> >
> > Sorting should have three possibilities: Ascending, Descending and No.
> 
> +1
> 
> Imagine following data source as corner case:
> 
> Town   | Name
> Town1    Name1
> Town1    Name2
> Town1    Name3
> Town2    Name4
> Town2    Name5
> Town1    Name6
> Town3    Name7
> Town3    Name8
> 
> I would say, the report, unsortet grouped by Town, should then print:
> 
> Town: Town1
>    Name: Name1
>    Name: Name2
>    Name: Name3
     Name: Name6

When you group a table every field with the same value will be hold together. "Town1" couldn't appear in 2 groups.
 
> Town: Town2
>    Name: Name4
>    Name: Name5

> Town: Town3
>    Name: Name7
>    Name: Name8

Town1 must not be the first, wenn it will be ordered by "Town" - but all "Name" of "Town1" must be shown in one group. That is, what SQL- GROUP BY means.
Comment 11 Ulf Zibis 2012-07-12 20:44:28 UTC
(In reply to comment #10)
> When you group a table every field with the same value will be hold together.
> "Town1" couldn't appear in 2 groups.

This suggestion was born by the by the assumption, that the report builder would serially process the incoming records from a stream. If it would first buffer all records before grouping, yes, then it's possible, the pricessing could happen as you say. Actually to me it doesn't matter. It was just mentioned to theoretically legitimate such output from the report builder, if it doesn't buffer internally.

> Town1 must not be the first, wenn it will be ordered by "Town" - but all "Name"
> of "Town1" must be shown in one group. That is, what SQL- GROUP BY means.

I would say "... when it will be grouped by ...", right?

I was not sure, if the report builder internally processes a SQL- GROUP BY command.
Comment 12 Alex Thurgood 2015-01-03 17:39:11 UTC Comment hidden (no-value)
Comment 13 mhonline 2020-10-22 15:45:46 UTC
Hello

This is somehow related to this:
https://bugs.documentfoundation.org/show_bug.cgi?id=64377

mh