function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Derek BrostDerek Brost 

Display table data visualforce page

Hello All, 

I have been working on creating a custom dashboard that refreshes with Visualforce with the reportChart ability from the API.

It is great that I can pull in chats, but what I am looking to do is to pull in data in a table format as well. Is there an easy way to do this that I am missing? 

Thanks, 

Derek
Mathew Andresen 5Mathew Andresen 5
I actually did something similiar, I just created a personalized dashboard that tracked revenue and margin per quarter against goals, and then did top 10 accounts and opportunities at 90 and 66%.  Note this requrires too custom objects "My Plan" which is where I put the custom dashboard (they do a new plan each month where they say what they will do to hit their goal), and "Goal" which has their goal for the quarter.

If you want I can share it with you
Derek BrostDerek Brost
That would be great if you could. It sounds like it is close to what I am looking for and could at least get started from there.
Mathew Andresen 5Mathew Andresen 5
Sure here you go.  I have 4 parts, the main class, the tester class, a class to build the test data, and of course the visualforce page which extends the my_goal__c object.  Here is a screen shot of what the finished product looks like. 

Basically, I just do queryies and then use Visualforce's built in chart options for the charts, and the pageblocktable to display the queries for the other data.

Hope this helps,

Thanks,

PersonalDashBoard


The Main Class 
public class PersonalDashBoardClass {
    // attributes come first
    public My_Plan__c myPlan; 
    My_Plan__c plan = new My_Plan__c();
    String userId;
    Integer revenueMax =0;
    Integer marginMax = 0;
    Integer totalRevenue = 0;
    Integer totalMargin = 0;
    Integer chartTotalMargin = 0;
    Integer chartTotalRevenue = 0;
 
List<ChartData> guageRevData = new List<ChartData>();
List<ChartData> guageMarginData = new List<ChartData>();

        
    //Constructor comes next
    public PersonalDashBoardClass(ApexPages.StandardController stdController) {
          //  this.myPlan = (My_Plan__c)stdController.getRecord();
          Id PlanId = ApexPages.currentPage().getParameters().get('id');
			plan = [SELECT OwnerId FROM My_Plan__c WHERE Id = :PlanId];
        	userId = plan.OwnerId;
			system.debug(UserId);
          //  currentUser = [Select id from User where id =: UserInfo.getUserId() LIMIT 1];  // deprecated now use client owner to get current plan
       
 		// get the revenue and margin goals from the Goals__C object
        try {
            Goals__c myGoal = [SELECT Revenue_Goal__C, Margin_Goal__c FROM Goals__c WHERE Sales_Rep__c =:UserId AND Start_Date_of_New_Quarter__c = THIS_Quarter LIMIT 1];
            revenueMax = (Integer)myGoal.Revenue_Goal__c/1000;
            marginMax = (Integer)myGoal.Margin_Goal__c/1000;
         } catch (exception e) {
            system.debug(e);
            ApexPages.addMessage(new ApexPages.message(ApexPages.Severity.FATAL, 'You must have a goal for the quarter'));
            system.debug(' you have an error' +e);
        }   
        
        // get the total revenue for closed opportunities for this quarter
        try {
              AggregateResult ClosedWonOpptys = [select SUM(Amount) totalRevenue, CALENDAR_QUARTER(CloseDate) theMonth, COUNT(Name) numOpps
                                       from Opportunity
                                       where (Account.OwnerId =: UserId OR
                                              Account.Client_Specialist_1__c =:UserId OR
                                              Account.Client_Specialist_2__c =:UserId OR
                                              Account.Client_Executive__c =:UserId)
                                              AND CloseDate = THIS_Quarter 
                                              AND StageName = 'W. Win'
                                       GROUP BY CALENDAR_QUARTER(CloseDate) LIMIT 1];
              totalRevenue = Integer.valueOf(closedWonOpptys.get('totalRevenue'))/1000;	
              if(totalRevenue > revenueMax) {
                    chartTotalRevenue = revenueMax;
              } else { chartTotalRevenue = totalRevenue; }
                  guageRevData.add(new ChartData('Total' + ' Opptys', chartTotalRevenue   ) );


            } catch (exception e) {
                
            }
        
        // get the total margin for closed opportunities for this quarter
        try {
          AggregateResult ClosedWonOpptys = [select SUM(Total_Gross_Margin__c) totalMargin, CALENDAR_QUARTER(CloseDate) theMonth, COUNT(Name) numOpps
                                   from Opportunity
                                   where (Account.OwnerId =: UserId OR
                                         Account.Client_Specialist_1__c =:UserId OR
                                         Account.Client_Specialist_2__c =:UserId OR
                                         Account.Client_Executive__c =:UserId)                                            
                                         AND CloseDate = THIS_Quarter
                                         AND StageName = 'W. Win'
                                   GROUP BY CALENDAR_QUARTER(CloseDate) LIMIT 1];
             TotalMargin = Integer.valueOf(closedWonOpptys.get('totalMargin'))/1000;
                if (totalMargin > marginMax) {
                    chartTotalMargin = marginMax;
                } else { chartTotalMargin = totalMargin; }                  
				guageMarginData.add(new ChartData('Total' + ' Opptys', chartTotalMargin   ) );                    
        } catch(exception e) {
           
        }
          
        

        
 
   
    } // ends the constructor //////////////////////////////////////////////////////
    
    
    public List<ChartData> GuageChartRevenueList {
        get {
              return GuageRevData;
            
        }
    }
    
