Salesforce Connect Reporting

Salesforce Connect (Formerly Lightning Connect) is an exciting product that allows organizations to connect external data sources to their Salesforce instance. One way this connection is done uses the OData protocol, but you can now write your own connector in Apex as well. Instead of Standard or Custom Objects, this external data is represented as External Objects. Full Read/Write capability now exists for Salesforce Connect. However, the key is that the data is not persisted in Salesforce. 

The standard Salesforce Reporting engine cannot be used on External Objects (due to this lack of persistence within Salesforce), so reporting can only be done using Apex SOQL queries surfaced in some form. This blog posts shows two solutions to getting some basic charts and reports for External Object data, using custom Visualforce pages and Apex Controllers. Note that certain DML functions cannot be performed on External Objects, so I had to accumulate External Object data to create totals using Maps and Wrapper Classes. This also addressed the challenge of outputting multiple sets of data (totals and orders) on the same page.

There were two Salesforce Connect reporting solutions created:

  1. An enhanced list view of an External Object.
  2. A simple report of External Object data that includes charts and a list that has totals of Sales Orders by customer.

Solution 1: An Enhanced List View

The first solution results in a Controller for the List view page. Note that it’s basically just a SOQL query:

public with sharing class SalesOrderController {

public List Summaries { get; set; }
// Just a simple SOQL query to be used by the SalesOrderList VF page
public SalesOrderController() {
Summaries = [
SELECT customerName__c, ExternalId, totalSum__c, currency__c, netSum__c, note__c
from SalesOrder__x order by customerName__c limit 500 ];
     }
}

The Visualforce page simply lists out each record for the External Object (based on the query) and does some basic formatting:

<apex:page controller="SalesOrderController" >
<apex:pageBlock title="SAP Sales Order Listing" >
    <apex:PageBlockTable value="{!Summaries}" var="SlsOrderSum">
        <apex:column >
             <apex:facet name="header">Customer Name</apex:facet>
             <apex:outputText value="{!SlsOrderSum.customerName__c}"/>
        </apex:column>
        <apex:column >
             <apex:facet name="header">Sales Order ID</apex:facet>
             <apex:outputLink value="/{!SlsOrderSum.ID}">{!SlsOrderSum.ExternalID}</apex:outputLink>
        </apex:column>
        <apex:column >
             <apex:facet name="header">Total Amount</apex:facet>
             <apex:outputText value="${0, number, ###,###,###.00}">
                <apex:param value="{!SlsOrderSum.totalSum__c}" />
             </apex:outputText>
        </apex:column>
        <apex:column >
             <apex:facet name="header">Net Amount</apex:facet>
             <apex:outputText value="${0, number, ###,###,###.00}">
                <apex:param value="{!SlsOrderSum.netSum__c}"/>
             </apex:outputText>
        </apex:column>
        <apex:column >
             <apex:facet name="header">Notes</apex:facet>
             <apex:outputText value="{!SlsOrderSum.note__c}"/>
        </apex:column>
    </apex:PageBlockTable>
</apex:pageBlock>
</apex:page>

The following image shows sample output of the list view.

Sales Order Listing- Salesforce Connect

Solution 2: A Simple Report on a Visualforce Page

In this solution, the Controller for the Report page does a little bit more. It uses a SOQL query and some accumulation logic to be able to prepare the data to be displayed on the Visualforce page:

public class SalesOrderReportController {

    // Declare Lists and Maps
    public List SlsOrdRpt { get; set; }
    public List CustList {get; set; }
    public Map<String, List> SlsOrdMap {get; set; }
    public Map TotalCount { get; set; }
    public Map TotalNetSum { get; set; }
    public Map TotalSum { get; set; }
    public List TotalOrderCount { get; set; }
    public List TotalSales { get; set; }
    public List TotalNetSales { get; set; }
       
