+ Start a Discussion
Zaheer KhanZaheer Khan 

How to display data in tabular format using Visualforce/Apex?

Hi,
 
I would like to display data in tabular format, as below:
 
Customer        Brand         Series     Year    January    February   March     April        May         June                     
Best Solution    Apple          S5          2015    $20,500     $15,300     $12,200    $16,000    $23,700     $28,900
Best Solution    Samsung     A6          2015    $21,000     $23,400     $15,440    $19,700    $25,300     $32,430
Best Solution    Apple          S5          2016    $18,200     $11,100     $18,900    $11,000    $24,600   

My question is what should I do to get my data in above layout. I will appreciate your help, please include code to show to how to write code and layout the fields to get the above output, as I am new to Apex and Visualforce. Thanks. 

 
Please see my apex class and Visualforce page. My apex class and vf page are producing results result as follows:
 
Customer          Brand          Series     Year    Month     Net Sale
Best Solution    Apple          S5          2015    1              $20,500  
Best Solution    Apple          S5          2015    2              $15,300
...
...
...
Best Solution    Apple          S5          2016    1              $18,200
...
...
Best Solution    Samsung     A6          2015    1              $21,000  
...
...
 
Apex Class
 
public with sharing class SaleSum {
 
    public Summary[ ] Summaries { get; set; }
 
 public SaleSum() {
        AggregateResult[ ] results = [
        Select Brand__c, CALENDAR_MONTH(Invoice__r.Invoice_Date__c) InvMn, CALENDAR_YEAR(Invoice__r.Invoice_Date__c) InvYr,
            SUM(Net_Amount_US__c) NetUS, SUM(Net_Amount_CDN__c) NetCDN, Invoice__r.Account__r.Currency_Id__c,     
            Invoice__r.Account__r.Customer_Id__c
        From Invoice_Line__c
        Group By Brand__c, CALENDAR_MONTH(Invoice__r.Invoice_Date__c), CALENDAR_YEAR(Invoice__r.Invoice_Date__c),
            Invoice__r.Account__r.Currency_Id__c, Invoice__r.Account__r.Customer_Id__c
        ];
       
        Summaries = new List<Summary>();
        for (AggregateResult gr : results) {
            Summaries.add(new Summary(gr));
        }
    }
 
    public class Summary {
        public Decimal NetUS { get; private set; }
        public Decimal NetCDN { get; private set; }
        public String Brand { get; private set; }
        public Integer InvMn { get; private set; }
        public integer InvYr { get; private set; }
        public String Curren { get; private set; }
        public String Customer { get; private set; }
       
   public Summary(AggregateResult gr) {
            NetUS = (Decimal) gr.get('NetUS');
            NetCDN = (Decimal) gr.get('NetCDN');
            Brand = (String) gr.get('Brand__c');
            InvMn = (Integer) gr.get('InvMn');
            InvYr = (Integer) gr.get('InvYr');
            Currency = (String) gr.get('Currency_Id__c');
            Customer = (String) gr.get('Customer_Id__c');
        }
    }
 
}
 
Visualforce Page
<apex:page controller="TestController" title="Monthly Net Amount by Customer" >
    <apex:pageBlock title="Monthly Net Amount by Customer">
    <table>
    <tr>
            <td>Customer</td>
            <td>Brand</td>
            <td>Series</td>
            <td>Year</td>
            <td>Month</td>
            <td>Net Sale</td>
    </tr>
   
<apex:repeat value="{!Summaries}" var="summary">
           <tr>
                <td><apex:outputText value="{!summary.Name}"/></td>               
                <td><apex:outputText value="{!summary.Brand}"/></td>
                <td><apex:outputText value="{!summary.Series}"/></td>
                <td><apex:outputText value="{!summary.InvYr}"/></td>
                <td><apex:outputText value="{!summary.InvMn}"/></td>
                <td><apex:outputText value="{!summary.NetUS}" rendered="{!IF(summary.Curren='US',true,false)}"/></td>
                <td><apex:outputText value="{!summary.NetCDN}" rendered="{!IF(summary.Curren='CAD',true,false)}"/></td>
            </tr>
      </apex:repeat>
      </table>
    </apex:pageBlock>
</apex:page>
 
Laxman1975Laxman1975
I think you are storing data based on year and month as rows and now you are expecting display in column format for each month.

You need to transform or group data and store that in some list of wrapper class. The wrapper class will hold all column data.

Customer
Brand        
Series    
Year    
January    
February  
March    
April      
 May        
June

You can have 12 records max per customer/brand for year considering you are storing data for 1 year.  Then you can populate that to wrapper conditionally based on months. Lot of tweaks n tricks might be needed. But it should be possible.

Best Luck

Regards
Laxman
Zaheer KhanZaheer Khan
Thanks for the good luck, Laxman.

Anybody can help with solution/code please. Thanks. 
Zaheer KhanZaheer Khan
Hi Everyone, 

Does anybody know how to accomplish the above task? Your help will be much appreciated.

Thanks,

Zaheer