0

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 Jul 25 '14 at 8:27
  • For the header (x axis) is status field, and row (y axis) is origin field – Ion Jul 25 '14 at 9:03
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);
            }
        }
    }
  • sorry for the late reply, i've tested the code and it works, but how can i get the subtotal and grand total? – Ion Jul 31 '14 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 Jul 31 '14 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 Jul 31 '14 at 7:27
  • @Ion See "PS" added to answer. – Keith C Jul 31 '14 at 8:22
  • it works with 0 but not if null, thanks – Ion Jul 31 '14 at 8:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.