Am I missing something? Report Format "Group By" not like SQL

[Andy Conn]

I have been enjoying playing with Accounting Fact Details report and been experimenting with a variety of reporting summaries. We have a large number of transactions to sort through so it is best to view the reports in summary mode. But then, I noticed that "Group By" fields do not summarize unless that group field value changes. This is not like SQL which would summarize each group if it OR a higher level group changed.

Consider the scenario: I'd like to show a summary of accounting debits and credits transactions by trxdate, table, recordid & account. If this were SQL I'd say

select trxdate, table, recordid, account, sum(debit), sum(credit) from myrv_fact_act group by trxdate, table, recordid, account

This query would return exactly what I want. In contrast, the Accounting Fact Details will only show me account summaries when it changes - regardless of whether recordid or any other higher level group by fields change. Therefore, if I have a series of transactions with different trxdate, tables, recordids that all hit the same account, I don't see the account being hit.

Am I missing something? This seems like an obvious problem with reporting.

Thanks,

Andy

[Carlos Antonio Ruiz Gomez]

Too little information.


El 27/02/17 a las 01:34, Andy Conn escribió:

[Andy Conn]

Does this help? These examples show output in summary mode. You can see I am sorting and grouping by trx date, table, recordid & account. The expected output shows I only get an account group total when the account changes. Instead, I would expect to see an account group total every time the recordid group changes as well (like you would see in SQL):

[Carlos Antonio Ruiz Gomez]

You can try setting all order columns as grouping - I think there is a mistake there ordering first by AcctDate and not grouping by it.

Also you can try showing the columns in the report.

Regards,

Carlos Ruiz


El 27/02/17 a las 16:52, Andy Conn escribió:

Does this help? These examples show output in summary mode. You can see I am sorting and grouping by trx date, table, recordid & account. The expected output shows I only get an account group total when the account changes. Instead, I would expect to see an account group total every time the recordid group changes as well (like you would see in SQL):

On Monday, February 27, 2017 at 4:14:48 AM UTC-5, Carlos Antonio Ruiz Gómez wrote:

Too little information.


El 27/02/17 a las 01:34, Andy Conn escribió:

I have been enjoying playing with Accounting Fact Details report and been experimenting with a variety of reporting summaries. We have a large number of transactions to sort through so it is best to view the reports in summary mode. But then, I noticed that "Group By" fields do not summarize unless that group field value changes. This is not like SQL which would summarize each group if it OR a higher level group changed.

Consider the scenario: I'd like to show a summary of accounting debits and credits transactions by trxdate, table, recordid & account. If this were SQL I'd say

select trxdate, table, recordid, account, sum(debit), sum(credit) from myrv_fact_act group by trxdate, table, recordid, account

This query would return exactly what I want. In contrast, the Accounting Fact Details will only show me account summaries when it changes - regardless of whether recordid or any other higher level group by fields change. Therefore, if I have a series of transactions with different trxdate, tables, recordids that all hit the same account, I don't see the account being hit.

Am I missing something? This seems like an obvious problem with reporting.

Thanks,

Andy

--
You received this message because you are subscribed to the Google Groups "iDempiere" group.
To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/19362941-0d44-42f4-91e8-23a2cfb31ade%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Andy Conn]

Adding AcctDate as a Group By field does not have any additional impact.

I do not know what you mean by "Showing the Columns"; do you mean not summarizing? In this case, summarizing is very important due to the large number of detail records.

[Hiep Lq]

can you temp remove "*date" column out of group by and group list.

date is order by full value (minute, second, milliseconds) but break group just by display value (maybe just to day depend your report)

To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/1e290991-8078-4e6c-a8c6-adbf4a9df55e%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Lê Quý Hiệp
Email: hie...@hasuvimex.vn
Skype: admin.hasuvimex

Company: Thanh Hoa Fishery Import - Export J.s.c  (Hasuvimex) DL 47

Add: Lot E, Le Mon Industrial Zone, Thanh Hoa, Vietnam

[Hiep Lq]

"out of group by and group list." i mean order by and group list

[Andy Conn]

I am seeing no change in the report when I remove Acct Date. 

Here is a more simple explanation of what I see:

REPORT FORMAT

Order by:    Account, Trx Date

Group By: Account, Trx Date

Sum:         Accounted

DATA

Account    Trx Date   Accounted
11100       2/22/2002     50.00

11100       2/22/2002     98.00
11200       2/22/2002     -100.00

11200       2/22/2002     100.00
11300       8/10/2002     100.00

I should expect to see (summary only):

                 2/22/2002    148.00
11100                           148.00
                 2/22/2002    100.00
11200                           100.00
                 8/10/2002    -100.00
11300                           -100.00

but instead I see

11100                           148.00
                 2/22/2002    248.00

