Details about this thread
  • State Not Answered
  • Replies 2 replies
  • Subscribers 313 subscribers
  • Views 1254 views
  • Users 0 members are here
Related content from around SugarClub

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

Damien Pochon

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!

Parents Reply Children
  • 0 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