        public Integer GuageChartRevenue {
        get {
              return totalRevenue;
            
        }
    }
    
            // returns the guage revenue max
    public Integer GuageMaxRev {
        get {
            return revenueMax;
        } set; 
    }
    
     public List<ChartData> GuageChartMarginList {
        get {
              return guageMarginData;
            
        }
    }
    
        public Integer GuageChartMargin {
        get {
              return totalMargin;
            
        }
    }
    
            // returns the guage revenue max
    public Integer GuageMaxMargin {
        get {
            return MarginMax;
        } set; 
    }   
    
   
    // Wrapper class
    public class ChartData {
        public String name { get; set; }
        public Double data1 { get; set; }
              
        public ChartData(String name, Double data1) {
            this.name = name;
            this.data1 = data1;
        
  
        }
    }
    
  /////////////////////////////////////////////////////////////////////////////
 // The query for the top 10 opportunities at 90%
    public list<Opportunity> getOppList90() {
        
        List<Opportunity> OppList = new List<Opportunity>();
        OppList = [SELECT Account.Name, Account.Id, Name, CloseDate, Amount, Id 
                   FROM Opportunity 
                                   WHERE (Account.OwnerId =: UserId OR
                                         Account.Client_Specialist_1__c =:UserId OR
                                         Account.Client_Specialist_2__c =:UserId OR
                                         Account.Client_Executive__c =:UserId)                                            
                                         AND CloseDate = THIS_Quarter                                           
                                         AND CloseDate = THIS_Quarter                   
                                         AND StageName='A. Pending Sale' AND CloseDate = THIS_Quarter
                                         ORDER BY Amount DESC LIMIT 10];
        return OppList;
        
    }
    
    public Opportunity getOpp90Total() {
        Opportunity opp90Total = new Opportunity(Amount=0);
        for(Opportunity o :getOppList90()) {
            opp90Total.Amount +=o.amount;
        }
        return opp90Total;
    }
    
       /////////////////////////////////////////////////////////////////////////// 
    public class AcctSumList {
        public string AccountId {get; set; }
        public String AccountName {get; set; }
        public double AccountTotal {get; set; }
        
        public AcctSumList(String AccountId, String AccountName, double AccountTotal) {
            this.AccountId = AccountId;
            this.AccountName = AccountName;
            this.AccountTotal = AccountTotal;
        }
        
    }
 /////////////////////////////////////////////////////////////////////////   
    public List<AcctSumList> getAccountSummary90() {
        list<AcctSumList> acctSummary = new List<AcctSumList>();
        List<AggregateResult> getList = [SELECT Account.Id acctId, Account.Name acctName, sum(amount) TotalAmount
                                         FROM Opportunity 
                                 		 WHERE (Account.OwnerId =: UserId OR
                                         Account.Client_Specialist_1__c =:UserId OR
                                         Account.Client_Specialist_2__c =:UserId OR
                                         Account.Client_Executive__c =:UserId) 
                                         AND StageName='A. Pending Sale' AND CloseDate = THIS_Quarter
                                         GROUP BY Account.Name, Account.Id ORDER BY sum(amount) DESC LIMIT 10];
        for(AggregateResult agg : getList) {

             String AccountName = (string)agg.get('acctName');
             Double TotalAmount = (double)agg.get('TotalAmount');
			 String AccountID = (string)agg.get('acctId');
            acctSummary.add(new AcctSumList(AccountId, AccountName, TotalAmount));
        }
        return acctSummary;
    }
    
