Cross-tabular Report based on GROUP BY CUBE Result

Question

Based on Salesforce Documentation here , we may create cross-tabular report from the query result.

I've managed to create the class so far to show the aggregate result.

Apex Class

 public class reportResource { class resultMatrix { public String Origin {get;set;} public String Status {get;set;} public Integer Count {get;set;} public resultMatrix(AggregateResult ar) { Origin = (String)ar.get('Origin'); Status = (String)ar.get('Status'); Count = (Integer)ar.get('Total'); } } public List getCaseMatrix() { List rm = new List(); for(AggregateResult result : [SELECT Origin, Status, COUNT(CaseNumber) Total FROM Case GROUP BY Origin, Status]) { resultMatrix objResultMatrix = new resultMatrix(result); rm.add(objResultMatrix); } return rm; } }

And the VF Page for the aggregate result

VF

 <apex:page controller="reportResource" sideBar="false" standardStyleSheets="false"> <apex:form id="reportForm"> <apex:pageBlock title="Matrix Result"> <apex:pageBlockTable value="{!caseMatrix}" var="cm"> <apex:column headerValue="Incoming Origin" value="{!cm.Origin}"/> <apex:column headerValue="Case Status" value="{!cm.Status}"/> <apex:column headerValue="Number of Record" value="{!cm.Count}"/> </apex:pageBlockTable> </apex:pageBlock> </apex:form> </apex:page>

But, I don't know how to generate cross-tabular report like in the documentation.
Can anyone give me some clue how to build it?

Can you explain exactly what the columns are that you want in the report? — Keith C
– Keith C, Commented Jul 25, 2014 at 8:27
For the header (x axis) is status field, and row (y axis) is origin field — Ion
– Ion, Commented Jul 25, 2014 at 9:03

Keith C · Accepted Answer · 2014-07-31 08:21:44Z

Here is some (untested) code for one way to do this.

Call this method somewhere in your controller to convert the data. It finds the unique origin and status values and adds up the counts for all the entries that have the same origin and status and stores them in a map keyed by the combination of the origin and status:

public List<String> origins {get; set;} public List<String> statuses {get; set;} public Map<String, Integer> counts {get; set;} private void createCrossTabData() { Set<String> originsSet = new Set<String>(); Set<String> statusesSet = new Set<String>(); counts = new Map<String, Integer>(); for (resultMatrix rm : getCaseMatrix()) { originsSet.add(rm.Origin); statusesSet.add(rm.Status); String key = rm.Origin + '-' + rm.Status; Integer c = counts.get(key); if (c == null) { c = 0; } c += rm.Count; counts.put(key, c); } origins = new List<String>(originsSet); origins.sort(); statuses = new List<String>(statusesSet); statuses.sort(); }

and add this to the Visualforce:

<apex:pageBlock title="Cross-Tab Result"> <table> <tr> <td></td> <apex:repeat value="{!statuses}" var="status"> <td><apex:outputText value="{!status]}"/></td> </apex:repeat> </tr> <apex:repeat value="{!origins}" var="origin"> <tr> <td><apex:outputText value="{!origin]}"/></td> <apex:repeat value="{!statuses}" var="status"> <td><apex:outputText value="{!counts[origin + '-' + status]}"/></td> </apex:repeat> </tr> </apex:repeat> </table> </apex:pageBlock>

This is a raw HTML table that you will have to use CSS to make better looking.

PS

To avoid the Visualforce error "Map key Email-Closed not found in map" you can add this code to the end of the method to make sure all keys are there:

 for (String origin : origins) { for (String status : statuses) { String key = origin + '-' + status; if (!counts.containsKey(key)) { // Set to null or perhaps to 0 counts.put(key, null); } } }

sorry for the late reply, i've tested the code and it works, but how can i get the subtotal and grand total? — Ion
– Ion, Commented Jul 31, 2014 at 3:37
@Ion Do you mean totals for the rows to the right, totals for the columns at the bottom and a grand total in the bottom right corner? You can use more map keys e.g. rm.Origin + '-origin-total', rm.Status + '-status-total', 'grand-total' and sum them in the controller loop and add them to the Visualforce. — Keith C
– Keith C, Commented Jul 31, 2014 at 6:58
thanks keith, just one more problem is if the record is null (e.g. origin=email w/ status=closed) the visualforce show error "Map key Email-Closed not found in map", how can i resolved this? — Ion
– Ion, Commented Jul 31, 2014 at 7:27

Stack Exchange Network

Cross-tabular Report based on GROUP BY CUBE Result

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Cross-tabular Report based on GROUP BY CUBE Result

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions