[#XRP-107] Distinct rows and Dynamic selection of columns to database based on the configure columns. - Cocoondev.org Issue Tracker

Issue Details [XML]

Key:	XRP-107
Type:	New Feature
Status:	Resolved
Resolution:	Fixed
Priority:	Minor
Reporter:	Marc Portier
Votes:	0
Watchers:	1

Operations

If you were logged in you would be able to see more operations.

xReporter

Distinct rows and Dynamic selection of columns to database based on the configure columns.

Created: 03/Mar/06 05:21 AM Updated: 12/Sep/07 08:51 AM

Component/s:

xReporter

Affects Version/s:

None

Fix Version/s:

1.3

Description

When discussing some ideas behind ~~XRP-103~~ there was a natural question for having 'distinct' row values:
http://lists.cocoondev.org/pipermail/xreporter/2006-February/002569.html

Some more thoughts around this:

Currently the list of selected columns is always expected to be fixed in the statement
--> so adding the literal 'distinct' to your report doesn't change a thing: ignoring the selected viewable columns leeds to the same sql statement being executed

--> this has also an impact on data-volumes being read and transferred (since we ignore the selected column list we read more columns then we are showing)

Starting to think about a way to support this:

- we might introduce a <visible-columns ref="*"/> <visible-columns ref="colname1 , colname2" /> element into the sql statement that should dynamically look into the set

- also we might want to have certain columns by default in a non-selected state --> @default-selected="hidden"

- and/or, want to force people to make their column-selection at the end of the flow, but before showing the first output --> introducing a special flow-step, might be a common redirect in fact so people could be forced into qbe or whatnot as well...
(this idea comes from the 'performance' and download remark earlier: if we want to avoid selecting unnecesary columns from the database, it also makes sense to enforce making those decisions before performing the first db-request)

There are however quite some other areas that are going to be affected by this as well.

- order-by clauses might not be meaningful any more, and break legal sql syntax constraints

- same remark about any other column reference made: having-clause, grouping-expressions, ...

- qbe display might become awkward?

other things I'm missing?

All

Comments

Change History

Sort Order:

Comment by Marc Portier [29/Mar/06 03:36 AM]

[ Permlink ]

A first stab at this functionality is committed in revision 665
http://svn.cocoondev.org/viewsvn?rev=665&root=xreporter&view=rev

As mentioned this still misses
- some filtering in the order-by
- updating the schema to validate the new element in the report-definition

See also mail-thread:
http://lists.cocoondev.org/pipermail/xreporter/2006-March/002618.html

Comment by Marc Portier [17/Aug/07 10:25 AM]

[ Permlink ]

suggestion for the 'filterig of order-by':

we could probably just drop the predefined order-by options as soon as some of the fields are excluded from the view?

Comment by Bruno Dumon [12/Sep/07 08:51 AM]

[ Permlink ]

Fixed in SVN rev 707:

* column definition can have an attribute allow-orderby (see also ~~XRP-127~~), which can be true/false/if-selected

* orderby-choice: can now have an attribute "if-columns-selected". If present, the order by choice will only be available if the columns mentioned in that attribute are currently all visible (= selected).

* when the selected columns are changed, the order by configuration will be checked to see if it is still valid for the new column selection, if not it is reset to its default state.

* updated xreporter.xsd with all related changes, including addition of select-column-list.