1

I have built a code snippet to draw a dynamic Grouped bar chart using Chart.JS where status and priority are the group by fields.

Now I want to make these two fields also dynamic based on the object i.e, if am considering another object instead of status and priority it would be stage and type.

As of now I have constructed the forEach loop based on status and priority. Please guide me to make these two fields a dynamic one to handle any two combination of fields.

For example, my raw data is coming from aggregate result query:

Select Count(Id) cnt, Status, Priority from Case group by Status, Priority

If I change the aggregate result on another object there would fields like Stage and Type instead of status and priority. So if I mention o.Status it would say field is undefined.

Is there a way to make it a dynamic one to handle any field that comes into this raw data.

raw -----> [{"cnt":1,"Status":"New","Priority":"High"},{"cnt":9,"Status":"New","Priority":"Medium"},{"cnt":4,"Status":"New","Priority":"Low"},{"cnt":3,"Status":"Working","Priority":"High"},{"cnt":9,"Status":"Working","Priority":"Medium"},{"cnt":1,"Status":"Working","Priority":"Low"},{"cnt":1,"Status":"Escalated","Priority":"High"},{"cnt":8,"Status":"Escalated","Priority":"Medium"},{"cnt":2,"Status":"Escalated","Priority":"Low"},{"cnt":4,"Status":"Closed","Priority":"High"},{"cnt":17,"Status":"Closed","Priority":"Medium"}]

  raw.forEach(function (o) {
        if (!(o.Status in nameIndices)) {
            nameIndices[o.Status] = data.labels.push(o.Status) - 1;
            data.datasets.forEach(function (a) { a.data.push(0); });
        }
        if (!statusHash[o.Priority]) {
            
            statusHash[o.Priority] = { label: o.Priority, backgroundColor: cg[counter], data: data.labels.map(function () { return 0; }) };
            data.datasets.push(statusHash[o.Priority]);
            counter++;
        }
        statusHash[o.Priority].data[nameIndices[o.Status]] = o.cnt;
        });

1 Answer 1

0

Assume your query is in apex controller, then it can run dynamic query return to the JS structure (json might be the best) with all the require input for drawing the graph.

Something like:

//Construct dynamic SQL
list<AggregateResult> aggResult = Database.query('select count(Id) cnt,' + GroupField1 + ',' +GroupField2 + ' from Case group by ' + groupField1 + ',' gorupField2);

//loop over result and set it and return map
map<String, objec> m_retData = new map<String, object>();



for(AggregateResult ag : aggResult){
    m_retData.put('valuesY', ag.get('cnt'));
    m_retData.put('labelX', ag.get(GroupField1));
}


return JSON.serialize(m_retData);

//Later in JS controller, parse the JSON, read the input and construct the graph

+Alternative approach that can be useful to you - is using sf standard report to calculate the grouping and aggregation that you need, and later running the report in apex, reading the result and sending it to the JS Controller. I posted here such example: https://lc169.blogspot.com/2020/07/salesforce-report-kpi-dashboard-and.html

Your code can be something like this:

map<String, object> m_retData = new map<String, object>();
        
        map<String, decimal> m_oppType_Amt = new map<String, decimal>();
        
        Report rep = [select Id from Report where DeveloperName = :ReportDeveloperName];
        
        //Run the report
        Reports.ReportResults repResult = Reports.ReportManager.runReport(rep.Id, false);
        
        //Read result and grouping property
        list<Reports.GroupingValue> l_groupingValue = repResult.getGroupingsDown().getGroupings();
        map<String,Reports.ReportFact> m_reportFact = repResult.getFactMap();

        map<String, object> m_cell_sumValue = new map<String, object>();
        
        //Store each per each uniuque cell its summary value
        for(String keyFact : m_reportFact.keyset()){
            list<Reports.SummaryValue> l_summryValues = m_reportFact.get(keyFact).getAggregates();
            String keyToUse = m_reportFact.get(keyFact).getKey().replace('!T', '');
    
            m_cell_sumValue.put(keyToUse, l_summryValues.get(0).getValue());    //*Might have several summaries. In this assume the first summary if the sum amount that we need
        }
        
        //Per each grouping label store it in the final result
        for(Reports.GroupingValue grupingItem : l_groupingValue){
            m_oppType_Amt.put(grupingItem.getLabel(), decimal.valueOf(String.valueOf(m_cell_sumValue.get(grupingItem.getKey()))));
        }        
        
        m_retData.put('labels', m_oppType_Amt.keySet());
        m_retData.put('values', m_oppType_Amt.values());
        
        return JSON.serialize(m_retData);
2
  • I am using two group by fields. In the map you have provided it is returning only count and group by Field. Am returning aggregate result as a String to JS controller. So in Js controller based on returned value "raw" am forming dataset as below: data {"labels":["New","Working","Escalated","Closed"], "datasets":[{"label":"High","backgroundColor":"#D54920","data":[1,3,1,4]},{"label":"Medium","backgroundColor":"#A89D83","data":[9,9,8,17]},{"label":"Low","backgroundColor":"#4D0951","data":[4,1,2,0]}]} I need to pass Status and Priority dynamically based on query we are returning.
    – Prathyusha
    Commented Aug 1, 2020 at 6:49
  • I provided only example. You should add additional output in the map if needed and arrange it in any desire output that suite you. Assume the method should receive the group fields as parameter, I wrote them as GroupField1,GroupField2. By getting the value from the result: ag.get(GroupField1) it will work dynamically.
    – Liron C
    Commented Aug 1, 2020 at 7:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .