Skip to content

Data Visualization: Adding Sparklines to Visualforce Lists

What’s great about the dashboards in Salesforce?  They allow a user to easily digest a high level view of what’s going on in an organization.  Drilling down to reports is great because they provide an in depth look into the day to day granular details.  But each of these has their limitations, especially when trying to compare subsets of data on one screen while still in Salesforce.

One request I often encounter is for a side by side comparison of of visual data, such as the last 12 months of sales across all accounts.  There isn’t an out of the box solution for this in Salesforce, which leads me to this post.  I found a neat javascript library for creating sparklines, similar to the excel.

Screen Shot 2015-04-21 at 7.17.50 AM

 

Sparklines allow a user to see groups of data in a tabular format, so it’s easier to compare trends across a large subset of data points.  To build the sparklines, we’ll need to create a static resource for the JS library – downloaded from jQuery Sparklines.  I created a component for each type of sparkline so they’re easily reusable and placed them inside a pageBlockTable in visualforce for this example.

  • 2 Controllers
    • AccountList
    • SparklineRemote
  • 1 Static Resource
    • Sparklines
  • 1 Page
    • Sparklines
  • 2 Components
    • importSparkline
    • sparkline

Controllers

The AccountList will return a list of accounts with opportunities that have been closed and SparklineRemote has a remote method that returns a list of integers for the sparklines.


public with sharing class AccountList {

    public static list<account> getAccounts(){

        list<account> accounts = new list<account>();

        accounts = [SELECT id, Name FROM Account WHERE id in (SELECT AccountId FROM Opportunity WHERE isClosed = TRUE) ORDER BY Name LIMIT 100];

        return accounts;

    }

}


global with sharing class SparklineRemote {

    @RemoteAction
    global static list<Integer> getData(string qry){

        list<Integer> dataList = new list<Integer>();

        for(aggregateResult r:database.query(qry)){
            dataList.add(integer.valueof(r.get('expr0')));
        }

        return dataList;

    }

}

Components
The top level component, importSparkline will have the JS libraries, CSS and the script to build sparklines called by the other component.


<apex:component controller="SparklineRemote">

	<script src="https://code.jquery.com/ui/1.11.3/jquery-ui.js"></script>
    <apex:includeScript value="https://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"/>
    <apex:includeScript value="{!URLFOR($Resource.sparklines)}"/>

    <style type="text/css">

    	.sparklineAlert{
    		background-color: #f2dede;
			border-color: #ebccd1;
			color: #a94442;
			padding: 15px;
			margin-bottom: 20px;
			border: 1px solid transparent;
			border-radius: 4px;
			box-sizing: border-box;
		}

    </style>

    <script>

        var j$ = jQuery.noConflict();

        Visualforce.remoting.timeout = 30000;

        function getData(qry,spanId,sparkType){
        	j$("#"+spanId).empty().append("Loading...");
            Visualforce.remoting.Manager.invokeAction(
                '{!$RemoteAction.SparklineRemote.getData}', qry,
                function(result, event){
                    if(event.status){
	                    console.log('# # # result: '+result.length);

	                    j$("#"+spanId).empty();

		                if(result.length > 0){
		                	console.log('@ @ @ results: '+result);

		                	if(sparkType === 'line'){
			                    j$("#"+spanId).sparkline(result, {
			                        type: 'line',
			                        normalRangeMin: 0,
			                        drawNormalOnTop: false,
			                        fillColor: '#c0d0f0',
			                        lineColor: '#0000f0',
			                        height: '24px',
			                        width: '100px',
			                        spotColor: '#f08000',
			                        minSpotColor: '#f08000',
			                        maxSpotColor: '#f08000',
			                        highlightSpotColor: '#50f050',
			                        highlightLineColor: '#f02020'
			                    });
			                }else if(sparkType === 'bar'){
			                	j$("#"+spanId).sparkline(result, {
			                        type: 'bar',
			                        chartRangeMinX: 0,
			                        normalRangeMin: 0,
			                        barColor: '#3366cc',
			                        height: '24px',
			                        width: '100px'
			                    });
			                }

	            		}else{
	            			j$("#"+spanId).empty().append("<center>No Data</center>");
	            		}
	            	}
                },
                {
                	escape: false
                }
            );
        }
    </script>

</apex:component>

 

Sparkline Component

 


<apex:component>
    <apex:attribute name="query" type="string" description="Query for the sparkline" required="true"/>
    <apex:attribute name="spanId" type="string" description="ID for the sparkline" required="true"/>
    <apex:attribute name="sparkType" type="string" description="Type of sparkline" required="true"/></pre>
<pre>

    <span id="{!spanId}">

        <script>

            getData('{!query}','{!spanId}','{!sparkType}');

        </script>

    </span>

</apex:component>

Page


<apex:page controller="AccountList">

    <apex:pageBlock title="My Accounts">

        <apex:pageBlockTable title="My Accounts" value="{!Accounts}" var="a">

            <apex:column headerValue="Account Name">

                 <apex:outputField value="{!a.name}"/>

            </apex:column>

            <apex:column headerValue="Won Opportunities">

                <c:sparkline query="SELECT SUM(Amount) FROM Opportunity WHERE isClosed = TRUE AND AccountId = '{!a.id}' GROUP BY Calendar_Year(CloseDate), Calendar_Month(CloseDate) ORDER BY Calendar_Year(CloseDate), Calendar_Month(CloseDate)" spanId="{!a.id}-Opps" sparkType="bar"></c:sparkline>
            </apex:column>

            <apex:column headerValue="Completed Calls">

                <c:sparkline query="SELECT COUNT(Id) FROM Task WHERE isClosed = TRUE AND AccountId = '{!a.id}' GROUP BY Calendar_Year(ActivityDate), Calendar_Month(ActivityDate) ORDER BY Calendar_Year(ActivityDate), Calendar_Month(ActivityDate)" spanId="{!a.id}-Tasks" sparkType="line"></c:sparkline>
            </apex:column>

        </apex:pageBlockTable>

    </apex:pageBlock>

</apex:page>

A live demo can be found here https://btspark-developer-edition.na24.force.com/.  Note that can you hover over the sparklines. Install Package here https://login.salesforce.com/packaging/installPackage.apexp?p0=04t1a000000AQQp
Published inAnalyticsApexJavascriptjQueryVisualforce

Be First to Comment

Leave a Reply