How to Create Pivot Table in force.com Visualforce Page
Apex Class
Public with sharing class pivot
{
public string pivotContent{get;set;}
public string ReturnValue{get;set;}
public string getPivot{get;set;}
public boolean exportPdf{get;set;}
public string getData()
{ List<PivotData> PivotDataList=new List<PivotData>();
List<Opportunity> OppList=[Select Account.Name,StageName,CloseDate,Amount from opportunity];
for(Opportunity o :OppList)
{
PivotData p=new PivotData();
p.AccountName=o.Account.Name;
p.SageName=o.StageName;
p.Year=string.valueof(o.CloseDate.Year());
p.Month=string.valueof(o.CloseDate.month()) ;
p.Amount=o.Amount;
PivotDataList.add(p);
}
getPivot='visibility: visible';
exportPdf=false;
return JSON.serialize(PivotDataList);
}
public void Result()
{
getPivot='visibility: hidden';
exportPdf=true;
ReturnValue = 'Save successfully ';
}
Public PageReference ViewPdf()
{
PageReference pageRef= new PageReference('/apex/ViewPivot');
pageRef.setredirect(false);
return pageRef;
}
public class PivotData
{
public string AccountName{get;set;}
public string SageName{get;set;}
public string Year{get;set;}
public string Month{get;set;}
public decimal Amount{get;set;}
}
}
Visualforce Page for Pivot
<apex:page controller="pivot">
<head>
<title>Pivot in Visualforce</title>
<apex:stylesheet value="{!$Resource.Pivot}" />
<script type="text/javascript" src="{!$Resource.jquery183min}" ></script>
<script type="text/javascript" src="{!$Resource.jqueryui192custommin}" ></script>
<script type="text/javascript" src="{!$Resource.PivotJS}" ></script>
</head>
<style>
* {font-family: Verdana;}
</style>
<script type="text/javascript">
function savedata()
{
var pivotContent=document.getElementById("output").innerHTML;
TestAF(pivotContent);
return true;
}
</script>
<apex:form >
<span class="btn" onclick="return savedata()" > Get Pivot Data</span>
<apex:commandlink action="{!ViewPdf}" target="_blank" >
<apex:commandButton value="View Pdf" />
</apex:commandLink>
<apex:actionfunction action="{!Result}" name="TestAF" rerender="resultPanel" status="TestStatus">
<apex:param assignto="{!pivotContent}" name="FirstParameter" value=""> </apex:param>
</apex:actionfunction>
<apex:outputpanel id="resultPanel">
<apex:actionstatus id="TestStatus" starttext="Processing..." stoptext="">
<b></b>
</apex:actionstatus>
<script type="text/javascript">
var InputData={!Data};
$(function(){
$("#output").pivot(
InputData
,
{
rows: ["AccountName","Year","Month"],
cols: ["SageName"]
}
);
});
</script>
<div id="output" style="margin: 10px;"></div>
</apex:outputpanel>
</apex:form>
</apex:page>
<head>
<title>Pivot in Visualforce</title>
<apex:stylesheet value="{!$Resource.Pivot}" />
<script type="text/javascript" src="{!$Resource.jquery183min}" ></script>
<script type="text/javascript" src="{!$Resource.jqueryui192custommin}" ></script>
<script type="text/javascript" src="{!$Resource.PivotJS}" ></script>
</head>
<style>
* {font-family: Verdana;}
</style>
<script type="text/javascript">
function savedata()
{
var pivotContent=document.getElementById("output").innerHTML;
TestAF(pivotContent);
return true;
}
</script>
<apex:form >
<span class="btn" onclick="return savedata()" > Get Pivot Data</span>
<apex:commandlink action="{!ViewPdf}" target="_blank" >
<apex:commandButton value="View Pdf" />
</apex:commandLink>
<apex:actionfunction action="{!Result}" name="TestAF" rerender="resultPanel" status="TestStatus">
<apex:param assignto="{!pivotContent}" name="FirstParameter" value=""> </apex:param>
</apex:actionfunction>
<apex:outputpanel id="resultPanel">
<apex:actionstatus id="TestStatus" starttext="Processing..." stoptext="">
<b></b>
</apex:actionstatus>
<script type="text/javascript">
var InputData={!Data};
$(function(){
$("#output").pivot(
InputData
,
{
rows: ["AccountName","Year","Month"],
cols: ["SageName"]
}
);
});
</script>
<div id="output" style="margin: 10px;"></div>
</apex:outputpanel>
</apex:form>
</apex:page>
Visualforce Page for Pivot Dynamic
<apex:page controller="pivot"><html>
<head>
<title>Pivot Dynamic in Visualforce</title>
<apex:stylesheet value="{!$Resource.Pivot}" />
<script type="text/javascript" src="{!$Resource.jquery183min}" ></script>
<script type="text/javascript" src="{!$Resource.jqueryui192custommin}" ></script>
<script type="text/javascript" src="{!$Resource.PivotJS}" ></script>
</head>
<style>
* {font-family: Verdana;}
</style>
<body>
<script type="text/javascript">
var derivers = $.pivotUtilities.derivers;
var tpl = $.pivotUtilities.aggregatorTemplates;
var InputData={!Data};
$(function(){
$("#output").pivotUI(
InputData
,
{aggregators: {
"Count": function() { return tpl.count()() },
"Sum Of Amount": function() { return tpl.sum()(["Amount"])},
}}
);
});
</script>
<div id="output" style="margin: 10px;"></div>
</body>
</html>
</apex:page>
Visualforce Page for Pivot Pdf
<apex:page controller="pivot" renderAs="PDF" contentType="text/pdf#SamplePivot.pdf">
<apex:stylesheet value="{!$Resource.Pivot}" />
<apex:outputText value="{!pivotContent}" escape="false"></apex:outputText>
</apex:page>
How would you obtain code coverage for the Apex Class?
ReplyDeleteCurrently I am very busy. shortly I update testcase
DeleteAny luck with the code coverage test? The pivot table worked nice in sandbox, but unable to deploy in prod without the coverage... please help!
DeleteCurrently I am looking JOB. for this reason I can not arrange time.
Deletei want to create pivot table for insert time sheet.could you please help me with code.
ReplyDeletefirst row is dates of current week and first column is task name and other rows with hours..
can you share the JS ressource please ?
ReplyDeletehttps://drive.google.com/file/d/0B6-JT4lgMh2oTDRGQ3ZtdmtmY28/view?usp=sharing
DeleteThis idea intrigues me. How well does it work for datasets of over 100k records? I'd be concerned with passing that many records for summary level reports.
ReplyDelete