+ Start a Discussion
Marry SteinMarry Stein 

Create Apex Chart related to Group By Statement

Hey Guys,

i have created a controller to generate a query which summarize the monthly revenue of each user. Now, i want to create a visualforce page which shows one chart for each user.  Is there a way to do that in visualforce without to create a separate query for each user ? I don't want to customize the controller for new employees. 


Thanks for your help guys !

Greetings Marry



 

Raj VakatiRaj Vakati
Yes ... 

Try this aggrigate SOQL 
 
SELECT SUM(Amount)aver FROM Opportunity Group By OwnerId Having OwnerId=:UserInfo.getCurrenyUser() ;

 
Alain CabonAlain Cabon
Hi,

By the way, is it one huge page with plenty of charts (one per user) or one page with one chart for the connected user?

 
Marry SteinMarry Stein
Hi Alain, Hi Raj,

yes alain, i want to create one huge page with plenty of charts . 

thanks for your reply raj but unfortunately that is not what i want to do. Sorry for that. I have to define my question more clear. 

Greetings Marry
Alain CabonAlain Cabon
Hi Marry,

Here is a sample with plenty of charts.

User-added image

User-added image

Responsive design:
User-added image

It is random but based on data stored in one object and still a POC (proof of concept, how does that work with 151 charts on the same page?).

The complete Visualforce page has only the code below using google chart :  https://developers.google.com/chart/
(unleash your creativity)
<apex:page controller="ChartGenerator">
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <apex:slds />
    <div class="slds-text-heading_large">Plenty of nice charts</div>
    <div id="colsId" class="slds-grid slds-wrap"/>         
    <apex:dynamicComponent componentValue="{!plentyCharts}"/>       
</apex:page>

The secret of this miracle is the "dynamic component" because the controller ChartGenerator is still short:

One Component.Apex.PageBlock is build dynamically with many charts:
public class ChartGenerator {  
    public Component.Apex.PageBlock getPlentyCharts () {
        Component.Apex.PageBlock dynPageBlock = new Component.Apex.PageBlock();       
        List<Contact> lacc =  [select id,name from contact order by name limit 20];
        integer i=1;
        for (Contact acc:lacc) {
             // 'Liz D'Cruz'
            String name = acc.name.replace('\'', '`');
            Component.c.mygooglechartcmp cmpChart =  new Component.c.mygooglechartcmp(placeChart=''+i,userName=name,userId=acc.Id);
            dynPageBlock.childComponents.add(cmpChart);
            i++;
        }       
        return dynPageBlock;
    }
}

And now the most important part is the component: mygooglechartcmp which does almost all the work with remote calls in javascript.
<apex:component controller="PieDataUser">
    <apex:attribute name="placeChart" description="place of the draw pie chart." type="String" required="true"/>
    <apex:attribute name="userId" description="User Id owner of the data." type="String" required="true"/>
    <apex:attribute name="userName" description="User Name owner of the data." type="String" required="true"/>    
    <script type="text/javascript">
    Visualforce.remoting.timeout = 120000; // Set timeout at page level
    google.charts.load("current", {packages:["corechart"]});
    google.charts.setOnLoadCallback(drawChart);
    function drawChart() {
        getRemoteData('{!userId}','{!userName}','{!placeChart}');         
    }
    function getRemoteData(userId,userName,placeChart) {       
        Visualforce.remoting.Manager.invokeAction(
            '{!$RemoteAction.PieDataUser.getDataPie}',
            userId,
            function (result, event){               
                if(event.status && result && (result.constructor === Array)) {
                    console.log('result:' + JSON.stringify(result));
                    let dataChart = [ ['Task', 'Hours per Day',{ role: "style" }]];
                    let colors = ["red","blue","yellow","green","cyan","gray"];
                    result.forEach(function(item,index) {
                        dataChart.push([item.Task__c,item.total,colors[index]]);
                    });              
      //  result:[{"Task__c":"Sleep","total":2},{"Task__c":"Watch TV","total":2},{"Task__c":"Work","total":2}]
                    let data = google.visualization.arrayToDataTable(dataChart);                    
                    let options = {
                        title: 'Contact:' + userName,
                        is3D: true,
                    };    
                    let place = placeChart + '_3d';   /*  id of the div containing the chart  */
                    let divtest = document.createElement("div");
                    divtest.setAttribute("class","slds-col slds-size_1-of-4 slds-box");
                    divtest.setAttribute("id",place);
                    divtest.setAttribute("style","width: 350px; height: 250px;");         
                    document.getElementById("colsId").appendChild(divtest);                
                    let random = Math.floor(Math.random() * 3);
                    let chart;
                    switch (random) {
                        case 1: chart = new google.visualization.PieChart(document.getElementById(place));break;
                        case 2:chart = new google.visualization.BarChart(document.getElementById(place));break;
                        case 0: chart = new google.visualization.ColumnChart(document.getElementById(place));break;
                        default:chart = new google.visualization.PieChart(document.getElementById(place));
                    }
                    chart.draw(data, options);                        
                } else if (event.type === 'exception') {
                    error_message = 'error message:' + event.message + " where:" + event.where + "";
                    console.log(error_message);
                } else {
                    error_message = 'error message:' + event.message;
                    console.log(error_message);
                }
            }
        );
    }
    </script>    
