4

How do I show the count of the records based on the created date-month(monthly) on a bar chart using Visualforce charting. Any example of getting record count through SOQL is much appreciated. (Record count on X-axis, months on Y-axis). I went through the documentation but unable to figure out how to display it monthly.

1 Answer 1

4

Here is how to get the record count per year and month using SOQL with aggregate and date functions:

public with sharing class ChartController {

    public class Month {
        public Integer year {get; set;}
        public Integer month {get; set;}
        public Integer count {get; set;}
        public String yearMonth {get; set; }
        Month(Integer year, Integer month, Integer count) {
            this.year = year;
            this.month = month;
            this.count = count;
            this.yearMonth = year + '/' + month;
        }
    }

    public Month[] getMonths() {
        Month[] months = new Month[] {};
        for (AggregateResult ar : [
                select
                        CALENDAR_YEAR(CreatedDate) y,
                        CALENDAR_MONTH(CreatedDate) m,
                        COUNT(Id) c
                from Contact
                group by CALENDAR_YEAR(CreatedDate), CALENDAR_MONTH(CreatedDate)
                order by CALENDAR_YEAR(CreatedDate), CALENDAR_MONTH(CreatedDate)
                ]) {
            months.add(new Month(
                    (Integer) ar.get('y'),
                    (Integer) ar.get('m'),
                    (Integer) ar.get('c')
                    ));
        }
        return months;
    }
}

and the corresponding chart page:

<apex:page controller="ChartController">
    <apex:chart height="400" width="400" data="{!months}">
        <apex:axis type="Numeric" position="left" fields="count"
                title="Record Count"/>
        <apex:axis type="Category" position="bottom" fields="yearMonth"
                title="Year/Month"/>
        <apex:barSeries orientation="vertical" axis="left"
                xField="yearMonth" yField="count"/>
    </apex:chart>
</apex:page>

and an example of the output produced:

screen shot of chart

Some Apex code should be added to fill in the months that have no data.

2
  • Thanks Keith, is there anyway that I could stack the chart based on picklist values and display the month name instead of number?
    – cartman
    Nov 11, 2014 at 0:23
  • @cartman You can change the line this.yearMonth = year + '/' + month; to reference a map that translates the month number to a month name. And yes, you can add multiple fields instead of count and produce a stacked chart.
    – Keith C
    Nov 11, 2014 at 7:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .