+ Start a Discussion
Sindhu VmSindhu Vm 

Need Help - to dynamically populating GeoChart in Visualforce page

Hi, I created a VF page and loaded Google GeoChart with static data and it worked fine. But then I wrote a controller to query the db as AggerateResult. Now my map does not work when I loop though the AggregateResult in VF page to populate the data for GeoChart.

I’m new to coding and not sure if I’m referencing the Aggregate Result properly in Vuisualforce page. Please help.

Here is the apex code:
public with sharing class testCon 
{
    Public AggregateResult[] myData{get; set;}
    Public Integer Count{get; set;}
   
    Public testCon()
    {     
       myData = [SELECT Collateral_State__c  State, Count(Name) NbrOfCollaterals FROM 
                        Collateral__c where Overall_Status__c = 'Active' and                                                              Collateral_State__c != null group by Collateral_State__c ];
       Count = Integer.valueOf(myData.size());
    }
  
    Public AggregateResult[] getmyData()
    {
       return myData;
    }
}

Here is the VF code:
<apex:page controller="testCon" readOnly="true">
    <apex:includeScript value="https://www.gstatic.com/charts/loader.js" />
    <apex:includeScript value="https://www.google.com/jsapi" />
    
    <script type="text/javascript">
        google.charts.load('current', {
        'packages':['geochart'],'mapsApiKey': 'MyKey'
      });
       
        var cnt = {!Count};
       
         google.charts.setOnLoadCallback(drawRegionsMap);
   
        function drawRegionsMap() {
   
            
            var data = new google.visualization.DataTable();
            data.addColumn('string', 'State');
            data.addColumn('number', 'Number of Collaterals');
            
            data.addRow(['Illinois',1]); // Just a sample static data for illustration
           
            for(AggregateResult rs : {!myData})
            {
                data.addRow([rs.State,rs.NbrOfCollaterals]);
            }
            
            var options = {
            region: 'US',
            displayMode: 'markers',
            backgroundColor: '#75a3e7',
            datalessRegionColor: '#f2f2f2',
            colorAxis: {colors: ['green', 'blue']
      
            }
      };

  
     //Getting the GeoChart Div Container
       var chart = new 
       google.visualization.GeoChart(document.getElementById('chart_div'));
 
     //Drawing the GeoChart with Data and Options
       chart.draw(data, options);
  }
   
   </script>
   <div id="chart_div" style="width: 900px; height: 500px;"></div>
</apex:page>

The part of the VF code which is an issue is:
 
for(AggregateResult rs : {!myData})
 {
        data.addRow([rs.State,rs.NbrOfCollaterals]);
 }

 
Best Answer chosen by Sindhu Vm
karthikeyan perumalkarthikeyan perumal
Hello 

Try below Code.  that javascript looping is not the issue.  from controller to VF page MYDate was not parsing correctly.  here is the complate code. 

Class
public with sharing class testCon 
{
   Public AggregateResult[] myData{get; set;}
   public  Map<String,Integer > FinalData {get;set;}
   public String JsonMap{get;set;}  
     
    Public Integer Count{get; set;}
   
    Public testCon()
    { 
        FinalData= new Map<String,Integer >();
       myData = [SELECT Collateral_State__c  State, Count(Name) NbrOfCollaterals FROM 
                        Collateral__c where Overall_Status__c = 'Active' and                                                              Collateral_State__c != null group by Collateral_State__c ];
       Count = Integer.valueOf(myData.size());
       
       for ( AggregateResult ar : myData)
       {
         
        FinalData.put(string.valueof(ar.get('State')),integer.Valueof(ar.get('NbrOfCollaterals')));
         
                
       }
       
        JsonMap=JSON.serialize(FinalData); 
       
        
    }
  
     
}

VF page :
 
