Hi,

I have aggregate results and for my bar chart I would like to display Net Sales and Year. Net Sales will be populatd from one of two fields, either US net sale or Canadian net sale, based on customer region. Is there any way, I could get this condional selection in apex? 

Three Fields:
1. Currency - currency values could be either “US” or “CAD”.
2. NetUS – this field holds total sale in US dollars.
3. NetCAD – this field holds total sale in Canadian dollars.
 
IF Currency = “CAD” THEN
Net_Sale_in_CAD
ELSE
Net_Sale_in_US
 
I want to publish the results in Visualforce Chart, based on currency either “NetUS” or “NetCDN” but not both. 
 
This is urgent all the help will be appreciated. I would appreciate, if you include the code. Please see my Apex class and Visualforce page below. Thanks.
 
Apex Class
 
public with sharing class SaleSum {
 
    public Summary[] Summaries { get; set; }
 
 public SaleSum() {
        AggregateResult[] results = [
        Select Brand__c,
CALENDAR_MONTH(Invoice__r.Invoice_Date__c) InvMn,
CALENDAR_YEAR(Invoice__r.Invoice_Date__c) InvYr,
            SUM(Net_Amount_US__c) NetUS,
            SUM(Net_Amount_CDN__c) NetCDN,             
            Invoice__r.Account__r.Currency_Id__c,     
            Invoice__r.Account__r.Customer_Id__c
        From Invoice_Line__c
        Group By Brand__c,
            CALENDAR_MONTH(Invoice__r.Invoice_Date__c), 
            CALENDAR_YEAR(Invoice__r.Invoice_Date__c),
            Invoice__r.Account__r.Currency_Id__c,
            Invoice__r.Account__r.Customer_Id__c
        ];
       
        Summaries = new List<Summary>();
        for (AggregateResult gr : results) {
            Summaries.add(new Summary(gr));
        }
    }
 
    public class Summary {
        public Decimal NetUS { get; private set; }
        public Decimal NetCDN { get; private set; }
        public String Brand { get; private set; }
        public Integer InvMn { get; private set; }
        public integer InvYr { get; private set; }
        public String Curren { get; private set; }
        public String Customer { get; private set; }
       
   public Summary(AggregateResult gr) {
            NetUS = (Decimal) gr.get('NetUS');
            NetCDN = (Decimal) gr.get('NetCDN');
            Brand = (String) gr.get('Brand__c');
            InvMn = (Integer) gr.get('InvMn');
            InvYr = (Integer) gr.get('InvYr');
            Currency = (String) gr.get('Currency_Id__c');
            Customer = (String) gr.get('Customer_Id__c');
        }
    }
 
}
             
 
Visualforce Page:
<apex:page controller="GraphController" title="Annual Sale">
<apex:chart data="{!Summaries}" hidden="false" rendered="true" width="400" height="400">
<apex:axis type="Numeric" position="left" fields="NetUS" title="Net Sales"/>
<apex:axis type="Category" position="bottom" fields="InvYr" title="Year"/>
<apex:barSeries orientation="vertical" axis="left" xField="InvYr" yField="NetUS"/>
</apex:chart>

<apex:dataTable value="{!Summaries}" var="ar">
<apex:column headerValue="Year :" value="{!ar.InvYr}"/>
<apex:column headerValue="Net Sales US :" value="{!ar.NetUS}"/>
</apex:dataTable>
</apex:page>
 

Hi,
 
I would like to display data in tabular format, as below:
 
Customer        Brand         Series     Year    January    February   March     April        May         June                     
Best Solution    Apple          S5          2015    $20,500     $15,300     $12,200    $16,000    $23,700     $28,900
Best Solution    Samsung     A6          2015    $21,000     $23,400     $15,440    $19,700    $25,300     $32,430
Best Solution    Apple          S5          2016    $18,200     $11,100     $18,900    $11,000    $24,600   

My question is what should I do to get my data in above layout. I will appreciate your help, please include code to show to how to write code and layout the fields to get the above output, as I am new to Apex and Visualforce. Thanks. 
 