            public double getAcct90Total() {
        double Acct90Total = 0;
        for(AcctSumList acct :getAccountSummary90()) {
            Acct90Total += acct.AccountTotal;
        }
        return Acct90Total;
    }

        
    
    
    ///////////////////////////////////////////////////////////////////////////
        public list<Opportunity> getOppList60() {
        List<Opportunity> OppList = new List<Opportunity>();
        OppList = [SELECT Account.Name, Name, CloseDate, Amount 
                   						 FROM Opportunity 
                                 		 WHERE (Account.OwnerId =: UserId OR
                                         Account.Client_Specialist_1__c =:UserId OR
                                         Account.Client_Specialist_2__c =:UserId OR
                                         Account.Client_Executive__c =:UserId) 
                                         AND StageName='B. Decision Due' AND CloseDate = THIS_Quarter
                                         ORDER BY Amount DESC LIMIT 10];
        
        return OppList;
       
    }
    
        public Opportunity getOpp60Total() {
        Opportunity opp60Total = new Opportunity(Amount=0);
        for(Opportunity o :getOppList60()) {
            opp60Total.Amount +=o.amount;
        }
        return opp60Total;
    }
 /////////////////////////////////////////////////////////////////////////   
    public List<AcctSumList> getAccountSummary60() {
        list<AcctSumList> acctSummary = new List<AcctSumList>();
        List<AggregateResult> getList = [SELECT Account.Id acctId, Account.Name acctName, sum(amount) TotalAmount 
                                         FROM Opportunity 
                                 		 WHERE (Account.OwnerId =: UserId OR
                                         Account.Client_Specialist_1__c =:UserId OR
                                         Account.Client_Specialist_2__c =:UserId OR
                                         Account.Client_Executive__c =:UserId) 
                                         AND StageName='B. Decision Due' AND CloseDate = THIS_Quarter
                                         GROUP BY Account.Name, Account.Id ORDER BY sum(amount) DESC LIMIT 10];
        for(AggregateResult agg : getList) {
            String AccountName = (string)agg.get('acctName');
             Double TotalAmount = (double)agg.get('TotalAmount');
			 String AccountID = (string)agg.get('acctId');
            acctSummary.add(new AcctSumList(AccountId, AccountName, TotalAmount));
        }
        return acctSummary;
    }
    
         public double getAcct60Total() {
        double Acct60Total = 0;
        for(AcctSumList acct :getAccountSummary60()) {
            Acct60Total += acct.AccountTotal;
        }
        return Acct60Total;
    }    
    
// ends the classs ////////////////////////////////////////   
}

The visualforce page
  
<apex:page standardController="My_Plan__c" extensions="PersonalDashBoardClass" sidebar="true" id="MyPlan" >
   <apex:messages />  
<apex:pageBlock title="My Plan">



    
<apex:pageBlockSection columns="3"> 
    
    <apex:chart name="RevenueGuage" height="100" width="300" animate="true" data="{!GuageChartRevenueList}">
       <apex:axis type="Gauge" position="gauge" title="Total Revenue"  minimum="0" maximum="{!GuageMaxRev}" steps="10"/>
       <apex:gaugeSeries dataField="data1" donut="10" colorSet="#78c953,#ddd"/>
   </apex:chart>
    
<apex:panelGrid columns="4" border="1" >
    <apex:facet name="header">Summary of Revenue and Margin needed</apex:facet>
    <apex:outputText value="Catagory" style="font-weight: bold" />
    <apex:outputText value="Qtr to Date" style="font-weight: bold" />
    <apex:outputText value="Goal" style="font-weight: bold" />
    <apex:outputText value="Needed" style="font-weight: bold" />
    <apex:outputText value="Revenue" />
    <apex:outputText value="{0,number,$###,###,##0}">
    <apex:param value="{!guageChartRevenue*1000}"/> </apex:outputText>     
    <apex:outputText value="{0,number,$###,###,##0}">
    <apex:param value="{!GuageMaxRev*1000}"/> </apex:outputText> 
    <apex:outputText value="{0,number,$###,###,##0}">
    <apex:param value="{!(guageChartRevenue - GuageMaxRev)*1000}"/> </apex:outputText>    
    <apex:outputText value="Margin" />
    <apex:outputText value="{0,number,$###,###,##0}">
    <apex:param value="{!guageChartMargin*1000}"/> </apex:outputText>  
    <apex:outputText value="{0,number,$###,###,##0}">
    <apex:param value="{!GuageMaxMargin*1000}"/> </apex:outputText>
    <apex:outputText value="{0,number,$###,###,##0}">
    <apex:param value="{!(guageChartMargin - GuageMaxMargin)*1000}"/> </apex:outputText>   
  
 </apex:panelGrid>
 

    <apex:chart name="MarginGauge" height="100" width="300" animate="true" data="{!GuageChartMarginList}">
       <apex:axis type="Gauge" position="gauge" title="Total Margin"  minimum="0" maximum="{!GuageMaxMargin}" steps="10"/>
       <apex:gaugeSeries dataField="data1" donut="10" colorSet="#78c953,#ddd"/>
   </apex:chart>
