Skip to main content

Code-pattern for Creating Mind-blowing Tabular Reports on Visualforce (With Sample code)

Salesforce.com has a mind-blowing reporting structure but it limits on data that is available on the system. Can we report on Data that is not present in the system?
For e.g., in a time-sheet management system, can we identify the people who have not filled time-sheets for a particular day? Can we report on data that is not present in the system? Can we identify records that are not created?

There is one thing I have learned working on Force.com platform for last 7 years, there is No No answer in Salesforce. Think a little bit and the answer will reach you. It is somewhat like climbing mount Everest, can you do it? Sure yes, in how many days depends on how fit I am (or how crazy I am)

Ok that Everest thing came up because someone did ask me that a few weeks back, moving on. Someone did also ask me about creating a report on missing details and I gave it a thought. The short answer is Yes we can, the long answer is we write a Visualforce page, anyone can tell you that. But what I really wanted to do was create a reusable code that I could use for a generic tabular report component.

The code is fairly simple, the pseudo code for the code is.
1. Generate a set of String for Rows
2. Generate a set of String for Columns
3. Prepare a map with Key as Row+Column and store the data accordingly.
4. Create a Dynamic table on Visualforce.

Apex Class for the page

public with sharing class TabularReportController {
    
    public Set rows{get;set;}
    public Set Cols {get;set;}
    public Map dataMap {get;set;}

 public TabularReportController(){
  prepareDataForReport();
 }

  public string getURLParam(String paramName){
            return ApexPages.currentPage().getParameters().get(paramName);
 }
 
 //Get the data for the rows
 public Map getrow(){
     return new Map([Select id, name, Accountid from Contact]);
 }
 
 
 //Get the data for the columns
 public Map getColumns(){
       return new Map([Select id, name from Account]);
 }


    //This is where the magic happens
 public void prepareDataForReport(){
     rows= new Set();
     cols= new Set();
     dataMap= new Map();
     Map columnsData=getColumns();
     Map rowData= getRow();
     
     //Geronimo
     for(Account a: columnsData.values()){
         cols.add(a.name);
         for(Contact c: rowData.values()){
             rows.add(c.name);
             String key= a.name+c.name; //Key is the key in which we set the data that we want to display.
                //Do some hazy logic here. This is where the key lies, you perfom your calculations and simply prepare the data
                //For display
                //For this example I am going to check if Contact is related to account
                if(c.accountid==a.id){
                    dataMap.put(key,'Related');
                }else{
                    dataMap.put(key,'Unrelated');
                }
         }
     }
     
 }
}

Visualforce page for the code


 <apex:page controller="TabularReportController" sidebar="false">  
 <style type="text/css">  
      .Related {  
            background-color: #0B610B;  
            color: #FBF8EF !important;  
      }  
      .Unrelated{  
            background-color: #A4A4A4;  
      }  
   </style>  
 <apex:sectionHeader title="Documents" subtitle="Report"/>  
 <apex:form >  
 <apex:pageBlock id="TheTable" title="Are the Accounts and Contact related?">  
 <table border="0" cellpadding="2" cellspacing="2" style="table-layout:auto" class="list">  
      <colgroup span="2"></colgroup>   
      <thead class="">  
      <tr class=" headerRow">  
           <th class=" headerRow" scope="col" colspan="1">Contact</th>            
           <apex:repeat value="{!rows}" var="row">  
           <th class=" headerRow">{!row}</th>  
      </apex:repeat>  
      </tr>  
 </thead>  
  <tbody >  
 <apex:repeat value="{!cols}" var="col">  
 <tr class="dataRow">  
      <td class="dataCell" colspan="1" style="white-space:nowrap"><span>{!col}</span></td>  
      <apex:repeat value="{!rows}" var="row" >  
&lt;!-- This is the Key, so to speak, for the entire page. Generating the key on the visualforce page --&gt;
      <apex:variable var="key" value="{!col}{!row}"/>  
      <td class="{!dataMap[key]}">{!dataMap[key]}</td>       
 </apex:repeat>  
 </tr>  
 </apex:repeat>  
 </tbody>  
 </table>  
 </apex:pageBlock>  
 </apex:form>  
 </apex:page>  

You can see the output of the report here

You can enhance the code using the same structure. Here are couple different thing I tried
  1. Adding a link to the data using wrapper
  2. Model window editing for the data
  3. Adding complex many to many relationships
  4. Plotting records that are not present

The list can go on.

Popular posts from this blog

The curious case of the custom redirection on Salesforce Console

Every developer worth their salt knows that the easiest way of redirection from a page to another is by using everyone's favorite function

public PageReference redirect() { PageReference pageRef = page.peskyProblemRedirection; pageRef.setRedirect(true); return pageRef; }
And the method is called by adding it to the Action attribute of the CommandButton or link, which works like charm and the user is redirected to the page after completion of the action.
So why am I going back to the basics? Because this way of redirection causes a pesky little problem in using the Service Cloud or Sales Cloud console.

Let's illustrate the problem, let's say you have a visualforce page as follows:

<apex:page sidebar="false" showHeader="false" controller="myExampleController"> <apex:form > <apex:pageBlock > <apex:pageBlockButtons > <apex:commandButton action="{!Redirect}&qu…

Some PDF tricks on Visualforce: Landscape, A4, page number and more

The beauty of Visualforce is simplicity. Remember the shock you received when you were told the entire page renders as PDF if you just add renderAs=PDF to the Page tag.

For those who thought I spoke alien language right now, here is the trick, to render a page as PDF, we add a simple attribute to the <apex: page> tag

<apex: page renderAs='pdf'> This will render the entire page as PDF.

Now, say we need to add some extra features to the PDF. Like a page number in the footer or we need to render the page in landscape mode. Faced with this problem, I put on my Indiana Jones hat and went hunting for it in the vast hay-sack of the internet (read: googled extensively). Imagine my happiness when i found a big big page with many big big examples to solve the problem. The document I am referring to is from W3C, paged Box media.

Long story short, I now possess the ultimate secret of rendering the page in any format I want. So here are few tricks I learned from the page. To p…

Cache me if you can: What you should know before daring to set URL parameter on visualforce

If someone gave me a pence for every time there was an SOQL query in an APEX Class without using Limit or a condition during a code review, I could afford a Lamborgini this month. Sigh. If only. We make it a habit of going digging for data, at the very moment we need it. The crux of this problem happens when you have chain classes which are independent of each other. Each class needs the reference from a single record and we have to query for that record every single time.

While we don't see it, every SOQL query has a cost to it, and it does not go in my Lamborghini fund, however, it should. In a recent project, we had to construct an Account 360 page that could fetch information from different integration points. The page was also called using a live telephony integration, which could pass the phone number for the account. This required an ability to keep in context the Account that was on call.

Passing the Account id in URL parameter was a valid option, however, any manipulatio…