Please see my apex class and Visualforce page. My apex class and vf page are producing results result as follows:
 
Customer          Brand          Series     Year    Month     Net Sale
Best Solution    Apple          S5          2015    1              $20,500  
Best Solution    Apple          S5          2015    2              $15,300
...
...
...
Best Solution    Apple          S5          2016    1              $18,200
...
...
Best Solution    Samsung     A6          2015    1              $21,000  
...
...
   
public with sharing class SaleSum {
 
    public Summary[ ] Summaries { get; set; }
 
 public SaleSum() {
        AggregateResult[ ] results = [
        Select Brand__c, CALENDAR_MONTH(Invoice__r.Invoice_Date__c) InvMn, CALENDAR_YEAR(Invoice__r.Invoice_Date__c) InvYr,
            SUM(Net_Amount_US__c) NetUS, SUM(Net_Amount_CDN__c) NetCDN, Invoice__r.Account__r.Currency_Id__c,     
            Invoice__r.Account__r.Customer_Id__c
        From Invoice_Line__c
        Group By Brand__c, CALENDAR_MONTH(Invoice__r.Invoice_Date__c), CALENDAR_YEAR(Invoice__r.Invoice_Date__c),
            Invoice__r.Account__r.Currency_Id__c, Invoice__r.Account__r.Customer_Id__c
        ];
       
        Summaries = new List<Summary>();
        for (AggregateResult gr : results) {
            Summaries.add(new Summary(gr));
        }
    }
 
    public class Summary {
        public Decimal NetUS { get; private set; }
        public Decimal NetCDN { get; private set; }
        public String Brand { get; private set; }
        public Integer InvMn { get; private set; }
        public integer InvYr { get; private set; }
        public String Curren { get; private set; }
        public String Customer { get; private set; }
       
   public Summary(AggregateResult gr) {
            NetUS = (Decimal) gr.get('NetUS');
            NetCDN = (Decimal) gr.get('NetCDN');
            Brand = (String) gr.get('Brand__c');
            InvMn = (Integer) gr.get('InvMn');
            InvYr = (Integer) gr.get('InvYr');
            Currency = (String) gr.get('Currency_Id__c');
            Customer = (String) gr.get('Customer_Id__c');
        }
    }
 
}
  <apex:page controller="TestController" title="Monthly Net Amount by Customer" >
    <apex:pageBlock title="Monthly Net Amount by Customer">
    <table>
    <tr>
            <td>Customer</td>
            <td>Brand</td>
            <td>Series</td>
            <td>Year</td>
            <td>Month</td>
            <td>Net Sale</td>
    </tr>
   
<apex:repeat value="{!Summaries}" var="summary">
           <tr>
                <td><apex:outputText value="{!summary.Name}"/></td>               
                <td><apex:outputText value="{!summary.Brand}"/></td>
                <td><apex:outputText value="{!summary.Series}"/></td>
                <td><apex:outputText value="{!summary.InvYr}"/></td>
                <td><apex:outputText value="{!summary.InvMn}"/></td>
                <td><apex:outputText value="{!summary.NetUS}" rendered="{!IF(summary.Curren='US',true,false)}"/></td>
                <td><apex:outputText value="{!summary.NetCDN}" rendered="{!IF(summary.Curren='CAD',true,false)}"/></td>
            </tr>
      </apex:repeat>
      </table>
    </apex:pageBlock>
</apex:page>
 

Hi,

I would like to get a data from one of the two fields based on the value of third field. So it is conditional output.

Three Fields:
1. Currency - currency values could be either “US” or “CAD”.
2. NetUS – this field holds total sale in US dollars.
3. NetCAD – this field holds total sale in Canadian dollars.
 
IF Currency = “CAD” THEN
Net_Sale_in_CAD
ELSE
Net_Sale_in_US
 
I want to publish the results in Visualforce Page, based on currency either “NetUS” or “NetCDN” but not both. 
 
