creating a matrix out of a flat table in visualforce

Question

I have a controller which filters for variables: Sales Rep, Month, Revenue. However, in the VF page, I want to show this table as a matrix which would have Sales Reps in rows, Months in column and goal in respective cell in the matrix.

Controller:

public List<RevMonthscol> getRevMonthscol()
{
        Integer regnInt = (regn!=null) ? Integer.valueOf(regn) : -1;   

 List<RevMonthscol> accscol = new List<RevMonthscol>();
for (User_Stats__c a : [Select Sales_Rep__c, Month_Number__c,Year__c, Total_Rental_Revenue_Actual__c From User_Stats__c 
    where ((Month_Number__c = : today.month() AND Year__c = : string.valueof(today.year()))
    OR (Month_Number__c = : monthPlus1.month() AND Year__c = : string.valueof(monthPlus1.year()))
    OR (Month_Number__c = : monthPlus2.month() AND Year__c = : string.valueof(monthPlus2.year()))
    OR (Month_Number__c = : monthMinus1.month() AND Year__c = : string.valueof(monthMinus1.year()))
    OR (Month_Number__c = : monthMinus2.month() AND Year__c = : string.valueof(monthMinus2.year()))
    OR (Month_Number__c = : monthMinus3.month() AND Year__c = : string.valueof(monthMinus3.year()))
    OR (Month_Number__c = : monthMinus4.month() AND Year__c = : string.valueof(monthMinus4.year()))
    OR (Month_Number__c = : monthMinus5.month() AND Year__c = : string.valueof(monthMinus5.year()))
    OR (Month_Number__c = : monthMinus6.month() AND Year__c = : string.valueof(monthMinus6.year()))
    OR (Month_Number__c = : monthMinus7.month() AND Year__c = : string.valueof(monthMinus7.year()))
    OR (Month_Number__c = : monthMinus8.month() AND Year__c = : string.valueof(monthMinus8.year()))) 
and Region__c =: regnInt and District__c =: dist and Sales_Rep__c != null and Region__c != null and District__c != null
     order by Sales_Rep__c asc, Year__c asc, Month_Number__c asc])
{
    RevMonthscol opp = new RevMonthscol((String) string.valueof(a.get('Sales_Rep__c')),
                      (Integer) integer.valueof(a.get('Month_Number__c')),
                                              (Double)a.get('Total_Rental_Revenue_Actual__c'));    
    accscol.add(opp);
}

 return accscol;

}

public class RevMonthscol 
{

public Integer month { get; set; }
public String monthString { get; set; }    
public String OSRRep { get; set; }    
public Double Revenue { get; set; }
public RevMonthscol(String rep, Integer mon, Double Reve)
{
    month = integer.valueof(mon);
    OSRRep = rep;
    monthString = DateTime.newInstanceGMT(System.Date.newInstance(2000, month, 1), System.Time.newInstance(0, 0, 0, 0)).formatGMT('MMM'); 
    Revenue = Reve;

}
}

How do I code this in VF page to make the Matrix table? Please help.

Answer 1

Let me start by saying there probably is no true 'correct' way to solve this. I'm sure there are 10 ways that will get you the same result. This is just one of many possible ways to solve this problem.

I used a wrapper class to hold each row of data you need. I constructed the column header data independently to try to make it a bit simpler. The key here is in the Ordering of the User_Stats__c records in the query. If you have it ordered correctly, there is no need to keep the month and year value in the wrapper class with the actuals.

Also, as I don't have these objects, I did this in a text editor so it hasn't been compiled and could have some syntax errors, but the general idea is there and should help you get started.

APEX

public class myController {

    public list<SalesRepWrapper> wraps              {get; private set;}

    Public list<string> getMonthList(){
        list<string> months = new list<String>();

        for(Integer i = system.today().month(); i <= 12; i++){
            months.add(DateTime.newInstanceGMT(System.Date.newInstance(system.today().year(), i, 1), System.Time.newInstance(0, 0, 0, 0)).formatGMT('MMM-yy'));
        }
        for(Integer i = 1; i < system.today().month(); i++){
            months.add(DateTime.newInstanceGMT(System.Date.newInstance(system.today().year() + 1, i, 1), System.Time.newInstance(0, 0, 0, 0)).formatGMT('MMM-yy'));
        }
            return months;
    }

    public myController(){
        buildData();
    }

    public void buildData(){
        list<User_Stats__c> stats = [Select Id, Sales_Rep__c, Month_Number__c, Year__c, Total Rental_Revenue_Actual__c
                                        From User_Stats__c 
                                        Where (Month_Number__c >= system.today().month() && Year__c == system.today().year()) Or
                                        (Month_Number__c < system.today().month() && Year__c == system.today().year() + 1)
                                        Order By Sales_Rep__c, Year__c, Month_Number__c];

        set<Id> UserIDs = new set<Id>();                            
        for(User_Stats__c stat : stats){
            userIDs.add(Sales_Rep__c);
        }

        map<Id,User> userMap = new map<Id,User>([Select Id, Name From User Where Id In: userIDs]);

        map<Id,list<decimal>> rep2ActualsMap = new map<Id,list<decimal>>();

        for(User_Stats__c stat : stats){
            if(!rep2ActualsMap.containsKey(stat.Sales_Rep__c){
                rep2ActualsMap.put(stat.Sales_Rep__c, new list<Decimal>());
            }
            rep2ActualsMap.get(stat.Sales_Rep__c).add(stat.Rental_Revenue_Actual__c);
        }
        wraps = new list<SalesRepWrapper>();
        for(Id uId : rep2ActualsMap.keySet()){
            wrap.add(new SalesRepWrapper(userMap.get(uId), rep2ActualsMap.get(uId)));
        }
    }

    public Class SalesRepWrapper {

        public list<decimal> actuals            {get;set;}
        public User rep                         {get;set}

        public SalesRepWrapper(User u, list<decimal> vals){
            this.rep = u;
            this.actuals = vals;
        }
    }

}

VF

<table> 
    <tr>       
        <td></td>
        <apex:repeat value="{!monthList}" var="month">
            <th>{!month}</th>
        </apex:repeat>
    </tr>

    <apex:repeat value="{!wraps}" var="w">
        <tr>                      
            <td>{!w.rep.Name}</td>
            <apex:repeat value="{!w.actuals}" var="a">
                <td>{!a}</td>
            </apex:repeat>
        </tr>
    </apex:repeat>
</table>

This code also makes a fairly important assumption about your data model

A rep has Exactly one and only one User_Stats__c record for every month and every year, even if they have no data to report for that month. The table will not align properly if this is not the case.

Answer 2

I think this should create html table. Then use apex:repeat element to loop over rows.

How about workaround? create wrapper class which contain 13 fields, 12 months names and 1 Sales rep. And based on Month field put goal value to one of 12 varibles. Then create just normal table and display wrapper class objects in it. Thanks to that you will have Sales rep in row and months as collumns and goal in right cell. This solution is so much easier :)