Here is a link for a demo:
Re-rendering a section of a Visualforce page that includes Google Visualization and updating the data is easy by using the following:
- <apex:actionFunction>
- <apex:outputPanel>
- <apex:selectList> & <apex:selectOption>
In my example, I also have jQuery but it is not really essential. I just use it to automatically re-size the charts when the window size changes.
For the data, I am using a custom object called Expense and it will show the expenses of the current user.
The main part of the Visualforce page is this:
<apex:form >
<apex:selectList value="{!selYear}" label="Year" multiselect="False" size="1" style="width:400px;" onchange="updateReports();">
<apex:selectOptions value="{!YRoptions}"/>
</apex:selectList>
<apex:actionFunction action="{!filterYear}" name="updateReports" rerender="mainPanel" oncomplete="drawChart();drawChart2();">
</apex:actionFunction>
</apex:form>
<apex:outputPanel id="mainPanel">
*** this is where you put the javascript that calls the Google Visualization functions to draw the charts. This is also where you include all other visualforce codes that will be re-rendered when the action is called.
</apex:outputPanel>
- The value for the select list comes from a getter method in the controller called getYRoptions and by using Salesforce's Select Option Class
- I use onchange="updateReports();" in selectList to call the action in actionFunction. ActionFunction acts like a javascript function but it calls a controller method instead of a javascript function.
- In actionFunction I call the following attributes:
- rerender - here, I put the id of the outputPanel that will be re-rendered when the action is called
- name - this is used by selectList to call actionFunction like it's a javascript function
- oncomplete - this is used to call the javascript methods to re-draw the charts when the controller completes the action
By doing this, your chart will dynamically update based on the picklist value selected in the Visualforce page.
Here is the sample Visualforce code:
<apex:page controller="Blog2DemoController" showHeader="false">
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<apex:form >
<apex:selectList value="{!selYear}" label="Year" multiselect="False" size="1" style="width:400px;" onchange="updateReports();">
<apex:selectOptions value="{!YRoptions}"/>
</apex:selectList>
<apex:actionFunction action="{!filterYear}" name="updateReports" rerender="mainPanel" oncomplete="drawChart();drawChart2();">
</apex:actionFunction>
</apex:form>
<div id="mainPanelDiv">
<apex:outputPanel id="mainPanel">
<script>
var $j = jQuery.noConflict();
// Load the Visualization API and the piechart package.
google.load('visualization', '1.0', {'packages':['corechart']});
// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(drawChart);
// Callback that creates and populates a data table,
// instantiates the pie chart, passes in the data and
// draws it.
function drawChart() {
// Create the data table.
var data = new google.visualization.DataTable();
data.addColumn('string', 'Expense Type');
data.addColumn('number', 'Amount');
<apex:repeat value="{!TableVal}" var="x" id="theRepeat">
data.addRow(["{!x.title}",{!x.val}]);
</apex:repeat>
// Set chart options
var width = $j(window).width();
var options = {'title':'Expensed Amount by Expense Type',
'width':width,
'height':width*.5,
'backgroundColor':'none',
'pieSliceText':'none'};
// Instantiate and draw our chart, passing in some options.
var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
var formatter = new google.visualization.NumberFormat(
{prefix: '$', negativeColor: 'red', negativeParens: true});
formatter.format(data, 1);
chart.draw(data, options);
}
google.setOnLoadCallback(drawChart2);
function drawChart2() {
var data2 = google.visualization.arrayToDataTable([
['Month', 'Expenses']
<apex:repeat value="{!MTableVal}" var="x">
,['{!x.title}', {!x.val}]
</apex:repeat>
]);
var width = $j(window).width();
var options = {
title: 'Monthly Total Expenses',
hAxis:{slantedText:true, slantedTextAngle:30},
legend:{position:'none'},
backgroundColor:'none',
width:width,
height:width*.5
};
var chart2 = new google.visualization.LineChart(document.getElementById('chart2_div'));
var formatter = new google.visualization.NumberFormat(
{prefix: '$', negativeColor: 'red', negativeParens: true});
formatter.format(data2, 1);
chart2.draw(data2, options);
}
function loadingMessage(){
$j('#mainPanelDiv').css('display', 'none');
}
function endLoadingMessage(){
$j('#mainPanelDiv').css("display", "block");
}
$j(window).resize(function() {
drawChart();
drawChart2();
});
</script>
<style>
.expTable, .expTable th, .expTable td{
font-size:14px;
margin:auto;
border: 1px solid black;
}
.col2{
text-align:right;
}
#chart_div{
margin:auto;
}
</style>
<apex:dataTable value="{!TableVal}" var="exp" styleClass="expTable" cellpadding="5px" columnClasses="col1, col2">
<apex:column headerValue="Expense Type" value="{!exp.title}"/>
<apex:column headerValue="Total Amount" >
<apex:outputText value="{0, number, $###,###,###.##}" >
<apex:param value="{!exp.val}" />
</apex:outputText>
</apex:column>
</apex:dataTable>
<br/>
<apex:dataTable value="{!MTableVal}" var="exp" styleClass="expTable" cellpadding="5px" columnClasses="col1, col2">
<apex:column headerValue="Month" value="{!exp.title}"/>
<apex:column headerValue="Total Amount" >
<apex:outputText value="{0, number, $###,###,###.##}" >
<apex:param value="{!exp.val}" />
</apex:outputText>
</apex:column>
</apex:dataTable>
<br/>
<apex:dataTable value="{!CTableVal}" var="exp" styleClass="expTable" cellpadding="5px" columnClasses="col1, col2">
<apex:column headerValue="Company" value="{!exp.title}"/>
<apex:column headerValue="Total Amount" >
<apex:outputText value="{0, number, $###,###,###.##}" >
<apex:param value="{!exp.val}" />
</apex:outputText>
</apex:column>
</apex:dataTable>
<div id="chart_div"></div>
<div id="chart2_div"></div>
</apex:outputPanel>
</div>
</apex:page>
Here is the sample controller for the Visualforce page:
public class Blog2DemoController{
ID ownID;
public String selYear {get; set;}
public Blog2DemoController(){
selYear = string.valueof(date.today().year());
ownId = '005d00000012d9s';
}
public List<SelectOption> getYRoptions() {
List<SelectOption> options = new List<SelectOption>();
integer yr = date.today().year();
Expense__c exp = new Expense__c();
if([select Id, Date__c from Expense__c where ownerID =: ownID order by Date__c limit 1] != null){
exp = [select Id, Date__c from Expense__c where ownerID =: ownID order by Date__c ASC limit 1];
integer y = exp.Date__c.year();
system.debug(exp.Date__c.year());
for(integer x = yr; x >= y; x--){
options.add(new SelectOption(string.valueof(x), string.valueof(x)));
}
system.debug(options);
}else{
options.add(new SelectOption(string.valueof(yr), string.valueof(yr)));
}
return options;
}
public List<tableWrapper> getTableVal() {
List<tableWrapper> data = new List<tableWrapper>();
//double totalVal = 0;
for (AggregateResult ar : [select Expense_Type__c eType, SUM(Total_Amount__c) sAmt from Expense__c WHERE CALENDAR_YEAR(Date__c) =: integer.valueof(selYear) AND OwnerId =: ownID group by Expense_Type__c]) {
data.add(new tableWrapper(string.valueof(ar.get('eType')), double.valueof(ar.get('sAmt'))));
//totalVal += double.valueof(ar.get('sAmt'));
}
//data.add(new tableWrapper('Total', totalVal));
return data;
}
public List<tableWrapper> getMTableVal() {
Map<string, string> monthMap = new Map<string,string>();
monthMap.put('1', 'Jan');
monthMap.put('2', 'Feb');
monthMap.put('3', 'Mar');
monthMap.put('4', 'Apr');
monthMap.put('5', 'May');
monthMap.put('6', 'Jun');
monthMap.put('7', 'Jul');
monthMap.put('8', 'Aug');
monthMap.put('9', 'Sep');
monthMap.put('10', 'Oct');
monthMap.put('11', 'Nov');
monthMap.put('12', 'Dec');
List<tableWrapper> data = new List<tableWrapper>();
for (AggregateResult ar : [select CALENDAR_MONTH(Date__c) d, SUM(Total_Amount__c) sAmt from Expense__c WHERE CALENDAR_YEAR(Date__c) =: integer.valueof(selYear) AND OwnerId =: ownID group by CALENDAR_MONTH(Date__c) ORDER BY CALENDAR_MONTH(Date__c)]) {
data.add(new tableWrapper(monthMap.get(string.valueof(ar.get('d'))), double.valueof(ar.get('sAmt'))));
}
return data;
}
public List<tableWrapper> getCTableVal() {
List<tableWrapper> data = new List<tableWrapper>();
//double totalVal = 0;
for (AggregateResult ar : [select Company__c c, SUM(Total_Amount__c) sAmt from Expense__c WHERE CALENDAR_YEAR(Date__c) =: integer.valueof(selYear) AND OwnerId =: ownID group by Company__c order by Company__c]) {
data.add(new tableWrapper(string.valueof(ar.get('c')), double.valueof(ar.get('sAmt'))));
//totalVal += double.valueof(ar.get('sAmt'));
}
//data.add(new tableWrapper('Total', totalVal));
return data;
}
public class tableWrapper {
public string title {get; set;}
public double val {get; set;}
//This is the contructor method. When we create a new cContact object we pass a Contact that is set to the con property. We also set the selected value to false
public tableWrapper(string c, double v) {
title = c;
val = v;
}
}
public PageReference filterYear() {
getTableVal();
getMTableVal();
getCTableVal();
return null;
}
}
Nice work Val Valino,
ReplyDeletei am new to salesforce and working on google visualization api with salesforce. I have one custom object, in that i have custom fields like risk name and country. What
i want is risk count associated with each country;
For example total risks in India. And then use these values to populate google Geomap. I have two query regarding this.
(i) How can i fetch data for each country in single list using SOQL?
(ii) If I use where (select count(risk),country from objName__c where country__c='India')condition then what about new country that customer may have enter latter. How can i handle this?
Please help me
Thanks.
The easiest way is instead of using Where country__c = 'India' is to use group by country__c.
ReplyDeleteHere is a good blog about this subject.
http://blog.jeffdouglas.com/2010/04/12/using-aggregateresult-in-salesforce-com-soql/
It works.
ReplyDeleteThanks Val Valino.
also this blog is very helpful.
Thanks again
Thank you! I'm glad that you find it helpful.
Delete