What would be the best way to accomplish this? I would appreciate, if you include the code. Please see my Apex class and Visualforce page below. Thanks.
   
public with sharing class SaleSum {
 
    public Summary[] Summaries { get; set; }
 
 public SaleSum() {
        AggregateResult[] results = [
        Select Brand__c,
CALENDAR_MONTH(Invoice__r.Invoice_Date__c) InvMn,
CALENDAR_YEAR(Invoice__r.Invoice_Date__c) InvYr,
            SUM(Net_Amount_US__c) NetUS,
            SUM(Net_Amount_CDN__c) NetCDN,             
            Invoice__r.Account__r.Currency_Id__c,     
            Invoice__r.Account__r.Customer_Id__c
        From Invoice_Line__c
        Group By Brand__c,
            CALENDAR_MONTH(Invoice__r.Invoice_Date__c), 
            CALENDAR_YEAR(Invoice__r.Invoice_Date__c),
            Invoice__r.Account__r.Currency_Id__c,
            Invoice__r.Account__r.Customer_Id__c
        ];
       
        Summaries = new List<Summary>();
        for (AggregateResult gr : results) {
            Summaries.add(new Summary(gr));
        }
    }
 
    public class Summary {
        public Decimal NetUS { get; private set; }
        public Decimal NetCDN { get; private set; }
        public String Brand { get; private set; }
        public Integer InvMn { get; private set; }
        public integer InvYr { get; private set; }
        public String Curren { get; private set; }
        public String Customer { get; private set; }
       
   public Summary(AggregateResult gr) {
            NetUS = (Decimal) gr.get('NetUS');
            NetCDN = (Decimal) gr.get('NetCDN');
            Brand = (String) gr.get('Brand__c');
            InvMn = (Integer) gr.get('InvMn');
            InvYr = (Integer) gr.get('InvYr');
            Currency = (String) gr.get('Currency_Id__c');
            Customer = (String) gr.get('Customer_Id__c');
        }
    }
 
}
             
   
<apex:page controller="SaleSum">
<apex:form >
<apex:repeat value="{!Summaries}" var="summary">
{!summary.Customer}
{!summary.Brand}
{!summary.Series}
{!summary.Currency}
{!summary.InvMn}
{!summary.InvYr}
{!summary.NetUS}
{!summary.NetCDN}
<br/>
</apex:repeat>
</apex:form>
</apex:page>
 

Hi,

I need to join four objects for my Visualforce page.
 
I am trying to get customer info, ordered products, and invoiced amount. For example Customer “John” ordered product “ABC” and invoiced amount is “$1200”. In order to get this done, I need to join four objects. Two objects are custom objects and two standard objects.       
 
Objects are: Account, Invoice__c, Invoice_Line__c, Product2
 
Account à Invoice__c à Invoice_Line__c
                   Product2    à Invoice_Line__c
 
Invoice__c object has Account__c lookup column to Account.Id column for relationship
 
Invoice_Line__c object has Invoice__c lookup column to Invoice__c.Id column for relationship
 
Invoice_Line__c object has Product__c lookup column to Product2.Id column for relationship
 
 
I need to merge these four objects to get the following information:
 
Account.Customer_Id__c, Account.Name from Account object
 
Invoice__c.Invoice_Id__c, Invoice__c.Invoice_Date__c from Invoice__c object
 
Invoice_Line__c.Brand__c, Invoice_Line__c.Name, Invoice_Line__c.Net_Amount_US__c from Invoic_Line__c object
 
Product2.ID__c, Product2.Brand__c, Product2.Description__c, Product2.Family, Product2.Product_Line__c, Product2.Product_Series__c from Product2 object
 
I am new to SOQL and Visualforce. What is the best way to get it done? Your help will be appreciated.
 

Hi,
 
I would like to display data in tabular format, as below:
 
Customer        Brand         Series     Year    January    February   March     April        May         June                     
Best Solution    Apple          S5          2015    $20,500     $15,300     $12,200    $16,000    $23,700     $28,900
Best Solution    Samsung     A6          2015    $21,000     $23,400     $15,440    $19,700    $25,300     $32,430
Best Solution    Apple          S5          2016    $18,200     $11,100     $18,900    $11,000    $24,600   

