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).
1st two fields are the formula field which returns
Number of Month and Year. The 3rd 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.
