5

I have created a visualforce charting barSeries and it renders the results that I want aside from products with values of zero for the month.

The chart is a repeat business report that is meant to show how many quantities were sold during a particular month. Its intended to also show where products were not sold during a month so sales reps will know which products to target to customers.

The two things that this chart doesn't do yet that I would like it to are:

  • Group bars by product codes so legend shows product code's bar color.
  • show products with values of zero in addition to those with positive values.

What do I need to change in my code to achieve this?

Apex Class:

public with sharing class VFChartVolReportController {

    public VFChartVolReportController() {

    }


public Account acc {get;set;}   

 public VFChartVolReportController(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];  
           }
// Data class 
public class Data {   
public String name { get; set; }  
public Integer data1 { get; set; }     
public Data(String name, Integer data1) {       
this.name = name;       
this.data1 = data1;

} 
}   

public List<Data> getChartData(){       

List<AggregateResult> arList = [SELECT COUNT(Id) inv, Product__r.Name, CALENDAR_MONTH(Actual_Ship_Date__c) monthKey, Sum(Quantity__c) quantityCount  from Invoice_History__c  where Account__c=:acc.Id AND (Product__r.Name like '%Filter%' OR Product__r.Name like '%Rapicide%' OR Product__r.Name like '%Intercept%')  AND Actual_Ship_Date__c >= :Date.newInstance(2013,1,1) group by Product__r.Name, CALENDAR_MONTH(Actual_Ship_Date__c) ORDER BY CALENDAR_MONTH(Actual_Ship_Date__c) asc];   
List<Data> dataList = new List<Data>();       
for (AggregateResult ar : arList){        
Integer monthInt = Integer.valueOf(ar.get('monthKey'));
String monthName = monthFromInteger(monthInt); 
Integer quantities = Integer.valueOf(ar.get('quantityCount'));   
dataList.add(new Data(monthName, quantities)); 
}   
return dataList; 
}   
// helper method to translate integer month to month string 
public String monthFromInteger(Integer month){ 
String s; 
s = month == 1 ? 'Jan' : s; 
s = month == 2 ? 'Feb' : s; 
s = month == 3 ? 'Mar' : s; 
s = month == 4 ? 'Apr' : s; 
s = month == 5 ? 'May' : s; 
s = month == 6 ? 'Jun' : s; 
s = month == 7 ? 'Jul' : s; 
s = month == 8 ? 'Aug' : s; 
s = month == 9 ? 'Sep' : s; 
s = month == 10 ? 'Oct' : s; 
s = month == 11 ? 'Nov' : s; 
s = month == 12 ? 'Dec' : s;   

return s; 

}   

}

Portion of the VF Page:

<apex:page standardcontroller="Account" extensions="AccountSnapShot2,VFChartVolReportController" tabStyle="Account" sidebar="false" showHeader="false"> 
<apex:pageBlock >
  <apex:pageBlockSection >
  <td align="left">
        <table width="10" align="left">
        <tr><td align="right"><apex:chart height="240px" width="600" data="{!chartdata}" animate="true">
<apex:axis type="Category" position="bottom" fields="inv,name">
<apex:chartLabel orientation="vertical"/> </apex:axis>
<apex:axis type="Numeric" position="left" steps="5" fields="data1"/>

<apex:barSeries colorsProgressWithinSeries="false" tips="true" gutter="75" axis="left" orientation="vertical" xField="inv,name" yField="data1" stacked="true">
<apex:chartTips valueField="inv,name" height="50" width="120"/>
</apex:barSeries>

<apex:legend position="bottom"/>
</apex:chart>
</td>
</tr>
</table>
</td>
  </apex:pageBlockSection>
  </apex:pageBlock>
</apex:page>

This is what it looks like now: enter image description here

This is what I want it to look like: enter image description here

2

The answer is going to be long so please allow me to post another one.

If the data need to be obtained dynamically, then you should look into "Providing Chart Data via a JavaScript Array" which allows you to get the data via controller and process it before you pass it to chart function.

Create a map in controller.

private static Map<String, Map<String,Integer>> data;

Assuming you retrieve month name and each product and its quantity from database one by one, put all data into the map. Please note that you will need sorting or some modification of the code to show the graph sorted by month.