My question is what should I do to get my data in above layout. I will appreciate your help, please include code to show to how to write code and layout the fields to get the above output, as I am new to Apex and Visualforce. Thanks. 
 
Please see my apex class and Visualforce page. My apex class and vf page are producing results result as follows:
 
Customer          Brand          Series     Year    Month     Net Sale
Best Solution    Apple          S5          2015    1              $20,500  
Best Solution    Apple          S5          2015    2              $15,300
...
...
...
Best Solution    Apple          S5          2016    1              $18,200
...
...
Best Solution    Samsung     A6          2015    1              $21,000  
...
...
   
public with sharing class SaleSum {
 
    public Summary[ ] Summaries { get; set; }
 
 public SaleSum() {
        AggregateResult[ ] results = [
        Select Brand__c, CALENDAR_MONTH(Invoice__r.Invoice_Date__c) InvMn, CALENDAR_YEAR(Invoice__r.Invoice_Date__c) InvYr,
            SUM(Net_Amount_US__c) NetUS, SUM(Net_Amount_CDN__c) NetCDN, Invoice__r.Account__r.Currency_Id__c,     
            Invoice__r.Account__r.Customer_Id__c
        From Invoice_Line__c
        Group By Brand__c, CALENDAR_MONTH(Invoice__r.Invoice_Date__c), CALENDAR_YEAR(Invoice__r.Invoice_Date__c),
            Invoice__r.Account__r.Currency_Id__c, Invoice__r.Account__r.Customer_Id__c
        ];
       
        Summaries = new List<Summary>();
        for (AggregateResult gr : results) {
            Summaries.add(new Summary(gr));
        }
    }
 
    public class Summary {
        public Decimal NetUS { get; private set; }
        public Decimal NetCDN { get; private set; }
        public String Brand { get; private set; }
        public Integer InvMn { get; private set; }
        public integer InvYr { get; private set; }
        public String Curren { get; private set; }
        public String Customer { get; private set; }
       
   public Summary(AggregateResult gr) {
            NetUS = (Decimal) gr.get('NetUS');
            NetCDN = (Decimal) gr.get('NetCDN');
            Brand = (String) gr.get('Brand__c');
            InvMn = (Integer) gr.get('InvMn');
            InvYr = (Integer) gr.get('InvYr');
            Currency = (String) gr.get('Currency_Id__c');
            Customer = (String) gr.get('Customer_Id__c');
        }
    }
 
}
  <apex:page controller="TestController" title="Monthly Net Amount by Customer" >
    <apex:pageBlock title="Monthly Net Amount by Customer">
    <table>
    <tr>
            <td>Customer</td>
            <td>Brand</td>
            <td>Series</td>
            <td>Year</td>
            <td>Month</td>
            <td>Net Sale</td>
    </tr>
   
<apex:repeat value="{!Summaries}" var="summary">
           <tr>
                <td><apex:outputText value="{!summary.Name}"/></td>               
                <td><apex:outputText value="{!summary.Brand}"/></td>
                <td><apex:outputText value="{!summary.Series}"/></td>
                <td><apex:outputText value="{!summary.InvYr}"/></td>
                <td><apex:outputText value="{!summary.InvMn}"/></td>
                <td><apex:outputText value="{!summary.NetUS}" rendered="{!IF(summary.Curren='US',true,false)}"/></td>
                <td><apex:outputText value="{!summary.NetCDN}" rendered="{!IF(summary.Curren='CAD',true,false)}"/></td>
            </tr>
      </apex:repeat>
      </table>
    </apex:pageBlock>
</apex:page>
 

Hi,

I would like to get a data from one of the two fields based on the value of third field. So it is conditional output.

Three Fields:
1. Currency - currency values could be either “US” or “CAD”.
2. NetUS – this field holds total sale in US dollars.
3. NetCAD – this field holds total sale in Canadian dollars.
 
