How to properly sort groups in a summation & details report?

Hello there!

I'm trying to create a seemingly simple report for one of my clients: Number of opps per client per status.

For this I tried 2 approaches:

1. Summation with details report:

Filter on opp date = this year, group by account then opp status, display summaries = account name, status, count, colums: account name, opp name, opp status. Graph: stacked horizontal bars.

I get a report showing what I need, but no way to sort the bars by # of opps. The sorting option is simply not there.

2. Summation report:

Filter on opp date = this year, group by account then opp status, display summaries = account name, status, count. Sort on count (desc). Graph: stacked horizontal bars.

I get a report that partially sorts the data, but not the way I want: the grid below shows that the sorting occurs at the smallest group (ie opp / account / status, and not at the account level.

Is there anything that can be done apart from using a third-party reporting module?

Thanks community!

0 Dennis Wangerin over 3 years ago

Damien Pochon said:
I get a report that partially sorts the data, but not the way I want: the grid below shows that the sorting occurs at the smallest group (ie opp / account / status, and not at the account level.

I'm not fully sure if I catch that correctly, can you clarify how'd you'd expect this to sort? Also can you share the SQL that is generated to see what needs to be changed to get the right results?

support.sugarcrm.com/.../
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

0 Damien Pochon over 3 years ago in reply to Dennis Wangerin

Hello Dennis,

Thanks for picking this up.

I need the graph to show the accounts with the highest total number of opportunities first, regardless of the number per stage.

In my tests (reproduced just now on a fresh 10.3 demo builder instance), the graph shows the account with the status that has the highest count. The issues comes on the 7th bar from the top in my graph above. This bar should rank 2nd. Sales stages within an account should be sorted by stage instead (but I can imagine cases in which sorting also by count would be valid).

It seems that this is not possible out of the box but might be coming through WSystem's enhanced chart plugins, as discussed here : sugarclub.sugarcrm.com/.../chart-dashlets-on-christian-wettre-s-blog-post

And for the reference, the generated query is included below. My knowledge of SQL is limited so I cannot really say where the problem is :) I can only see that the order by clause is only in the first query. Maybe adding it in the second would sort on the total number of opps for each account?

Best regards,
Damien

Requête 1:

SELECT IFNULL(l1.name,'') l1_name
,IFNULL(opportunities.sales_status,'') opportunities_sales_status,COUNT(opportunities.id) opportunities__allcount, COUNT(DISTINCT  opportunities.id) opportunities__count
FROM opportunities
 INNER JOIN  accounts_opportunities l1_1 ON opportunities.id=l1_1.opportunity_id AND l1_1.deleted=0

 INNER JOIN  accounts l1 ON l1.id=l1_1.account_id AND l1.deleted=0

 WHERE ((1=1)) 
AND  opportunities.deleted=0 
 GROUP BY l1.name
,IFNULL(opportunities.sales_status,'') 
 ORDER BY opportunities__count DESC

Requête 2:

SELECT COUNT(opportunities.id) opportunities__allcount, COUNT(DISTINCT  opportunities.id) opportunities__count
FROM opportunities
 INNER JOIN  accounts_opportunities l1_1 ON opportunities.id=l1_1.opportunity_id AND l1_1.deleted=0

 INNER JOIN  accounts l1 ON l1.id=l1_1.account_id AND l1.deleted=0

 WHERE ((1=1)) 
AND  opportunities.deleted=0

Damien Pochon

CRM & Digital consultant @ ITS4U Group