1

Can any on ehelp me out how to create a Table in a visual force page which is in a PDF format , without repeating the same "product name" non grouped column data.

For Example : Here the Product Name as "BXRC-25e4000-F-04" appears twice in a Quarter for the months Jan and Feb.Now the Quantity & Amount Should get Added displayed at one place and the product name should get appeared only once.

The Table looks like :

ProductName             Quantity          Amount     Q1      Q2  Q3  Q4 Total
BXRC-25e4000-F-04          100             5000    100,5000
BXRC-25e4000-F-04          200             3000    200,3000

The quarter is calculated as Q1 = JAN+FEB+MAR Q2= Apr+may+jun similarly other q3 and q4.

But Im looking for the Table as :

ProductName             Quantity          Amount     Q1      Q2  Q3  Q4 Total
BXRC-25e4000-F-04          300             8000    300,8000       

I tried SOQL Using the Group By clause but it did not work out. Any help is very much appreciated .

CODE

public with sharing class QuoteContentController {

       public Competitor__c com{get;set;}

       public gmod__Opportunity_Forecast__c opflist{get;set;}

       public Id qId {get;set;}

       Public string all{get;set;}

//Declare a wrapper class  

       public class Wrapperclass{

 //custom wrapper datatype  

       Public string Name{get;set;}  
       Public string AccountType{get;set;}  
       Public date todaysDate{get;set;}  
       Public date Expected_Order_Date{get;set;}
       Public string Probability{get;set;}  
       Public string Internal_Comment{get;set;}  
       Public string External_Comment{get;set;}  

       Public string Segment{get;set;}  
       Public string Application{get;set;}  
       Public string Persona{get;set;}  
       Public string Geogrpahy{get;set;}  

       Public string PartNumbers{get;set;}  
       Public Decimal  Price{get;set;}  
       Public Decimal End_Customer_Price{get;set;}  
       Public Decimal Quantity {get;set;}  
       Public Decimal Total{get;set;}  

       Public string RFQ_justification{get;set;}  
       Public string Main_Customer_of_Account{get;set;}  
       Public string Bridgelux_competition_at_account{get;set;}
       Public string Geographic_regions_serviced{get;set;}  
       Public string Annual_lighting_revenue{get;set;}  
       Public string Annual_LED_revenue_or_percent{get;set;}  
       Public string Annual_purchases_of_LED_light_sources{get;set;}
       Public string Percent_of_LED_purchases_that_are_COB{get;set;}  
       Public string Other_information{get;set;}  

       Public string Product_Series{get;set;}  
       Public string Volume{get;set;} 
       Public string Date_Price_is_Valid{get;set;} 

       Public string gmod_Opportunity{get;set;}
       Public string gmod_Product{get;set;}
       Public Decimal gmod_Quantity{get;set;}
       Public Decimal gmod_Price{get;set;}
       Public Decimal gmod_Quarter{get;set;}
       Public Decimal gmod_Month{get;set;}
       Public Decimal gmod_Amount{get;set;}
       Public Decimal Actual_Price{get;set;}
       Public Decimal  gmod_Year{get;set;}
       Public Date gmod_date{get;set;}
       Public string gmod_Month_Text{get;set;}
       Public Date  Forecast_Date{get;set;}




       Public wrapperClass(){} 

  }

       Public QuoteContentController(){}

       Public QuoteContentController(ApexPages.StandardController controller) {

       qId=Apexpages.currentPage().getparameters().get('Id');

    }

        Public Integer subtotalofquantity{get;set;}

        Public Integer subtotalofamount{get;set;}

       Public List<wrapperClass> disp_list {get;set;}{

       subtotalofquantity = 0;

       subtotalofamount = 0;

     // Public List<wrapperClass> disp_list(){
    //define constructor to instantiate the wrapper data type 

       disp_list=new list<wrapperclass>();

     //Query all the list 

     list<Quote> q =[select id ,Name ,QuoteNumber,Effective_Date__c ,Comments__c ,Quote.Opportunity.id, 
                    Quote.Opportunity.Probability ,Quote.Opportunity.AccSegment__c ,Quote.Opportunity.AccApplication__c,Quote.Opportunity.Persona__c,Quote.Opportunity.Region__c
                    from Quote where id=:apexpages.currentpage().getparameters().get('id')];

    // list<QuoteLineItem> qli =[Select QuoteId , PricebookEntry.Product2.Name ,Unitprice ,End_Customer_Price__c ,Quantity , TotalPrice from QuoteLineItem where QuoteId =:qId ];

   //  list<Account> a = [Select id ,Name ,Main_Customers_of_Account__c ,Bridgelux_Competition_at_Account__c,Servicing_Region__c ,
                     // Annual_LED_Revenue__c,Annual_Purchases_of_LED_Sources_SAM__c ,Percent_of_LED_purchases_that_are_COB__c from Account where account.id =:qId ];

   //  list<Competitor__c>  com = [Select id ,Part_Number__c,Product_Series__c,Price_Offered__c,Volume__c,Date_Price_is_Valid__c from Competitor__c ]  ; 

     Opportunity opp =[select id , Name, (select id, Quantity, product2id from OpportunityLineItems), probability, AccSegment__c from Opportunity where opportunity.Id =:q[0].opportunity.id];

    list<gmod__Opportunity_Forecast__c>  opflist = [Select id ,Name ,gmod__opportunity__r.id,gmod__Product__c,gmod__Quantity__c,gmod__Price__c , gmod__Month__c,    gmod__date__c,  gmod__Quarter__c ,gmod__Amount__c ,Actual_Price__c ,gmod__Year__c ,gmod__Month_Text__c ,Forecast_Date__c,gmod__Product__r.Name ,gmod__opportunity__r.name from gmod__Opportunity_Forecast__c WHERE gmod__Product__c!=null and gmod__opportunity__r.id =:opp.id order by gmod__Month__c asc limit 9]; 

   // list<gmod__Opportunity_Forecast__c>  opflist=[ SELECT  id,gmod__product__r.name,gmod__Quantity__c,createdby.name , gmod__opportunity__r.name ,gmod__Amount__c,GROUPING(gmod__Quantity__c) grpQty, GROUPING(gmod__Amount__c) grpAmt ,COUNT(id) oppfc from gmod__Opportunity_Forecast__c GROUP BY CUBE(gmod__Quantity__c, gmod__Amount__c) ORDER BY GROUPING(gmod__Quantity__c), GROUPING(gmod__Amount__c) ];

  // Executes in DC 
  // [SELECT  gmod__product__r.name ,gmod__opportunity__c,COUNT(id) cnt FROM gmod__Opportunity_Forecast__c WHERE gmod__Product__c!=null GROUP BY  gmod__product__r.name ,gmod__opportunity__c];
//Executes in DC
 /*list<AggregateResult> groupedResults   =[SELECT  gmod__product__r.name ,gmod__opportunity__c,sum(gmod__Price__c) gmod__Price__c ,SUm(gmod__Amount__c) gmod__Amount__c,Sum(gmod__Quantity__c) gmod__Qunatity__c,COUNT(id) Quantity FROM gmod__Opportunity_Forecast__c WHERE  gmod__date__c=THIS_FISCAL_YEAR GROUP BY  gmod__product__r.name ,gmod__opportunity__c];

 for (AggregateResult ar : groupedResults)  {
          System.debug('sum amount' + ar.get('gmod__Amount__c'));
    System.debug('Count' + ar.get('Quantity'));


}*/

       //Iterate through each list to extract the values and add it to the custom wrapper data type  

        for (Quote qt :q){
             System.debug('Quote Size ++ '+q.size());
             System.debug('opp forcast ++ ' +opflist.size());

            for(integer i=0;i<opflist.size();i++){

            subtotalofquantity += integer.valueOf(opflist[i].gmod__Quantity__c);

            subtotalofamount  += integer.valueOf(opflist[i]. gmod__Amount__c);

            //for(gmod__Opportunity_Forecast__c op: opflist ){

                    //system.debug('Opp id++ '+Quote.Opportunity.id);
                    //if(qt.Quote.Opportunity.id == op.opportunity__r.id){

                                        //Instantiating the wrapper SObject 

                                        wrapperclass w = new wrapperclass();


                    //Assigning the wrapper variables from the SObject Fields in the database. 

                 w.gmod_Opportunity = opflist[i].gmod__Opportunity__r.Name;

                 w.gmod_Product = opflist[i].gmod__Product__r.Name;

                 w.gmod_Quantity =opflist[i].gmod__Quantity__c;

                 w.gmod_Price=opflist[i].gmod__Price__c;

                 w.Name =opflist[i].Name;

                 w.gmod_Quarter=opflist[i].gmod__Quarter__c;

                 w.gmod_Month=opflist[i].gmod__Month__c;

                 w.gmod_Amount=opflist[i]. gmod__Amount__c;

                 w.Actual_Price=opflist[i].Actual_Price__c;

                 w.gmod_Year=opflist[i].gmod__Year__c;

                 w.gmod_date=opflist[i].gmod__date__c;

                 w.gmod_Month_Text=opflist[i].gmod__Month_Text__c;

                 w.Forecast_Date=opflist[i].Forecast_Date__c;
                              //Adding everthing to the List  


                            // w.name =qt.name;

                              disp_list.add(w);

                              // return disp_list;  
                    //}         
       }
     }
   }  
 } //End of Class