    public SalesOrderReportController() {
      // Use a fixed list to start:
   CustList = new List{
  'Asia High tech', 'Advanced Interconnections Corp', 'United Partners', 'Allied Technologies',
  'Chicago Computers', 'County of Solano','Panorama Studios','Sustainable Audio'};
      // add others, or build a query to populate a list
      // Instantiate the maps
  	SlsOrdMap = new Map<String, List>();
  	TotalCount = new Map ();
  	TotalNetSum = new Map ();
  	TotalSum = new Map ();
  	TotalOrderCount = new List ();
  	TotalSales = new List ();
  	TotalNetSales = new List ();

      // Iterate over the fixed customer list, build the map with keys/ values: 
      for (String clist : CustList){  
        // Load the map, using the iterator for the key and the where clause
    	SlsOrdMap.put(clist, SlsOrdRpt = [
     	   SELECT customerName__c, ExternalId, totalSum__c, currency__c, netSum__c, note__c
     	   from SalesOrder__x where customerName__c = :clist order by customerName__c ]);
         
       // Zero out the local accumulator variables
   	Decimal LocTotCount = 0;
   	Decimal LocTotNetSum = 0;
   	Decimal LocTotSum = 0;
       // Iterate over records in each SlsOrdRpt List and do the count/ accumulations
       for (SalesOrder__x SlsOrd : SlsOrdRpt){
     	LocTotCount = LocTotCount + 1;
         LocTotNetSum = LocTotNetSum + (Decimal)SlsOrd.netSum__c;
     	LocTotSum = LocTotSum + (Decimal)SlsOrd.totalSum__c;
               
    	}
       // Put the Totals into a Map by customer
  	TotalCount.put (clist, LocTotCount); 
  	TotalNetSum.put (clist, LocTotNetSum);
  	TotalSum.put (clist, LocTotSum);
  	TotalOrderCount.add (new ChartData (clist, LocTotCount));    
  	TotalSales.add (new ChartData (clist, LocTotSum));
  	TotalNetSales.add (new ChartData (clist, LocTotNetSum));
  	}
       
	}
    // Wrapper Class for Chart data
	Public Class ChartData {
     	Public String Name {get; set; }
     	Public Decimal Value {get; set; }

     	Public ChartData (String N,Decimal V){
              Name = N;
          	Value = V;
     	}
	}
 }

The Visualforce page for the Report first outputs two charts based on the accumulation data, and then outputs the records based on the SOQL query (with some formatting):

  
<apex:page controller="SalesOrderReportController" >
<!-- Use two Pageblocks, one for the Charts, one for the report details -->

<apex:pageBlock title="SAP Sales Order Report" >
   <apex:pageBlockSection columns="2">
       <apex:pageBlockSectionItem >           
           <apex:chart height="250" width="450" data="{!TotalSales}">
               <apex:axis type="Numeric" position="bottom" fields="Value" title="Total Sales"/> 
               <apex:axis type="Category" position="left" fields="Name" title="Customer"/>         
               <apex:barSeries orientation="horizontal" axis="left" xField="Value" yField="Name"/>
           </apex:chart>
       </apex:pageBlockSectionItem>
       <apex:pageBlockSectionItem >
           <apex:chart height="250" width="450" data="{!TotalOrderCount}">
               <apex:axis type="Numeric" position="bottom" fields="Value" title="Count of Orders"/> 
               <apex:axis type="Category" position="left" fields="Name" title="Customer"/>         
               <apex:barSeries orientation="horizontal" axis="left" xField="Value" yField="Name"/>
           </apex:chart>           
       </apex:pageBlockSectionItem>
   </apex:pageBlockSection>   
</apex:pageBlock>
    
