Create a Matrix Table in Visualforce

Question

I was wondering how I can create a matrix table showing the months across the top (x-axis) and the product name on the y-axis with the sum of the quantity (sum of the products purchased for each month). I somewhat have what I need but it is not in the right format. If anyone has any ideas on how I can do this, that would be great. Here is the code that I am starting with so far

Apex Class:

public with sharing class AccountSnapShot2 {

public AccountSnapShot2() {

}

public Account acc {get;set;}
public SVMXC__Installed_Product__c[] instprod {get;set;}
public contact[] contacts {get;set;}
public quote[] quotes {get;set;}
public Invoice_History__c[] invoices {get;set;}  
public list<Invoice_History__c> invoice {get;set;}
public map<string,integer> data {get;set;}


public AccountSnapShot2(ApexPages.StandardController controller) {

    // Get information about the Account being worked on
       acc = [select Id, Name, CustomerMasterId__c, ShippingStreet, ShippingCity, ShippingState, Status_Flag__c, (select name, Tier_Code__c from Account_GPOs__r) from Account where Id = :controller.getRecord().Id limit 1];

       instprod = [select Id, Name, SVMXC__Date_Installed__c, Product_Age__c, Product_Code__c, SVMXC__Status__c, SVMXC__Warranty_Start_Date__c, SVMXC__Warranty_End_Date__c, (select id, name, SVMXC__Active__c, SVMXC__Start_Date__c, SVMXC__End_Date__c, SVMXC__Weeks_to_Renewal__c, Contract_PN__c from Service_Maintenance_Contracts__r where SVMXC__Active__c !=null) from SVMXC__Installed_Product__c where SVMXC__Company__c=:acc.Id Order by SVMXC__Warranty_End_Date__c DESC];

       contacts = [select Name, Email, Phone, MailingAddress from Contact where AccountId=:acc.Id and LastViewedDate !=null ORDER BY LastViewedDate DESC];

       invoices = [select Name, Product__c, Quantity__c, Actual_Ship_Date__c from Invoice_History__c where Account__c=:acc.Id];

       quotes = [select name, opportunity.name, opportunity.stagename, Submitted_for_approval__c, Id, ExpirationDate, Quote_Stage__c, TotalPrice, Opportunity.Account.Customer_Type_Code__c, CreatedById from Quote where AccountId=:acc.Id Order by ExpirationDate DESC];

        invoice=new list<Invoice_History__c>();
        invoice=[select Name, Product__c, Quantity__c, Actual_Ship_Date__c from Invoice_History__c where Account__c=:acc.Id];

        data = new map<string,integer>();
     for(Invoice_History__c inv: [Select Id, Name, Product_Code__c, Quantity__c, Actual_Ship_Date__c from Invoice_History__c where Account__c=:acc.Id]){
         integer count = data.get(inv.Product_Code__c);
            if(count != null)
             count++;
         else  
             count = 1;
         data.put(inv.Product_Code__c, count);
     }
}


}

VF Page:

<apex:page standardcontroller="Account" extensions="AccountSnapShot2"   tabStyle="Account" sidebar="false" showHeader="false">
  <style type="text/css">
      .highlight
      {
          background-color: Yellow;
          }
  .highlight1
  {
      background-color: Chartreuse;
      }     
   </style>       
  <apex:form >
  <apex:pageBlock >
      <apex:pageBlockSection title="Account Info">
          <apex:pageBlockTable value="{!acc}" var="a">
              <apex:column value="{!a.Name}"/>
              <apex:column value="{!a.CustomerMasterID__c}"/>
              <apex:column value="{!a.ShippingStreet}"/>
              <apex:column value="{!a.ShippingCity}"/>
              <apex:column value="{!a.ShippingState}"/>
              <apex:column value="{!a.Status_Flag__c}"/>
              <apex:column headerValue="GPO Price List" style="width:250px"> 
                <apex:repeat value="{!acc.Account_GPOs__r}" var="gpo">
                <DIV style="width:250%;overflow:auto">
                <apex:outputField value="{!gpo.name}"/>
                </DIV>
                </apex:repeat>
              </apex:column>
          </apex:pageBlockTable>
      </apex:pageBlockSection>
  </apex:pageBlock>

  <apex:pageBlock >
  <apex:pageBlockSection title="Contact (Recently Viewed)">
      <apex:pageBlockTable value="{!contacts}" var="con">
          <apex:column value="{!con.Name}"/>
          <apex:column value="{!con.Email}"/>
          <apex:column value="{!con.Phone}"/> 
      </apex:pageBlockTable>
  </apex:pageBlockSection>