<apex:page controller="testCon" readOnly="true">
    <apex:includeScript value="https://www.gstatic.com/charts/loader.js" />
    <apex:includeScript value="https://www.google.com/jsapi" />
    
    <script type="text/javascript">
    
        google.charts.load('current', {'packages':['geochart'],'mapsApiKey': 'MyKey' });
       
        var cnt = {!Count};
       
         google.charts.setOnLoadCallback(drawRegionsMap);
         
        function drawRegionsMap() {
     
            var data = new google.visualization.DataTable();
            data.addColumn('string', 'State');
            data.addColumn('number', 'Number of Collaterals');
            
            data.addRow(['Illinois',1]); // Just a sample static data for illustration
            
              
            var DataMap= JSON.parse('{!JsonMap}');
            
            alert(DataMap);
            
            for (var key in DataMap) {
           if (DataMap.hasOwnProperty(key)) {
           
           alert(key + " -> " + DataMap[key]);
           
           data.addRow([key ,DataMap[key]]);
           
           }
        }
           
            var options = {
            region: 'US',
            displayMode: 'markers',
            backgroundColor: '#75a3e7',
            datalessRegionColor: '#f2f2f2',
            colorAxis: {colors: ['green', 'blue']
      
            }
      };

       //Getting the GeoChart Div Container
       var chart = new 
       google.visualization.GeoChart(document.getElementById('chart_div'));
 
     //Drawing the GeoChart with Data and Options
       chart.draw(data, options);
  }
   
   </script>
   <div id="chart_div" style="width: 900px; height: 500px;"></div>
</apex:page>

NOTE: Remove alerts after you finished testing. 

Hope this will works for you.

Thanks
karthik
 

All Answers

karthikeyan perumalkarthikeyan perumal
Hello 

Try below Code.  that javascript looping is not the issue.  from controller to VF page MYDate was not parsing correctly.  here is the complate code. 

Class
public with sharing class testCon 
{
   Public AggregateResult[] myData{get; set;}
   public  Map<String,Integer > FinalData {get;set;}
   public String JsonMap{get;set;}  
     
    Public Integer Count{get; set;}
   
    Public testCon()
    { 
        FinalData= new Map<String,Integer >();
       myData = [SELECT Collateral_State__c  State, Count(Name) NbrOfCollaterals FROM 
                        Collateral__c where Overall_Status__c = 'Active' and                                                              Collateral_State__c != null group by Collateral_State__c ];
       Count = Integer.valueOf(myData.size());
       
       for ( AggregateResult ar : myData)
       {
         
        FinalData.put(string.valueof(ar.get('State')),integer.Valueof(ar.get('NbrOfCollaterals')));
         
                
       }
       
        JsonMap=JSON.serialize(FinalData); 
       
        
    }
  
     
}

VF page :
 
<apex:page controller="testCon" readOnly="true">
    <apex:includeScript value="https://www.gstatic.com/charts/loader.js" />
    <apex:includeScript value="https://www.google.com/jsapi" />
    
    <script type="text/javascript">
    
        google.charts.load('current', {'packages':['geochart'],'mapsApiKey': 'MyKey' });
       
        var cnt = {!Count};
       
         google.charts.setOnLoadCallback(drawRegionsMap);
         
        function drawRegionsMap() {
     
            var data = new google.visualization.DataTable();
            data.addColumn('string', 'State');
            data.addColumn('number', 'Number of Collaterals');
            
            data.addRow(['Illinois',1]); // Just a sample static data for illustration
            
              
            var DataMap= JSON.parse('{!JsonMap}');
            
            alert(DataMap);
            
            for (var key in DataMap) {
           if (DataMap.hasOwnProperty(key)) {
           
           alert(key + " -> " + DataMap[key]);
           
           data.addRow([key ,DataMap[key]]);
           
           }
        }
           
            var options = {
            region: 'US',
            displayMode: 'markers',
            backgroundColor: '#75a3e7',
            datalessRegionColor: '#f2f2f2',
            colorAxis: {colors: ['green', 'blue']
      
            }
      };

       //Getting the GeoChart Div Container
       var chart = new 
       google.visualization.GeoChart(document.getElementById('chart_div'));
 
     //Drawing the GeoChart with Data and Options
       chart.draw(data, options);
  }
   
   </script>
   <div id="chart_div" style="width: 900px; height: 500px;"></div>
</apex:page>

NOTE: Remove alerts after you finished testing. 

Hope this will works for you.

Thanks
karthik
 
This was selected as the best answer
Sindhu VmSindhu Vm
Thank you Karthik for the quick response. I tried the code and looks like the data is getting to VF page. But unfortunately the map is displayed without data. Here is the outcome when I run the page. The data is correct. Only 1 record is in db and the key-value pair is right. But the marker on the map is not being shown.

User-added image
User-added image
User-added image

This is the map rendered when I use only hardcoded data.
User-added image
Sindhu VmSindhu Vm
So Sorry. I missed adding my key, so the map did not render. Now it works great! Thank you very much.