Report View can not group by column
Description
Environment
Attachments
- 19 Dec 2013, 12:53 AM
- 31 Oct 2013, 10:17 PM
Activity
Thomas Bayen December 19, 2013 at 7:44 PM
This was not really an issue but only a bad documented area. A new documentation in the wiki is done. So I close this issue.
Thomas Bayen December 19, 2013 at 7:42 PM
Some time ago I created a documentation about that in the german wiki. I rewrote it a bit today:
Today I did an english translation. Feel free to change it if you know it better or you think it is too google-translatish.
Thomas Bayen December 19, 2013 at 9:45 AM
Thanks for the review. After my patch turned around every single usage of this value and my comment was that the name meaning is turned too I am a bit ashamed. The fact that I used the checkmarks exactly the wrong way is now obvious for me. This issue is no issue.
As you say what stays is a lack of documentation. I will keep this open until I did that.
Carlos Ruiz December 19, 2013 at 12:53 AM
Thanks Thomas, firstly we need to credit properly 2pack (or 6pack?)
With this revamped 2pack I was able to export your sample from demo and import it locally smoothly - and then debug your test case.
During the debugging, found some interesting clues:
Biggest clue was line 559 on DataEngine:
// => Function(Table.Column) AS Column -- function has @ where column name goesWith this comment in mind I changed the function columns to "SUM(@)"
And then as the logic of code for IsGroupFunction is inverse to the description of the field, then I inverted the "SQL Group Function" flags you defined.
And the resulting report (and SQL) is precisely what you expect!
SELECT (SELECT c_elementvalue.value
|| '-'
|| c_elementvalue.name
FROM c_elementvalue C_ElementValue
WHERE fact_acct.account_id = c_elementvalue.c_elementvalue_id) AS AAccount_ID,
fact_acct.account_id AS Account_ID,
Sum(fact_acct.amtacctcr) AS AmtAcctCr,
Sum(fact_acct.amtacctdr) AS AmtAcctDr
FROM fact_acct
WHERE fact_acct.ad_client_id IN( 0, 11 )
AND fact_acct.ad_org_id IN( 50007, 0, 50004, 50005,
50006, 50000, 50001, 50002,
11, 12 )
AND ( fact_acct.fact_acct_id IS NULL
OR fact_acct.fact_acct_id NOT IN (SELECT record_id
FROM ad_private_access
WHERE ad_table_id = 270
AND ad_user_id <> 100
AND isactive = 'Y') )
GROUP BY aaccount_id,
fact_acct.account_id
ORDER BY aaccount_idSo, maybe what we need to do is document properly how to define and use a report view
Thomas Bayen December 18, 2013 at 12:57 PM
I created some domonstration reports in http://demo.idempiere.com If you open the window for "Accounting fact Details" (not the report) you can choose between three reports I created.
The first report "...example 1" just gives out all accounting facts sorted by "account". This is a list that has 315 entries. I want this list to be grouped by accounts and see the debit and credit from every account. (this is not exactly my business case but it shows the point.)
The traditional solution is in report "...example 2". It it the same as example 1 but it groups by account. To see the expected result you have to choose the report "...example 2", then you see the whole list of 315 entries with some more lines with sums. Then you can click on the "summary" checkmark to get a summary report with about 25 lines. This is what I expect.
If you try this with 100.000 entries from 350 accounts this becomes unusable. There are two reasons for that: The database extracts much too much rows and the database extracts too much columns. AFAIS this is the exact reason why the "Report View Columns" facility was created. It allows to decide that not all columns but only the given columns will be read from the database. And it allows to group and sum the rows. There is a button "SQL group function" in the "Report View Column" tab. The help text (tooltip) is "this function will generate a group by clause".
I created a report view (in the system client) and created a print format "PrintFormat Fact_Acct_IDEMPIERE_1490". This should give the same result as "...example 2" but it works much faster on a big database. Reading only some columns does work with the actual trunk code. Grouping does not work. The result differs from the expected result.
I want to get this comment ready before the weekly meeting. At this very moment I can not explain which sql statement is exactly created with the actual trunk code. The result list is shorter than an ungrouped result. But I can not debug this on demo.idempiere.com. When I did the patch (already some weeks ago) I looked deeply through the code, checked the resulting SQL code and did this patch. If someone feels bad with me telling that the patch resolves this issue I could get the resulting SQL with and without the patch with some work on my local system.
I hope that the demo setup helps to understand my goal.
Details
Assignee
Thomas BayenThomas BayenReporter
Thomas BayenThomas BayenComponents
Affects versions
Priority
Minor
Details
Details
Assignee
Reporter
Components
Affects versions
Priority
Minor
I want to use the "report view" window to create a report of data that is grouped by an SQL GROUP clause. (An example is a view of the Accounting Facts that shows only one line per accounting element.) After reading the help and the source I am sure that this is what the Register "Report View Column" of the "Report View" window is for. There is a checkbox "SQL Group Function".
This function does not work.
In a fresh database installation there is no record in the table AD_ReportView_Col. So it seems that this feature is not used very often and tested by noone.
As a workaround one can either filter data later (e.g. in the printformat; this becomes slow/unusable for tables >50000 records) or earlier (as a database view - this is what some of the standard report views do; this is unflexible and does not allow new features only by configuration of the AD). Another workaround may be to have a preprocessed report cube, (this is a very different approach to grouping of live data).
In my opinion this feature is very worthful for midsize and big installations. If you deal with big tables you come very fast to the point where creation of reports become very slow. E.g. the Accounting Fact table may have millions of records in a real world enterprise.
Especially the zk webclient shows first a html version of a report before you can choose to filter by print format. This means that iDempiere (and its persistence layer) deals with a whole long table two times. In my experience a table with 10.000 - 100.000 entries fast becomes unusable.
Possible solution: In "DataEngine.java" I see some lines with a logic like this:
if (!IsGroupFunction) groupByColumns.add(sb.toString());For me this looks like the wrong logic. But this needs further investigation.