<apex:pageBlock title="Report Detail" >
    <apex:repeat value="{!SlsOrdMap}" var="clist" >
        <!--- Iterate by Customer -->
        <apex:PageBlockTable value="{!SlsOrdMap[clist]}" var="SlsOrder" rendered="true" >
        <!-- Iterate over Sales Orders by Customer -->
            <apex:column >
                <apex:facet name="header">Customer Name</apex:facet>
                 <apex:outputText value="{!SlsOrder.customerName__c}"/>
            </apex:column>
            <apex:column rendered="true" >
                <apex:facet name="header">Sales Order ID</apex:facet>
                <apex:outputLink value="/{!SlsOrder.ID}">{!SlsOrder.ExternalID}
            </apex:outputLink>
                <!-- Footer for this Column -->
                <apex:facet name="footer">
                    <apex:outputText value="{0, Number}">
                    	Count:
                        <apex:param value="{!TotalCount [clist]}"/>
                    </apex:outputText>   
                </apex:facet>
            </apex:column>
            <apex:column >
                 <apex:facet name="header">Total Amount</apex:facet>
                <apex:outputText value="{0, Number, Currency}">
                      <apex:param value="{!SlsOrder.totalSum__c}" />
                </apex:outputText>
                <!-- Footer for this Column -->
                <apex:facet name="footer">   
                    <apex:outputText value="{0, Number, Currency}">
                    	Total Sales:
                        <apex:param value="{!TotalSum [clist]}"/>
                    </apex:outputText>
                </apex:facet>
            </apex:column>
            <apex:column >
                 <apex:facet name="header">Net Amount
                </apex:facet>
                <apex:outputText value="{0, Number, Currency}">
                    <apex:param value="{!SlsOrder.netSum__c}"/>
                </apex:outputText>           
                <!-- Footer for this Column -->
                <apex:facet name="footer">   
                <apex:outputText value="{0, Number, Currency}">
                	Total Net:
                    <apex:param value="{!TotalNetSum [clist]}"/>
                </apex:outputText>
                </apex:facet>
            </apex:column>
            <apex:column >
                <apex:facet name="header">Notes</apex:facet>
                <apex:outputText value="{!SlsOrder.note__c}"/>
            </apex:column>
        </apex:PageBlockTable> 
    </apex:repeat>
</apex:pageBlock>
</apex:page>

The following image shows sample output of the Visualforce page as a chart and report detail.

Chart:

Salesforce Connect Report Charts

Detail:

Salesforce Connect Report Detail

Here are the two Test Classes for the Controllers. Note: these Classes provide over 90% coverage:

  
@istest
public class TestSalesOrderController {

    static testmethod void TestSalesOrderController (){
    	Test.startTest();

    	SalesOrderController controller = new SalesOrderController ();
    
        Test.stopTest();
                
	}
}

@istest
public class TestSalesOrderReportController {

    static testmethod void TestSalesOrderReportController (){
         
    	Test.startTest();

    	SalesOrderReportController controller = new SalesOrderReportController ();
    
        Test.stopTest();      
              
	}
}

Next Steps

Here are some things you might try next:

  • Port the solution to other data sets as appropriate.
  • Broaden the queries to more Accounts (parameterize the accounts used for the queries).
  • Build Visualforce components and detail views.

Like everything at Salesforce, this was a team effort. Many thanks to David Scruggs for letting me scattershot him with questions during the process, and to Tim Long for listening as I explained what I was trying to do… and for giving me encouragement when I got stuck. Steve Cox with Enablepath was particularly helpful in helping me with the tests and refactoring the code.

Thanks for reading. Please let me know your thoughts in the comment section below.

 

tagged , , , , Bookmark the permalink. Trackbacks are closed, but you can post a comment.
  • Kelly Jerden

    Great work Mark! This is a great interim solution for our customers to build out reporting today.

  • Saru

    This post is really useful for knowledge on external object reporting.
    I have the requirement for reporting on external objects that
    need to populate the different external objects data based on custom pick list selection.
    Suppose if i select test1 picklist value then exobj1 data should be display in columns.
    If i select test2 picklist value then extob2j data should be display in columns.
    Please share me if there are any ideas to start the vf page implementation on this.

  • keylabs keyabs

    hi,
    great post! this is very clear about salesforce connect reporting thank you </a

  • Christian Anderson

    Will Salesforce Standard reports ever be able to use External Objects? I understand the technical challenge but wondering if it is on the roadmap. Possibly by using a cached version of the External Object data.