Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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.

share|improve this question
    
Not sure what your exact date criteria need to be for the SOQL query, but my guess is that the where clause could be drastically simplified using SOQL date literals like PREV_N_DAYS:90 and NEXT_N_DAYS:90. More info here: salesforce.com/us/developer/docs/officetoolkit/Content/… –  James Loghry Feb 5 at 16:24

2 Answers 2

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.

share|improve this answer
    
Thanks alot for such detailed reply sfdc_ninja. Yes that is correct there will always be 1 record for every rep for every month so the model if perfectly fine. However when I am using your code snippet, I am getting an error " Compile Error: Non-void method might not return a value or might have statement after a return statement." for getMonthList().. I have tried different stuffs but not able to resolve this. Also just to let you know, the data I want is 9 months trailing and 3 months future.. i.e. currently its Feb so I want data from May'13 to Apr'14.. Thanks again for your help –  Robin Feb 6 at 6:39
    
the code also gives error saying incorrect constructor name for : public constructorName(){ buildData(); } –  Robin Feb 6 at 6:41
    
really sorry for asking such obvious doubts.. I am very new to this environment and am struggling.. –  Robin Feb 6 at 6:47
    
No worries. Easy fixes...the issue of not being able to compile the code. The getMonths needs to return the list, and I changed the constructor name. Updated the answer. –  sfdc_ninja Feb 6 at 11:37
    
rep2ActualsMap.put(stat.Sales_Rep__c, new list<User_Stats__c>()); gives error: Incompatible value type LIST<User_Stats__c> for MAP<Id,LIST<Decimal>> –  Robin Feb 6 at 14:08

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 :)

share|improve this answer
2  
Thanks Birthus. Can you give a rough example of how that code would look like? –  Robin Feb 5 at 13:53
2  
@Birthus, while it's always appreciated when people try to help and answer questions, it's always nice if you provide some sort of example or code snippet to accompany your answer. This answer would be much better if you had some snippet of code to show exactly what you mean. –  sfdc_ninja Feb 5 at 16:09
1  
Yeap, I know that. The issue is that as you know there is sometimes too much work in real life to create code for this forum ;/ Usually I provide some code with my answers. This time I just wasn't able to do that... Also I was sure my "work around" was described good enough :) –  Artur Kępczyński Feb 5 at 17:59
    
No worries. I know real life sometimes interferers with best intentions on SFSE :) Totally get where you're coming from. –  sfdc_ninja Feb 5 at 18:14
    
Thanks Birthus and sfdc_ninja. I understand helping newbies like me with exact code or example can become very cumbersome. much appreciated. Only with such help is it possible to slowly but surely learn this language. –  Robin Feb 6 at 6:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.