</apex:component>
The controller itself is short and easy to write.
public class PieDataUser {
    @RemoteAction
    public static List<AggregateResult> getDataPie(String userId) {
        List<AggregateResult> lr = [select task__c,count(id) total from Contact_Task__c where contact__c = :userId group by task__c];  
        return lr;
    }
}
Init with random values in the new object Contact_Task__c
String[] tasks = new String[]{'Work','Eat', 'Commute', 'Watch TV','Sleep'};
List<Contact> lc = [select id,name from contact order by name limit 20];
Contact[] ac = lc;
List<Contact_Task__c> ct = new List<Contact_Task__c>();
for (integer i=1;i<=200;i++) {
    integer ind1 = (integer)(Math.random() * 5);
    integer ind2 = (integer)(Math.random() * 20);
    system.debug('ind:'+ ind1+' task:'+tasks[ind1] + ' cnt:' + ac[ind2].name);
    ct.add(new  Contact_Task__c(task__c=tasks[ind1],contact__c=ac[ind2].id));
}
insert ct;

There are many alternatives for your problem. Given that "google chart" is only in javascript, the dynamic component could be useless but I am trying new things to see the results.
There is one request (@RemoteAction) for each chart indeed but that is always the case with charts (what happens with 151 charts on the same page?).
Alain CabonAlain Cabon
Last and not least is that your problem was precisely to reduce the number of SOQL queries.
The idea of using google chart could be considered to be a valid option at least (only the client side needed and reduced use of the view state).
Alain CabonAlain Cabon
Hi,

The strength of the google charts (client side, javascript) is to give users controls to manipulate what data they show natively.

You can imagine only two charts on the page and a drop down on each with all the users (for comparison).

User-added image

https://developers.google.com/chart/interactive/docs/gallery/controls
 
Marry SteinMarry Stein
Big thanks for your reply Alain ! Google Chart is really cool but i am looking for a way to do it with simple visualforce tags.   Is there a way to assign an id to the apex:chart tag and use this id in the query? 

Greetings Marry
Alain CabonAlain Cabon
Hi Marry,

You also know chart.js ( I retrieved an old question ). You know these techniques with charts in javascript.

Here is an interesting question below with <apex:chart> and data from javascript:

https://developer.salesforce.com/forums/?id=9060G0000005ldaQAA

<apex:chart data="dataArray"
<script> var dataArray = new Array();
 
<apex:page controller="TestChartCTRL">
    <apex:repeat value="{!strings}" var="str">
        <table border="1"><tr><td>
            <span id='{!str}'>&nbsp;</span>
            <apex:chart renderto="{!str}" data="dataArray" width="500" height="200">
                <apex:axis type="Category" fields="gender" position="left" title="Gender" steps="1"/>
                <apex:axis type="Numeric" fields="amount" position="bottom" title="PeopleAmount" steps="1"/>
                <apex:barSeries axis="bottom" orientation="horizontal" xField="amount" yField="gender" 
                                colorsProgressWithinSeries="true" colorSet="#0000FF,#FF0000">
                    <apex:chartLabel display="insideStart" field="amount" color="#333"/>
                </apex:barSeries>
            </apex:chart>
            </td></tr></table>
    </apex:repeat>
    <script>
    var dataArray = new Array();
    dataArray.push({'amount':1225,'gender':'Male'});
    dataArray.push({'amount':1189,'gender':'Female'});
    </script>
</apex:page>
That could work as simply as this sample but you will not find this kind of data feeding in the documentation of Salesforce.
Alain CabonAlain Cabon
Is there a way to assign an id to the apex:chart tag and use this id in the query? 
If you use only javascript remote calls and the technique above, you can solve your problem with only the client side and it is a good technique to reduce the view state use.

Now, we need to put all together.

If you post some code of your current development, we can improve it together.
Marry SteinMarry Stein
Hey Alain, 
i apologize for the late response (i was sick). I think i have to learn javascript remote first. i have never used it before. I love the simplicity of  the action function.


Greetings Marry