IF Currency = “CAD” THEN
Net_Sale_in_CAD
ELSE
Net_Sale_in_US
 
I want to publish the results in Visualforce Page, based on currency either “NetUS” or “NetCDN” but not both. 
 
What would be the best way to accomplish this? I would appreciate, if you include the code. Please see my Apex class and Visualforce page below. Thanks.
   
public with sharing class SaleSum {
 
    public Summary[] Summaries { get; set; }
 
 public SaleSum() {
        AggregateResult[] results = [
        Select Brand__c,
CALENDAR_MONTH(Invoice__r.Invoice_Date__c) InvMn,
CALENDAR_YEAR(Invoice__r.Invoice_Date__c) InvYr,
            SUM(Net_Amount_US__c) NetUS,
            SUM(Net_Amount_CDN__c) NetCDN,             
            Invoice__r.Account__r.Currency_Id__c,     
            Invoice__r.Account__r.Customer_Id__c
        From Invoice_Line__c
        Group By Brand__c,
            CALENDAR_MONTH(Invoice__r.Invoice_Date__c), 
            CALENDAR_YEAR(Invoice__r.Invoice_Date__c),
            Invoice__r.Account__r.Currency_Id__c,
            Invoice__r.Account__r.Customer_Id__c
        ];
       
        Summaries = new List<Summary>();
        for (AggregateResult gr : results) {
            Summaries.add(new Summary(gr));
        }
    }
 
    public class Summary {
        public Decimal NetUS { get; private set; }
        public Decimal NetCDN { get; private set; }
        public String Brand { get; private set; }
        public Integer InvMn { get; private set; }
        public integer InvYr { get; private set; }
        public String Curren { get; private set; }
        public String Customer { get; private set; }
       
   public Summary(AggregateResult gr) {
            NetUS = (Decimal) gr.get('NetUS');
            NetCDN = (Decimal) gr.get('NetCDN');
            Brand = (String) gr.get('Brand__c');
            InvMn = (Integer) gr.get('InvMn');
            InvYr = (Integer) gr.get('InvYr');
            Currency = (String) gr.get('Currency_Id__c');
            Customer = (String) gr.get('Customer_Id__c');
        }
    }
 
}
             
   
<apex:page controller="SaleSum">
<apex:form >
<apex:repeat value="{!Summaries}" var="summary">
{!summary.Customer}
{!summary.Brand}
{!summary.Series}
{!summary.Currency}
{!summary.InvMn}
{!summary.InvYr}
{!summary.NetUS}
{!summary.NetCDN}
<br/>
</apex:repeat>
</apex:form>
</apex:page>
 

Hi,

I need to join four objects for my Visualforce page.
 
I am trying to get customer info, ordered products, and invoiced amount. For example Customer “John” ordered product “ABC” and invoiced amount is “$1200”. In order to get this done, I need to join four objects. Two objects are custom objects and two standard objects.       
 
Objects are: Account, Invoice__c, Invoice_Line__c, Product2
 
Account à Invoice__c à Invoice_Line__c
                   Product2    à Invoice_Line__c
 
Invoice__c object has Account__c lookup column to Account.Id column for relationship
 
Invoice_Line__c object has Invoice__c lookup column to Invoice__c.Id column for relationship
 
Invoice_Line__c object has Product__c lookup column to Product2.Id column for relationship
 
 
I need to merge these four objects to get the following information:
 
Account.Customer_Id__c, Account.Name from Account object
 
Invoice__c.Invoice_Id__c, Invoice__c.Invoice_Date__c from Invoice__c object
 
Invoice_Line__c.Brand__c, Invoice_Line__c.Name, Invoice_Line__c.Net_Amount_US__c from Invoic_Line__c object
 
Product2.ID__c, Product2.Brand__c, Product2.Description__c, Product2.Family, Product2.Product_Line__c, Product2.Product_Series__c from Product2 object
 
I am new to SOQL and Visualforce. What is the best way to get it done? Your help will be appreciated.