+ Start a Discussion
Eric FortenberryEric Fortenberry 

Formatting Date on Visualforce Chart

I'm trying to recreate as a visualforce chart a graph of Open Tasks by Date for the next 30 days.  We have this as a report and dashboard component, but I want to drop it on the Task creation page to help reps schedule future tasks on days where they don't have a lot of tasks.

 

Here's the current dashboard component with accurate data that I'm trying to replicate:

 

Correct chart

 

Here's what I'm currently seeing on my visualforce page:

 

Current visualforce page

 

Here's the visualforce code:

<apex:page controller="TaskChartController" title="Bar Chart">
    <apex:chart height="350" width="450" data="{!results}" >
        <apex:axis type="Category" position="bottom" fields="ActivityDate" title="Date"/>
        <apex:axis type="Numeric" position="left" fields="Total" title="Open Tasks"/>
        <apex:barSeries axis="bottom" orientation="vertical" xField="ActivityDate" yField="Total"/>
    </apex:chart>
    <apex:dataTable value="{!results}" var="ar">
        <apex:column headerValue="Open Tasks" value="{!ar.Total}"/>
        <apex:column headerValue="Date">
            <apex:outputText value="{0,date,MM'/'dd'/'yyyy}">
                <apex:param value="{!ar.ActivityDate}"/>
            </apex:outputText>
        </apex:column>
    </apex:dataTable>
</apex:page>

 

Here's my apex controller code:

public class TaskChartController {

    public List<AggregateResult> lstAR = new List<AggregateResult>();
    
    public TaskChartController()
    {
        lstAR = [SELECT ActivityDate, COUNT(id) Total 
                FROM Task
                WHERE IsClosed = FALSE 
                AND ActivityDate = NEXT_N_DAYS:30
                GROUP BY ActivityDate];
    }
    
    public list<TaskClass> getResults()
    {
        list<TaskClass> lstResult = new List<TaskClass>();
        for (AggregateResult ar: lstAR)
        {
            taskClass objTaskClass = new taskClass(ar);
            lstResult.add(objTaskClass);
        }                       
        return lstResult;
    }
    
    class taskClass
    {
        public Integer Total
        {get; set;}
        
        public Date ActivityDate
        {get; set;}
            
        public taskClass(AggregateResult ar)
        {
            Total = (integer)ar.get('Total');
            ActivityDate = (Date)ar.get('ActivityDate');
        }
    }
}

 

So the issues I'm having are:

1)  Formatting the ActivityDate to MM-DD-YYYY on bottom axis. It seems ideal to do this in the controller... and to convert the date to a string, but I'm not exactly sure how to do that.

 

2)  Make sure all data columns are showing for the every date with data.  Right now only one column of data is showing.

 

Thanks for the help!

 

Paul.FoxPaul.Fox

Use this apex class. Note that I just did 7 days since this chart will probably not look that good with 30 days of data, although you can try switching it to a horizontal bar chart. You'll need to update the outputtext on the visualforce page but nothing else.

 

public class TaskChartController {

    public List<AggregateResult> lstAR = new List<AggregateResult>();
    
    public TaskChartController()
    {
        lstAR = [SELECT ActivityDate, COUNT(id) Total 
                FROM Task
                WHERE IsClosed = FALSE 
                AND ActivityDate = NEXT_N_DAYS:7
                GROUP BY ActivityDate];
    }
    
    public list<TaskClass> getResults()
    {
        list<TaskClass> TaskCounts = new list<TaskClass>();
        
        for(Date d = date.today(); d < date.today().addDays(7) ; d = d.addDays(1)){
        
            for (AggregateResult ar: lstAR)
            {
                Date ActivityDate = (Date) ar.get('ActivityDate');
                if(ActivityDate == d) TaskCounts.add(new TaskClass(ActivityDate, (integer) ar.get('Total')));
            }
            if(TaskCounts.size() == 0) TaskCounts.add(new TaskClass(d,0));
            if(TaskCounts[TaskCounts.size()-1].ActivityDate != d.format()){
                TaskCounts.add(new TaskClass(d,0));
            }
        
        }
                            
        return TaskCounts;
    }
    
    class taskClass
    {
        public Integer Total
        {get; set;}
        
        public string ActivityDate
        {get; set;}
            
        public taskClass(AggregateResult ar)
        {
            Total = (integer)ar.get('Total');
            Date d = (Date)ar.get('ActivityDate');
            ActivityDate = d.format();
        }
        
