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?

Answer 1

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);
            }
        }
    }