7
  • Have you tried to use a SOQL aggregate function SUM()? Something like Select ProductName__c, SUM(Quantity__c), SUM(Amount__c) From Object__c Group By ProductName__c Commented Feb 18, 2015 at 10:34
  • @mast0r:I tried using the aggregate function as :SELECT gmod__product__r.name ,SUm(gmod__Amount__c) gmod__Amount__c,Sum(gmod__Quantity__c) gmod__Qunatity__c,COUNT(id) Quantity FROM gmod__Opportunity_Forecast__c GROUP BY gmod__product__r.name
    – nikkey
    Commented Feb 18, 2015 at 10:42
  • ...and which error did you get? Commented Feb 18, 2015 at 10:45
  • @mast0r:It works fine in Developer console by displaying the Amount and quantity with product name.When i tried in the controller class the same product name doesnot get grouped with the amount and qunatity.Instead It displays the product name twice in a quarter.
    – nikkey
    Commented Feb 18, 2015 at 10:53
  • Please post your controller code. I can help you out ! Commented Feb 18, 2015 at 11:10

2 Answers 2

0

It all depends on the amount of data, but if there are not that many to display you can add a method to your wrapper:

public void Add(yourObjectThatyouWrap y)

and put the addition in code

Use a map with a unique key to find out on what wrapper object you have to call this add method.

