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

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

  • 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: 


    Fullscreen
    1
    2
    3
    4
    5
    6
    7
    8
    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;
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX



    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: 

    Fullscreen
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    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
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    Using it in a Advanced Report will look like this

    Let me know if this helps. 

    Cheers, 

    André 

  • 0 in reply to Andre Serrazina

    Hi

    We have decided to do it in Tableau it is very simple there.