Month wise data distribution using Google Chart

Salesforce

This topic is related to the charting on Account, I need to show the chart in which the scenario is to show the Order total against account on monthly basis. For example, 10 Orders are made in December 2015, 15 Order in November and 30 Orders in October. So if user go to any Account than there would be Chart which would show these statistics. So I have a made a custom VF page in which I showed this chart using Google Chart. So Let’s start.

I have a made a custom button in Account page layout and if user click this custom button than it would redirect to the custom VF page taking Account Id and current Year in query string. If you see below image, you would that custom button on Account

Picture A

If you click this button on Account detail page than it would open this below custom VF Page

Picture B

This above picture show the Order Total sum month wise. I have made a 3 custom field in Order

1)    Order Month (Formula fieldà Month(EffectiveDate))   [Integer Type]

2)    Order Year    (Formula fieldà Year(EffectiveDate))     [Integer Type]

3)    Order Month String  (Text Field).

1^st two fields are the formula field which returns Number of Month and Year. The 3^rd field is tricky one, in this field we need the Month Name like “December”, so this field contains the data using Workflow, in workflow, I have set this string field using formula, if the Order Month (formula field) contains 12 than set the “December” text in this field, see below formula image taken from workflow

Picture 3

So this way, I have reached to that position in which SOQL will return that data which will pass into the Google chart to show Chart distribution. Please see the below apex page code.

<apex:page controller="GoogleChartsController" sidebar="false">     <!-- Google API inclusion --> <apex:form >     <apex:includeScript id="a" value="https://www.google.com/jsapi" />  <span>Select Year:</span><apex:selectList value="{!selectedYear}" multiselect="false" size="1">                 <apex:selectOption itemValue="2015" itemLabel="2015"/>                 <apex:selectOption itemValue="2014" itemLabel="2014"/>                 <apex:selectOption itemValue="2013" itemLabel="2013"/>                 <apex:selectOption itemValue="2012" itemLabel="2012"/>                 <apex:selectOption itemValue="2011" itemLabel="2011"/>      </apex:selectList>            <apex:commandButton action="{!Generate}" value="Generate Report" id="theButton"/>  <div id="chartBlock" />          <script type="text/javascript">         function getUrlVars() {     var vars = [], hash;     var hashes = window.location.href.slice(window.location.href.indexOf('?') + 1).split('&');     for(var i = 0; i < hashes.length; i++)     {         hash = hashes[i].split('=');         vars.push(hash[0]);         vars[hash[0]] = hash[1];     }     return vars; }         // 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(initCharts);            function initCharts() {                   // Following the usual Remoting syntax           // [<namespace>.]<controller>.<method>([params...,] <callbackFunction>(result, event) {...}           // controller : GoogleChartsController           // method : loadOpps           var Id = getUrlVars()["Id"];           var Year = getUrlVars()["Year"];           GoogleChartsController.loadOpps(Id,Year,                  function(result, event){                       // load Column chart                      var visualization = new google.visualization.ColumnChart(document.getElementById('chartBlock'));                      // Prepare table model for chart with columns                      var data = new google.visualization.DataTable();                      data.addColumn('string', 'Order');                      data.addColumn('number', 'Order Amount'); //                     data.addColumn('number', 'Amount');                         // add rows from the remoting results                      for(var i =0; i<result.length;i++){                         var r = result[i];                         data.addRow([r.OMonth__c, r.OrderTotal]);                       }                     // all done, lets draw the chart with some options to make it look nice.                     visualization.draw(data, {legend : {position: 'top', textStyle: {color: 'blue', fontSize: 10}}, width:window.innerWidth,vAxis:{textStyle:{fontSize: 10}},hAxis:{textStyle:{fontSize: 10},showTextEvery:1,slantedText:false}});               }, {escape:true});           }     </script> </apex:form> </apex:page>

In above yellow highlighted area, I am fetching the Query strings in javascript and pass these query string values in GoogleChartsController.loadOpps in above yellow highlighted area. This LoadOpps is actually is a remote action defined in controller of this apex page. Now moving towards the controller, see the controller code below.

global with sharing class GoogleChartsController { public String selectedYear{get;set;} public GoogleChartsController() {     selectedYear = ApexPages.currentPage().getParameters().get('Year'); } public PageReference Generate() {     PageReference nextPage = new PageReference('/apex/GoogleChartsController?Id=' + ApexPages.currentPage().getParameters().get('Id') + '&Year=' + selectedYear);     nextPage.setRedirect(true);     return nextPage;     }     /**       Loads Orders     */     @RemoteAction       global static List<AggregateResult> loadOpps(String Id,decimal Year) {         //return [select Id, Name, ExpectedRevenue, Amount from Opportunity order by CreatedDate DESC limit 10];         List<AggregateResult> li= [SELECT OMonth__c, sum(TotalAmount) OrderTotal FROM Order where AccountId = :Id and Order_Year__c = :Year GROUP BY OMonth__c];         return li;     }   }

The query I am using in above controller code is actually returning that data which I need and this SOQL based on some filter criteria. This way this page show the Order Statistics using google chart.