</apex:pageBlock>

<apex:pageBlock >
  <apex:pageBlockSection title="Installed Products & Active Service Contracts">
      <apex:pageBlockTable value="{!instprod}" var="ip">
          <apex:column value="{!ip.Name}"/>
          <apex:column value="{!ip.SVMXC__Date_Installed__c}"/>
          <apex:column value="{!ip.Product_Code__c}"/>
          <apex:column value="{!ip.SVMXC__Status__c}"/>
          <apex:column value="{!ip.SVMXC__Warranty_Start_Date__c}"/>
          <apex:column >
          <apex:facet name="header">
                <apex:commandLink value="Warranty End Date" >
                    <apex:param value="installedproducts"/>
                </apex:commandLink>
          </apex:facet>      
          <span class="{!IF(AND(ip.SVMXC__Warranty_End_Date__c >= TODAY(), ip.SVMXC__Warranty_End_Date__c <= TODAY()+30), 'highlight1', '')}">
              <apex:outputField value="{!ip.SVMXC__Warranty_End_Date__c}"/>
              </span>
          </apex:column>    
          <apex:column value="{!ip.Product_Age__c}"/>
          <apex:column headerValue="Contract PN"> 
          <apex:facet name="header">
                <apex:commandLink value="Contract PN" >
                    <apex:param value="installedproducts"/>
                </apex:commandLink>
            </apex:facet> 
            <apex:repeat value="{!ip.Service_Maintenance_Contracts__r}" var="ipsc">
            <DIV style="width:100%;overflow:auto">
            <apex:outputField value="{!ipsc.Contract_PN__c}"/>
            </DIV>
            </apex:repeat>
          </apex:column>
          <apex:column headerValue="Active" > 
          <apex:facet name="header">
                <apex:commandLink value="Active" >
                    <apex:param value="installedproducts"/>
                </apex:commandLink>
            </apex:facet> 
            <apex:repeat value="{!ip.Service_Maintenance_Contracts__r}" var="ipsc">
            <DIV style="width:100%;overflow:auto">
            <apex:outputField value="{!ipsc.SVMXC__Active__c}" dir="asc"/>
            </DIV>
            </apex:repeat>
          </apex:column>
           <apex:column headerValue="Start Date" > 
          <apex:facet name="header">
                <apex:commandLink value="Start Date" >
                    <apex:param value="installedproducts"/>
                </apex:commandLink>
            </apex:facet> 
            <apex:repeat value="{!ip.Service_Maintenance_Contracts__r}" var="ipsc">
            <DIV style="width:100%;overflow:auto">
            <apex:outputField value="{!ipsc.SVMXC__Start_Date__c}"/>
            </DIV>
            </apex:repeat>
          </apex:column>
           <apex:column headerValue="End Date" > 
          <apex:facet name="header">
                <apex:commandLink value="End Date" >
                    <apex:param value="installedproducts"/>
                </apex:commandLink>
            </apex:facet> 
            <apex:repeat value="{!ip.Service_Maintenance_Contracts__r}" var="ipsc">
            <span class="{!IF(ipsc.SVMXC__End_Date__c <= TODAY()+30, 'highlight', '')}">
            <apex:outputField value="{!ipsc.SVMXC__End_Date__c}" />
            </span>
            </apex:repeat>
          </apex:column>        

      </apex:pageBlockTable>
  </apex:pageBlockSection>
</apex:pageBlock>

  <apex:pageblock>
      <analytics:reportChart reportId="00OK0000000P9sE"></analytics:reportChart>
  </apex:pageblock>

  <apex:pageBlock >
      <apex:pageBlockSection title="Reprocessing Consumables">
      <apex:pageBlockTable value="{!data}" var="d">
      <apex:column headerValue="Product Name">
          {!d}
      </apex:column>
      <apex:column headerValue="Count">
          {!data[d]}
      </apex:column>
      <apex:column headerValue="Ship Date" > 
              <apex:facet name="header">
                    <apex:commandLink value="Ship Date" >
                        <apex:param value="invoices"/>
                    </apex:commandLink>
                </apex:facet> 
                <apex:repeat value="{!invoices}" var="inv">
                <DIV style="width:100%;overflow:auto">
                <apex:outputField value="{!inv.Actual_Ship_Date__c}"/>
                </DIV>
                </apex:repeat>
              </apex:column>
      </apex:pageblocktable>     
      </apex:pageBlockSection>
  </apex:pageBlock>

  <apex:pageBlock >
  <apex:pageBlockSection title="Opportunities/Quotes">
      <apex:pageBlockTable value="{!quotes}" var="q">
          <apex:column value="{!q.Name}"/> 
          <apex:column value="{!q.Opportunity.StageName}"/> 
          <apex:column value="{!q.Submitted_for_Approval__c}"/> 
          <apex:column value="{!q.Quote_Stage__c}"/> 
          <apex:column value="{!q.TotalPrice}"/> 
          <apex:column value="{!q.Opportunity.Account.Customer_Type_Code__c}"/> 
          <apex:column value="{!q.ExpirationDate}"/> 
      </apex:pageBlockTable>
  </apex:pageBlockSection>
  </apex:pageBlock>