public static Map<String, Map<String,Integer>> getChartData() {
    data = new Map<String, Map<String,Integer>>();
    //use a loop and edit the code below to putData(retrieved month name, retrieved quantity, retrieved product code);
    for (AggregateResult  ar: HERE IS YOUR QUERY ) {
            data.putData(monthFromInteger(monthInt), Integer.valueOf(ar.get('quantityCount')), yourProductName);
    }
    return data;
}

private static void putData(String name, Integer quantity, String productCode){
    Map<String,Integer> productGroup = new Map<String,Integer>();
    if (data.containsKey(name)){
        productGroup = data.get(name);
    }
    productGroup.put(productCode, quantity);
    data.put(name, productGroup);
}

Once you have your data in a map, you can pass the values to javascript array using the method below.

<script>
    // Build the chart data array in JavaScript
    var data = new Array();
</script>

<apex:repeat value="{!data}" var="monthList">
<script type="text/javascript">
var product = {};
</script>
<apex:repeat value="{!data[monthList]}" var="productList">
    {!monthList} {!productList} {!data[monthList][productList]}<br/>
    <script type="text/javascript">
        product['{!productList}'] = {!data[monthList][productList]};
        product['name'] = '{!monthList}';
    </script>
</apex:repeat>
<script type="text/javascript">
data.push(product);
</script>
</apex:repeat>

And pass the array to apex chart.

<apex:chart data="data" height="400" width="500">
    <apex:legend position="left"/>
    <apex:axis type="Numeric" position="left" title="title" grid="true"
        fields="{!yfield}" dashSize="2">
        <apex:chartLabel />
    </apex:axis>
    <apex:axis type="Category" position="bottom" fields="name">
        <apex:chartLabel rotate="315"/>
    </apex:axis>
    <apex:barSeries orientation="vertical" axis="left" 
        xField="name" yField="{!yfield}" title="{!title}"/>
</apex:chart>

Hope this help!

| improve this answer | |
  • I guess the part I don't understand is how to create that loop to populate the data because what you put in the answer above is static data: "//use a loop and edit the code below to putData(retrieved month name, retrieved quantity, retrieved product code); putData('Jan', 50, 'data1'); putData('Jan', 20, 'data2'); putData('Jan', 15, 'data3'); putData('Feb', 30, 'data1'); putData('Feb', 25, 'data2'); putData('Feb', 20, 'data3');" – NewbieDeveloper27 Jul 31 '14 at 15:24
  • You can change that part to SOQL for loop. I edited my post. – lw29 Aug 1 '14 at 0:52
  • So your code above is in addition to the code i have already written or do i have to get rid of certain lines? I tried integrating it with my code and it keeps telling me that the variable arList doesn't exist – NewbieDeveloper27 Aug 1 '14 at 16:57
  • @lw29 - I helped NewbieDeveloper27 through the controller compile, but when she runs the VF page she gets: Error is in expression '{!data[monthList][productList]}' in component <apex:repeat> in page and Incorrect parameter type for subscript. Expected Text, received Number Can you check your VF code example and provide guidance to proceed? – Andy Boettcher Aug 1 '14 at 22:02
  • Would adding TEXT () help? – lw29 Aug 2 '14 at 1:35
1

For static grouped bar chart:

Do the product codes need to be obtained dynamically from database? If not you can create a grouped bar chart like this.

Your data class would look like below.

public class Data {   
   public String name { get; set; }  
   public Integer data1 { get; set; }   
   public Integer data2 { get; set; }  
   //add more data   
   public Data(String name, Integer data1, Integer data2, ...) {       
      this.name = name;       
      this.data1 = data1; //MF01-0013
      this.data2 = data2; //MF01-0059
      //add more data
   } 
}

And this would be your chart's code.

<apex:barSeries orientation="vertical" axis="left" 
xField="name" yField="data1,data2,..." title="MF01-0013,MF01-0059,..." />

If you want to change the chart's tooltips, please refer to this post.

| improve this answer | |
  • There are hundreds of product codes and they need to be obtained dynamically from the database. So creating a static grouped bar chart wouldn't work out. The graph is going to change depending on which account it is in. – NewbieDeveloper27 Jul 28 '14 at 13:43
  • Please see my another answer. – lw29 Jul 29 '14 at 2:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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