1
  • :Can u help me out with an example .gmod__Amount__c is an formula field .
    – nikkey
    Commented Feb 18, 2015 at 12:16
0

Assuming product Name as unique key, this should work :

public with sharing class QuoteContentController {

    public Competitor__c com{get;set;}
    public gmod__Opportunity_Forecast__c opflist{get;set;}
    public Id qId {get;set;}
    public string all{get;set;}

    //Declare a wrapper class  

    public class Wrapperclass{

    //custom wrapper datatype  

        public string Name{get;set;}  
        public string AccountType{get;set;}  
        public date todaysDate{get;set;}  
        public date Expected_Order_Date{get;set;}
        public string Probability{get;set;}  
        public string Internal_Comment{get;set;}  
        public string External_Comment{get;set;}  

        public string Segment{get;set;}  
        public string Application{get;set;}  
        public string Persona{get;set;}  
        public string Geogrpahy{get;set;}  

        public string PartNumbers{get;set;}  
        public Decimal  Price{get;set;}  
        public Decimal End_Customer_Price{get;set;}  
        public Decimal Quantity {get;set;}  
        public Decimal Total{get;set;}  

        public string RFQ_justification{get;set;}  
        public string Main_Customer_of_Account{get;set;}  
        public string Bridgelux_competition_at_account{get;set;}
        public string Geographic_regions_serviced{get;set;}  
        public string Annual_lighting_revenue{get;set;}  
        public string Annual_LED_revenue_or_percent{get;set;}  
        public string Annual_purchases_of_LED_light_sources{get;set;}
        public string Percent_of_LED_purchases_that_are_COB{get;set;}  
        public string Other_information{get;set;}  

        public string Product_Series{get;set;}  
        public string Volume{get;set;} 
        public string Date_Price_is_Valid{get;set;} 

        public string gmod_Opportunity{get;set;}
        public string gmod_Product{get;set;}
        public Decimal gmod_Quantity{get;set;}
        public Decimal gmod_Price{get;set;}
        public Decimal gmod_Quarter{get;set;}
        public Decimal gmod_Month{get;set;}
        public Decimal gmod_Amount{get;set;}
        public Decimal Actual_Price{get;set;}
        public Decimal  gmod_Year{get;set;}
        public Date gmod_date{get;set;}
        public string gmod_Month_Text{get;set;}
        public Date  Forecast_Date{get;set;}

        public wrapperClass(){} 
    }

    public QuoteContentController(){}

    public QuoteContentController(ApexPages.StandardController controller) {
        qId=Apexpages.currentPage().getparameters().get('Id');
        disp_Map = new Map<String,wrapperClass>();
    }

    public Integer subtotalofquantity{get;set;}
    public Integer subtotalofamount{get;set;}
    public Map<String,wrapperClass> disp_Map{get;set;}
    public List<wrapperClass> disp_list {get;set;}{
        disp_list=new list<wrapperclass>();

        list<Quote> q =[select id ,Name ,QuoteNumber,Effective_Date__c ,Comments__c ,Quote.Opportunity.id, 
                    Quote.Opportunity.Probability ,Quote.Opportunity.AccSegment__c ,Quote.Opportunity.AccApplication__c,Quote.Opportunity.Persona__c,Quote.Opportunity.Region__c
                    from Quote where id=:apexpages.currentpage().getparameters().get('id')];

        Opportunity opp =[select id , Name, (select id, Quantity, product2id from OpportunityLineItems), probability, AccSegment__c from Opportunity where opportunity.Id =:q[0].opportunity.id];

        list<gmod__Opportunity_Forecast__c>  opflist = [Select id ,Name ,gmod__opportunity__r.id,gmod__Product__c,gmod__Quantity__c,gmod__Price__c , gmod__Month__c,    gmod__date__c,  gmod__Quarter__c ,gmod__Amount__c ,Actual_Price__c ,gmod__Year__c ,gmod__Month_Text__c ,Forecast_Date__c,gmod__Product__r.Name ,gmod__opportunity__r.name from gmod__Opportunity_Forecast__c WHERE gmod__Product__c!=null and gmod__opportunity__r.id =:opp.id order by gmod__Month__c asc limit 9]; 
        for (Quote qt :q){
             System.debug('Quote Size ++ '+q.size());
             System.debug('opp forcast ++ ' +opflist.size());

            for(integer i=0;i<opflist.size();i++){
                subtotalofquantity += integer.valueOf(opflist[i].gmod__Quantity__c);
                subtotalofamount  += integer.valueOf(opflist[i]. gmod__Amount__c);
                wrapperclass w = new wrapperclass();
                if(disp_Map.containsKey(opflist[i].gmod__Product__r.Name)){
                    w = disp_Map.get(opflist[i].gmod__Product__r.Name);
                    w.gmod_Quantity =disp_Map.get(opflist[i].gmod__Product__r.Name).gmod_Quantity + opflist[i].gmod__Quantity__c;
                    w.gmod_Amount = disp_Map.get(opflist[i].gmod__Product__r.Name).gmod_Amount + opflist[i].gmod_Amount;
                } else {
                    w.gmod_Quantity =opflist[i].gmod__Quantity__c;
                    w.gmod_Amount=opflist[i]. gmod__Amount__c;
                }
                w.gmod_Opportunity = opflist[i].gmod__Opportunity__r.Name;
                w.gmod_Product = opflist[i].gmod__Product__r.Name;
                w.Name =opflist[i].Name;
                w.gmod_Quarter=opflist[i].gmod__Quarter__c;
                w.gmod_Month=opflist[i].gmod__Month__c;
                w.gmod_Amount=opflist[i]. gmod__Amount__c;
                w.Actual_Price=opflist[i].Actual_Price__c;
                w.gmod_Year=opflist[i].gmod__Year__c;
                w.gmod_date=opflist[i].gmod__date__c;
                w.gmod_Month_Text=opflist[i].gmod__Month_Text__c;
                w.Forecast_Date=opflist[i].Forecast_Date__c;
                disp_Map.put(opflist[i].gmod__Product__r.Name,w);
            }
        }
        for(wrapperclass w : disp_Map.values()){
            disp_list.add(w);
        }
    }  
}
7
  • :I get an Error as : Initial term of field expression must be a concrete SObject: Boolean at w.gmod_Quantity =disp_Map.containsKey(opflist[i].gmod__Product__r.Name.
    – nikkey
    Commented Feb 18, 2015 at 13:57
  • Hope you took the updated code. this line is not present in the updated code Commented Feb 19, 2015 at 5:51
  • :I have copied the updated code.The line is present ,i think it didnot copied correctly in the comment.Here is the line w.gmod_Quantity =disp_Map.get(opflist[i].gmod__Product__r.Name).gmod_Quantity + opflist[i].gmod__Quantity__c;
    – nikkey
    Commented Feb 19, 2015 at 8:45
  • can you try to copy this code and paste and let me know if same error comes, actually it should not Commented Feb 19, 2015 at 9:14
  • :hey i tried it once again ,it gives me the same error as Error: Initial term of field expression must be a concrete SObject: Boolean at line 157 column 104.The 157 line is w.gmod_Quantity =disp_Map.get(opflist[i].gmod__Product__r.Name).gmod_Quantity + opflist[i].gmod__Quantity__c;
    – nikkey
    Commented Feb 19, 2015 at 9:38

You must log in to answer this question.

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