</apex:form>
</apex:page><!--add a few chars so SE will let me merge 3 code blocks-->

Why the 3 sections of Visualforce markup? Are they all part of the same page or 3 separate tries at a solution? — Jagular, Jul 21 '14 at 15:57
That was just how it uploaded to the stack exchange. They are all on the same visualforce page. — NewbieDeveloper27, Jul 21 '14 at 16:11
submitted an edit. Problem was a two lines not indented enough. — Jagular, Jul 21 '14 at 16:19

score 1 · Answer 1 · answered Jul 21 '14 at 23:08

It's bit hard to see what you're actually doing in the queries.

First and foremost - double check you can't achieve what you want with a normal matrix report. The Analytics API sounds like a good choice if you're OK with being limited to 2000 rows.

I think I would go with an aggregate query which would come with it's own grief of max 50K rows and you would have to build your own pagination but well. Maybe it'll give you some ideas.

First - write a query that looks close enough in the query editor. Something similar to this:

SELECT COUNT(Id) cnt, Type, CALENDAR_YEAR(LastModifiedDate) year, CALENDAR_MONTH(LastModifiedDate) month
FROM Account
WHERE Type != null
GROUP BY Type, CALENDAR_YEAR(LastModifiedDate), CALENDAR_MONTH(LastModifiedDate)

enter image description here

This query uses date functions to perform the appropriate grouping. Our next step would be to "pivot" the results somehow to generate columns where we have rows.

But it's bit hard to see from here what's the min & max date = we don't know if we need 5 or 50 columns... Ideally you'd let the user select some date range before proceeding or you can do equivalent of this:

SELECT MIN(LastModifiedDate) min, MAX(LastModifiedDate) max
FROM Account
WHERE Type != null

enter image description here

Having this you could prepare some helper data structure which you'd initially fill with zeroes and the put the non-zero quantities wherever there's a hit in the first table. Something like this:

public class Wrapper{
    String label; // for me - account type
    List<Integer> values; // values[0] = val for October 2007 etc.
}

Maybe a Map would work too?

If it sounds like a lot of work - that's because it kind of should. You'll really be better of utilising a standard report for that.

Thanks @eyescream for the advice. What I am looking to create I already have created in a standard matrix report, but I haven't found a way to get that report or a screenshot of the report onto a visualforce page. Can you put a matrix table, not a chart, on a visualforce page from a standard matrix report through analytics api? — NewbieDeveloper27, Jul 22 '14 at 13:37
Analytics API gives you only values which you'd still need to render yourself. And reports don't have "table chart" (dashboard components do). But you might be able to fetch for example the /{!reportId}?excel=1 (that's what you get when you click "Printable View") using Page.getContent() or REST API. If this looks promising - check http://salesforce.stackexchange.com/questions/4303/scheduled-reports-as-attachment for some ideas. — eyescream, Jul 22 '14 at 13:51
i have been trying to figure out how to filter based on the /{reportId} but I can't seem to get it to work. Do you add the page.getcontent() to the apex class or through a — NewbieDeveloper27, Jul 23 '14 at 21:32
In the apex class. something like this: String output = new PageReference('/ report id here').getContent().toString();. Then on your VF page add <apex:outputText value="{!output}" escape="false" /> and see what happens. You might have to tweak it, maybe debug a bit but that's the rough plan. — eyescream, Jul 23 '14 at 23:11
(of course "output" must be a class variable that's publicly visible in the VF page. Or maybe you can have it as a simple getter method: String getOutput(){ return new PageReference('/id here').getContent().toString();}) — eyescream, Jul 23 '14 at 23:17

score 0 · Answer 2 · answered Jul 21 '14 at 16:34

0

This looks like a good use-case for the Analytics API and some JavaScript to make a table. I'm on my mobile device, so I can't point you to a specific URL, but there is sample code on developer.force.com. It uses the d3 JS library, I believe.

answered Jul 21 '14 at 16:34

DavidSchach

5,874
1
23
48

Create a Matrix Table in Visualforce

2 Answers2