11200                           100.00

                 8/10/2002    -100.00
11300                           -100.00

To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/1e290991-8078-4e6c-a8c6-adbf4a9df55e%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Lê Quý Hiệp
Email: hie...@hasuvimex.vn
Skype: admin.hasuvimex

Company: Thanh Hoa Fishery Import - Export J.s.c  (Hasuvimex) DL 47

Add: Lot E, Le Mon Industrial Zone, Thanh Hoa, Vietnam

[Andy Conn]

Oops - corrected example:

I am seeing no change in the report when I remove Acct Date. 

Here is a more simple explanation of what I see:

REPORT FORMAT

Order by:    Account, Trx Date

Group By: Account, Trx Date

Sum:         Accounted

DATA

Account    Trx Date   Accounted
11100       2/22/2002       50.00

11100       2/22/2002       98.00
11200       2/22/2002    -100.00

11200       2/22/2002     100.00
11300       8/10/2002     100.00

I should expect to see (summary only):

                 2/22/2002    148.00
11100                           148.00

                 2/22/2002       0.00
11200                              0.00
                 8/10/2002    100.00
11300                           100.00

but instead I see

11100                             148.00

                 2/22/2002     148.00

11200                                0.00

                 8/10/2002     100.00
11300                            100.00

[Hiep Lq]

I see each group you have two line one for date and one for trx_date and one for account.

it's default behavior or by your setup? i'm not fan of default report.

(*) account 11100 isn't have date line, it miss from report or just by mistake when copy/paste

by your latest example, 

  group is corrected, you have three group, 

  sum is almost corrected

  just one line of trx_date is incorrect other things is work well

data for date 2/22/2002 is special, it don't change on account 11100, 11200

can you setup it on demo to easy collaborate?

you also can check sql query to get data of report  by debug at line "pd.setSQL(finalSQL.toString());" class DataEngine

To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+unsubscribe@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/80059ff5-8cbe-4114-9942-d347ac108a9f%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Andy Conn]

OK - I set it up in test.idempiere.org

1) Choose:
* Print Format: Accounting Fact Details - ANDYC

* Saved Parameters: ANDYC-1

2) See at bottom of report a summary  for 21190 and 51400 for each Record ID change (image1)

3) Now re-run choosing:

* Print Format: Accouting Fact Details - ANDYC

* Print Parameters: ANDYC-2

4) Since the I chose a single account, it only shows entry for the account at the bottom (image2) instead for each Record ID change

To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/80059ff5-8cbe-4114-9942-d347ac108a9f%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Hiep Lq]

it look normal.

record is same like you run sql with group clause.

but you have a extra sum line for each time one column on group is changed.

4) Since the I chose a single account, it only shows entry for the account at the bottom (image2) instead for each Record ID change

i see, 

it don't print out duplicate value is normal, just don't know why have line "51400-Inventory Clearing Σ"  

maybe a fan of default report can explain about it

To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/2dbdcc21-a035-4ea3-b920-a8896fd9b6f4%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[anton....@fildan.com]

Hallo,

I can confirm Andy's observation.

Some time ago I created a report on the FACT table grouping by account and activity

Expecting a sub-balance for each activity and a balance for each account

Every time the last activity of the previous account was equal to the first activity of the next account,

the activity balance was made across two different accounts.

Not only in SQL but also using an Excel Pivot Table one would not expect that behavior.

I considered it an error. - that time I found a way to use financial statement reporting around this problem.

KR

Anton Fildan

To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/2dbdcc21-a035-4ea3-b920-a8896fd9b6f4%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Luis Amesty]

Hi Andy

 

when i want to disconect Groups on Querys i use:

WITH Querys

I put my first GROUP BY on with and second GROUP BY on final SELECT.

You may have samples on Stackoverflow, i give you one.

Example:

https://www.postgresql.org/docs/9.1/static/queries-with.html 

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

[Luis Amesty]

Another way is making a JOIN with a SELECT with GROUP BY

Complementing with UNION.

Look at Query Sample for Trial Balance.

[Steven Sackett]

Hi,

We also saw the problem described by Anton.

Paul made the following change to fix.

https://sourceforge.net/p/adempiere/contribution-adaxa/ci/c4a672e4bcebd0a013ab798b3f8f8f1723dcbf16/ 

regards..

[Carlos Antonio Ruiz Gómez]

Thanks Steven.

Solved here:

https://idempiere.atlassian.net/browse/IDEMPIERE-3332

[Andy Conn]

Awesome - thanks Carlos!

--
You received this message because you are subscribed to a topic in the Google Groups "iDempiere" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/idempiere/IElG9CIhG50/unsubscribe.
To unsubscribe from this group and all its topics, send an email to idempiere+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/f3290377-7c35-41e2-822a-8b1dd018fe6f%40googlegroups.com.