</apex:pageBlockSection> 

    
<apex:form >
    <apex:pageBlockSection title="Action Plan">
    <apex:inputField value="{!My_Plan__c.Monthly_Plan__c}"/>  
    <apex:commandButton value="Save" action="{!save}"/>
    </apex:pageBlockSection>
    
</apex:form>  
    
     </apex:pageBlock> 
        
    <apex:pageBlock >
       <apex:pageBlockSection columns="6">
           
        <apex:pageBlockTable value="{!AccountSummary90}" var="acct" captionClass="tableHeader">
            <apex:facet name="caption">Top 10 Accounts at 90%</apex:facet>
         <apex:column headerValue="Account">
          	    <apex:facet name="header">Account</apex:facet>
      			<apex:outputLink value="{!URLFOR($Action.Account.View, acct.AccountId)}"> {!acct.AccountName} </apex:outputLink>                
                <apex:facet name="footer" ><apex:outputPanel > Total: &nbsp; 
                </apex:outputPanel> </apex:facet>
         </apex:column>
         <apex:column >
             <apex:facet name="header" >Amount</apex:facet>
             <apex:outputText value="{0,number,$###,###,##0}">
             <apex:param value="{!acct.AccountTotal}"/> </apex:outputText>    
             <apex:facet name="footer" ><apex:outputText value="{0,number,$###,###,##0}">
             <apex:param value="{!Acct90Total}"/> </apex:outputText></apex:facet>
        </apex:column>   
      </apex:pageBlockTable>
           
    <apex:pageBlockTable value="{!OppList90}" var="opp" captionClass="tableHeader">
          <apex:facet name="caption">Top 10 Opportunities at 90%</apex:facet>
        <apex:column > 
         	<apex:facet name="header">Account</apex:facet>
        <apex:outputLink value="{!URLFOR($Action.Account.View, opp.Account.Id)}"> {!opp.Account.Name} </apex:outputLink>
        </apex:column>
        <apex:column >
            <apex:facet name="header">Opportunity</apex:facet>
        <apex:outputLink value="{!URLFOR($Action.Opportunity.View, opp.Id)}" > {!opp.name} </apex:outputLink>
        </apex:column>
        
        <apex:column value="{!opp.CloseDate}"> 
        <apex:facet name="footer" ><apex:outputPanel > Total: &nbsp; 
        </apex:outputPanel> </apex:facet>
        </apex:column>
        <apex:column >
            <apex:facet name="header" >Amount</apex:facet>
            <apex:outputText value="{0,number,$###,###,##0}">
            <apex:param value="{!opp.Amount}"/> </apex:outputText> 
            <apex:facet name="footer" >
            <apex:outputText value="{0,number,$###,###,##0}">
            <apex:param value="{!opp90Total.Amount}"/> </apex:outputText> 
            </apex:facet>                
        </apex:column>
     </apex:pageBlockTable>
          
           </apex:pageBlockSection>
    </apex:pageBlock>    
    
        <apex:pageBlock >
       <apex:pageBlockSection columns="6">
           
        <apex:pageBlockTable value="{!AccountSummary60}" var="acct" captionClass="tableHeader">
            <apex:facet name="caption">Top 10 Accounts at 66%</apex:facet>
             <apex:column headerValue="Account">
           	  <apex:facet name="header">Account</apex:facet>
      		  <apex:outputLink value="{!URLFOR($Action.Account.View, acct.AccountId)}"> {!acct.AccountName} </apex:outputLink>             
            <apex:facet name="footer" >Total: &nbsp;</apex:facet>    
            </apex:column>
          <apex:column >
            <apex:facet name="header" >Amount</apex:facet>
            <apex:outputText value="{0,number,$###,###,##0}">
            <apex:param value="{!acct.AccountTotal}"/> </apex:outputText> 
            <apex:facet name="footer" ><apex:outputText value="{0,number,$###,###,##0}">
            <apex:param value="{!Acct60Total}"/> </apex:outputText></apex:facet>
          </apex:column>
       </apex:pageBlockTable>
           
    <apex:pageBlockTable value="{!OppList60}" var="opp" captionClass="tableHeader">
         <apex:facet name="caption">Top 10 Opportunities at 66%</apex:facet>
        <apex:column > 
            <apex:facet name="header">Account</apex:facet>
        <apex:outputLink value="{!URLFOR($Action.Account.View, opp.Account.Id)}"> {!opp.Account.Name} </apex:outputLink>
        </apex:column>
        <apex:column >
            <apex:facet name="header">Opportunity</apex:facet>
        	<apex:outputLink value="{!URLFOR($Action.Opportunity.View, opp.Id)}" > {!opp.name} </apex:outputLink>
        </apex:column>
        <apex:column value="{!opp.CloseDate}"> 
        <apex:facet name="footer" ><apex:outputPanel > Total: &nbsp; 
        </apex:outputPanel> </apex:facet>
        </apex:column>
        <apex:column >
            <apex:facet name="header" >Amount</apex:facet>
            <apex:outputText value="{0,number,$###,###,##0}">
            <apex:param value="{!opp.Amount}"/> </apex:outputText>             
            <apex:facet name="footer" >
            <apex:outputText value="{0,number,$###,###,##0}">
            <apex:param value="{!opp60Total.Amount}"/> </apex:outputText> 
            </apex:facet>
        </apex:column>
        
     </apex:pageBlockTable>
          
           </apex:pageBlockSection>
    </apex:pageBlock> 


    