        public taskClass(date d, Integer i){
            Total = i;
            ActivityDate = d.format();
        }
    }
}

 

Eric FortenberryEric Fortenberry

The date is formated correctly!  However, I chnaged it back to 30 days and only certain dates are showing up (see below).  Is there a way to make the dates display vertically instead of horizontal?  Or at least at a diagonal angle, like they are in the regular dashboard component?

 

Here's what I'm seeing:

Graph

 

Eric FortenberryEric Fortenberry

Two more questions:

 

1)  I updated the apex class and visualforce page to include the standardController, so that I could then added the visualforce page to the Task page layout.  However, when I drop the visualforce page on to the task page, it is only showing up on the details view and not the create / edit view.  How can I get this chart to only show up when creating or editing a task?

 

2)  How can I update my query to only return tasks for the current user?  I know I need to add something like owner = { $user } to the WHERE clause, but can't seem to get the syntax right.

 

public with sharing class TaskChartController {
    
    //added an instance varaible for the standard controller
    private ApexPages.StandardController controller {get; set;}
    
    public List<AggregateResult> lstAR = new List<AggregateResult>();
    
    public TaskChartController(ApexPages.StandardController controller)
    {
        lstAR = [SELECT ActivityDate, COUNT(id) Total 
                FROM Task
                WHERE IsClosed = FALSE AND ActivityDate = NEXT_N_DAYS:30
                GROUP BY ActivityDate];
    }
      
    public list<TaskClass> getResults()
    {
        list<TaskClass> TaskCounts = new list<TaskClass>();
        
        for(Date d = date.today(); d < date.today().addDays(30) ; d = d.addDays(1)){
        
            for (AggregateResult ar: lstAR)
            {
                Date ActivityDate = (Date) ar.get('ActivityDate');
                if(ActivityDate == d) TaskCounts.add(new TaskClass(ActivityDate, (integer) ar.get('Total')));
            }
            if(TaskCounts.size() == 0) TaskCounts.add(new TaskClass(d,0));
            if(TaskCounts[TaskCounts.size()-1].ActivityDate != d.format()){
                TaskCounts.add(new TaskClass(d,0));
            }
        }               
        return TaskCounts;
    }
    
    class taskClass
    {
        public Integer Total
        {get; set;}
        
        public string ActivityDate
        {get; set;}
            
        public taskClass(AggregateResult ar)
        {
            Total = (integer)ar.get('Total');
            Date d = (Date)ar.get('ActivityDate');
            ActivityDate = d.format();
        }
        
        public taskClass(date d, Integer i){
            Total = i;
            ActivityDate = d.format();
        }
    }
}

 

Eric FortenberryEric Fortenberry

I just noticed on the chart, that some numbers are being repeated (18, 24, 25, and 29).  Any ideas?

 

Chart

 

public with sharing class TaskChartController {
    
    //added an instance varaible for the standard controller
    private ApexPages.StandardController controller {get; set;}
    
    public List<AggregateResult> lstAR = new List<AggregateResult>();
    
    public TaskChartController(ApexPages.StandardController controller)
    {
        lstAR = [SELECT ActivityDate, COUNT(id) Total 
                FROM Task
                WHERE OwnerId = :UserInfo.getUserId() AND IsClosed = FALSE 
AND ActivityDate = NEXT_N_DAYS:30 GROUP BY ActivityDate]; } public list<TaskClass> getResults() { list<TaskClass> TaskCounts = new list<TaskClass>(); for(Date d = date.today(); d < date.today().addDays(30) ; d = d.addDays(1)){ for (AggregateResult ar: lstAR) { Date ActivityDate = (Date) ar.get('ActivityDate'); if(ActivityDate == d) TaskCounts.add(new TaskClass(ActivityDate, (integer) ar.get('Total'))); } if(TaskCounts.size() == 0) TaskCounts.add(new TaskClass(d,0)); if(TaskCounts[TaskCounts.size()-1].ActivityDate != d.format()){ TaskCounts.add(new TaskClass(d,0)); } } return TaskCounts; } class taskClass { public Integer Total {get; set;} public string ActivityDate {get; set;} public taskClass(DateTime d, Integer i){ Total = i; ActivityDate = d.format('d'); } } }

 

cwall_sfdccwall_sfdc

apex:chartLabel.rotate.  See docs.

 

<apex:axis type="Category" position="bottom" fields="name"  title="Month of the Year">
        <apex:chartLabel rotate="315"/>
    </apex:axis>