Thread Details
  • State Not Answered
  • Replies 2 replies
  • Subscribers 353 subscribers
  • Views 563 views
  • Users 0 members are here
Forum Actions
Related

Summary Report

Olivia Forde

Hi

I have three drop down fields on Opportunities that have Codes in them - Field A , Field B and Field C - each of these field have the same value list in the dropdown.

I want to run a report on Opportunities and count the no of times a value appears regardless of which field it is selected in.

Eg

Opp 1 

Field 1 = A Field 2 = B Field 3 = C

Opp 2 

Field 1 = B Field 2 = D Field 3 = C

Opp 3 

Field 1 = E Field 2 = A Field 3 =B

So My report should show the following count

A = 2

B = 3

C = 2

D = 1

E = 1

If possible it would be great to be able to group by the Values and show the Opps underneath

A = 2

Opp 1

Opp 3

B = 3

Opp 1

Opp 2

Opp 3 

etc

Parents
  • 0 Sugar Employee Badge

    Hello  , 

    I was giving this some thought and I think this is possible via custom query using Advanced Reports. 

    You first request of having the count per dropdown value can be accomplished with the following query: 


    SELECT combined_data.value AS FieldValue, COUNT(*) 
AS Count FROM
( SELECT id_c, fielda_c AS value FROM opportunities_cstm 
UNION ALL SELECT id_c, fieldb_c AS value 
FROM opportunities_cstm
UNION ALL SELECT id_c, fieldc_c AS value 
FROM opportunities_cstm ) 
AS combined_data GROUP BY combined_data.value;



    After inserting it in a SugarCustom Query and use it with an Advanced report it will look something like this: 

    The ability to show the Count and The Opps is a bit trickier, the only way I've found is by printing one row for each Opp, then you will have 5 rows if the A = 5. The query I used is the one bellow: 

    SELECT
    combined_data.value AS FieldValue,
    CONCAT('https://yoursugarinstance.sugarondemand.com/#Opportunities/', opportunities.id) AS OpportunityURL,
    subquery.Count AS Count
FROM (
    SELECT id_c, fielda_c AS value FROM opportunities_cstm
    UNION ALL
    SELECT id_c, fieldb_c AS value FROM opportunities_cstm
    UNION ALL
    SELECT id_c, fieldc_c AS value FROM opportunities_cstm
) AS combined_data
JOIN opportunities ON opportunities.id = combined_data.id_c
JOIN (
    SELECT
        value,
        COUNT(*) AS Count
    FROM (
        SELECT id_c, fielda_c AS value FROM opportunities_cstm
        UNION ALL
        SELECT id_c, fieldb_c AS value FROM opportunities_cstm
        UNION ALL
        SELECT id_c, fieldc_c AS value FROM opportunities_cstm
    ) AS subquery_data
    GROUP BY subquery_data.value
) AS subquery
ON combined_data.value = subquery.value
ORDER BY combined_data.value;

    Using it in a Advanced Report will look like this

    Let me know if this helps. 

    Cheers, 

    André 

Reply
  • 0 Sugar Employee Badge

    Hello  , 

    I was giving this some thought and I think this is possible via custom query using Advanced Reports. 

    You first request of having the count per dropdown value can be accomplished with the following query: 


    SELECT combined_data.value AS FieldValue, COUNT(*) 
AS Count FROM
( SELECT id_c, fielda_c AS value FROM opportunities_cstm 
UNION ALL SELECT id_c, fieldb_c AS value 
FROM opportunities_cstm
UNION ALL SELECT id_c, fieldc_c AS value 
FROM opportunities_cstm ) 
AS combined_data GROUP BY combined_data.value;



    After inserting it in a SugarCustom Query and use it with an Advanced report it will look something like this: 

    The ability to show the Count and The Opps is a bit trickier, the only way I've found is by printing one row for each Opp, then you will have 5 rows if the A = 5. The query I used is the one bellow: 

    SELECT
    combined_data.value AS FieldValue,
    CONCAT('https://yoursugarinstance.sugarondemand.com/#Opportunities/', opportunities.id) AS OpportunityURL,
    subquery.Count AS Count
FROM (
    SELECT id_c, fielda_c AS value FROM opportunities_cstm
    UNION ALL
    SELECT id_c, fieldb_c AS value FROM opportunities_cstm
    UNION ALL
    SELECT id_c, fieldc_c AS value FROM opportunities_cstm
) AS combined_data
JOIN opportunities ON opportunities.id = combined_data.id_c
JOIN (
    SELECT
        value,
        COUNT(*) AS Count
    FROM (
        SELECT id_c, fielda_c AS value FROM opportunities_cstm
        UNION ALL
        SELECT id_c, fieldb_c AS value FROM opportunities_cstm
        UNION ALL
        SELECT id_c, fieldc_c AS value FROM opportunities_cstm
    ) AS subquery_data
    GROUP BY subquery_data.value
) AS subquery
ON combined_data.value = subquery.value
ORDER BY combined_data.value;

    Using it in a Advanced Report will look like this

    Let me know if this helps. 

    Cheers, 

    André 

Children