</apex:page>

@isTest(SeeAllData=true)


Private class PersonalDashBoardTest {
    
    @isTest static void TestPersonalDashBoard() {
        PersonalDashTestData.createAccountWithOpps();
        PersonalDashTestData.createGoals();
        My_Plan__c plan = new My_Plan__c();
        plan.Name = 'test 123';
        insert(plan);

        
		Test.startTest();
        //Use the PageReference Apex class to instantiate a page
   //    PageReference pageRef = Page.AccountDashBoardPage;  // name of the page goes here
       
       //In this case, the Visualforce page named 'success' is the starting point of this test method. 
   //    Test.setCurrentPage(pageRef);
     
       //Instantiate and construct the controller class.
       Apexpages.currentPage().getParameters().put('Id', plan.Id);
       ApexPages.StandardController MyPlan = new ApexPages.StandardController(plan);   
       PersonalDashBoardClass controller = new PersonalDashBoardClass(MyPlan);
        controller.getAccountSummary60();
        controller.getAccountSummary90();
        controller.getAcct60Total();
        controller.getAcct90Total();
        controller.getOpp60Total();
        controller.getOpp90Total();

        
        
    

        
        test.stopTest();
        
    }

}

@isTest

public class PersonalDashTestData {
    
    public static List<Account> createAccountWithOpps() {
    
        Integer numAccts =3;
        List<Account> accts = new List<Account>();
        for(Integer i =0; i <=numAccts; i++) {
            Account a = new Account(Name = 'Test Account'+i);
            accts.add(a);
        }
        
        
        List<Opportunity> opp = new List<Opportunity>();
        for(Integer i = 0; i <- numAccts; i++) {
            Account acct = accts[i];
              Opportunity newOpp1 = new Opportunity(Name = 'Opp'+i, AccountId=acct.Id, Amount = 100000, StageName='W. Win', CloseDate=System.today() );
              opp.add(newOpp1);
              Opportunity newOpp2 = new Opportunity(Name = 'Opp'+i, AccountId=acct.Id, Amount = 100000, StageName='W. Win', CloseDate=System.today() );
            opp.add(newOpp2);
              Opportunity newOpp3 = new Opportunity(Name = 'Opp'+i, AccountId=acct.Id, Amount = 100000, StageName='W. Win', CloseDate=System.today().addMonths(4) );
                 opp.add(newOpp3);
            Opportunity newOpp90 = new Opportunity(Name = 'Opp'+i, AccountId=acct.Id, Amount = 100000, StageName='A. Pending Sale', CloseDate=System.today() );
           	  opp.add(newOpp90);
          
        }
        insert(opp);
        
       return(accts);
   }
    
    public static Goals__c createGoals() {
        Goals__c myGoal = new Goals__c(Name = 'My Goal', Revenue_Goal__c=300000, Margin_Goal__c = 150000, Start_Date_Of_New_Quarter__c = System.today() );
        insert(myGoal);
        return(myGoal);
        
    }
    

    
    
}