1

I want to create a datatable with opportunity information. I add a picture that shows how the table should look like. I have the information for the table ( below is my class code), but I don't know how can I put this information into a table which looks like in my picture.

In the first row are the recordtypes (products), this should be dynamic. Because if I add a new product to the org this should also than in this report.

In the first column are the accountowner, this should also be dynamic. Because if I add a new owner to the org this should also than in this report.

And in the cell B2 should the amount of all opportunities from accountowner 1 with the recordtype 1.

example

Public List <SelectOption> getListOfTeams() {
  List <AggregateResult> Teams = [SELECT Team__c FROM User WHERE Team__c LIKE '%Team%' GROUP BY Team__c];
  List <SelectOption> TeamOptionList = new List <SelectOption>();
  TeamOptionList.add (new SelectOption('',''));
  FOR (AggregateResult AR : Teams) {
    TeamOptionList.add (new SelectOption((String)ar.get('Team__c'),(String)ar.get('Team__c')));
}
RETURN TeamOptionList;
}

Public List <User> getUserList2() {
  List <User> UserList = [SELECT Id, Name, Team__c FROM User WHERE Team__c = :SelectedTeamId];
    RETURN UserList;
}

Public pageReference getUserList() {
  getUserList2(); 
  RETURN NULL;
}

Public List <RecordType> getRTypeList() {
  RTypeList = [SELECT Id, Name FROM RecordType WHERE SobjectType = 'Opportunity'];
  RETURN RTypeList;
}    

Public void getauswertung() {
  getRTypeList();
  getInformation();
}

Public void getInformation() {

Summaries = new List<Summary>();
    FOR (RecordType RType : RTypeList) {

AggregateResult[] Results = [SELECT Account.Owner.Name accOwner, RecordType.Name oppProduct, SUM(Amount) PlanValue FROM Opportunity WHERE RecordTypeId = :RType.Id AND Account.Team_Unternehmen__c = :SelectedTeamId GROUP BY Account.Owner.Name, RecordType.Name];

        FOR (AggregateResult ar : Results) {
            Summaries.ADD(new Summary(ar));
        }
}

}

Public Class Summary {
  Public String Ownername {get; set;}
  Public String Productname {get; set;}    
  Public Double Value {get; set;}

Public Summary (AggregateResult ar) {
  Ownername = (String) ar.get('accOwner');
  Productname = (String) ar.get('oppProduct');    
  Value = (Double) ar.get('PlanValue');
}
}

Thanks.

VISUALFORCE

<apex:pageBlock id="pbSearch" title="Suchbereich">
<apex:pageBlockButtons location="top" >
    <apex:commandButton action="{!getAuswertung}" value="Suchen..." id="SearchButton" />    
</apex:pageBlockButtons>         
        <apex:outputLabel value="Team:    "/>
        <apex:selectList value="{!SelectedTeamId}" size="1" multiselect="false" style="width:150px">
            <apex:selectOptions value="{!ListOfTeams}" />             
        </apex:selectList>    

<apex:pageblock >
<apex:pageBlockTable value="{!Summaries}" var="item">
  <apex:column headervalue="Record Type"/> 
      <apex:repeat var="v" value="{!RTypeList}">
    <apex:column headerValue="{!v.Name}">    
       <apex:outputText value="{!item.Value}"/>
      </apex:column>
    </apex:repeat>   
  </apex:pageBlockTable>
</apex:pageblock>

EDIT 16.07.15
example This is the output at the moment. At the moment the AccountOwner is missing and the values shows in each row the same value. Each row shows each recordtpyevalue.

EDIT 16.07.15
example

EDIT 17.07.15
example

VISUAL PAGE

     <table class="list" border="0" cellpadding="0" cellspacing="0">
      <tr class="headerRow">
          <td></td>
          <apex:repeat value="{!RTypeList}" var="rt">
              <td>{!rt.Name}</td>
          </apex:repeat>
      </tr>

  <apex:repeat value="{!UserList}" var="u">
  <tr>
      <td>{!u.Name}</td>
          <apex:repeat value="{!Summaries}" var="v">
      <td>{!v.Value}</td>
  </apex:repeat>
  </tr>
  </apex:repeat>
</table>

CLASS

Public List <RecordType> getRTypeList() {
  RTypeList = [SELECT Id, Name FROM RecordType WHERE SobjectType = 'Opportunity'];
  RETURN RTypeList;
}    

Public List <User> getUserList() {
  UserList = [SELECT Id, Name FROM User WHERE Team__c = :SelectedTeamId];
  RETURN UserList;
}   

Public void getauswertung() {
  getRTypeList();
  getUserList();
  getInformation();
}

Public void getInformation() {
Summaries = new List<Summary>();

FOR (User UserName : UserList) {
    FOR (RecordType RType : RTypeList) {
        AggregateResult[] Results = [SELECT Account.Owner.Name accOwner, RecordType.Name oppProduct, SUM(Amount) PlanValue FROM Opportunity WHERE RecordTypeId = :RType.Id AND Account.Owner.Id = :UserName.Id GROUP BY Account.Owner.Name, RecordType.Name];
        FOR (AggregateResult ar : Results) {
            Summaries.ADD(new Summary(ar));
        }
    }
}
}


EDIT 30.07.15

I have this code from another page, is that the correct way to have a solution for my case? If yes, can anybody help me to adjust this code.

public class DynamicColumnTableController {

public List<Rows> listRows {get;set;}
public List<String> listHeaders {get;set;}

public Integer numberofColumn {get;set;}

public DynamicColumnTableController() {
    numberofColumn = 5;
    refreshTable();
}

public PageReference refreshTable() {
    try {
            listHeaders  = new List<String>();
            // add header text
            for( Integer i = 1 ; i <= numberofColumn; i++ ) {
                listHeaders.add( ' Header ' + i.format() );
            }

            listRows = new List<Rows>();
            listRows.add( new Rows( -1, new List<String>() ));
          // add data rows
          for( Integer j = 0;  j <= 2; j++ ) {

                List<String> columnDataValues = new List<String>();
                Integer i = 0;
                // add values
                for( String headerName : listHeaders ) {
                    columnDataValues.add( ' H ' + j.format()+ i.format() );
                    i++;
                }
                Rows rowObj =  new Rows( j, columnDataValues);
                listRows.add(rowObj);
            }
    }
    catch(Exception e ) {
        ApexPages.addMessages(e);
    }
    return ApexPages.currentPage();
}

public class Columns {
    Public String columnValue {get;set;}
    public Integer columnIndex {get;set;}
    public Columns( Integer colIndex, String dataValue ) {
      this.columnIndex = colIndex;
        this.columnValue = dataValue;
    }
}

public class Rows {

    public List<Columns> columnData {get;set;}
    public Integer rowIndex {get;set;}
    public Boolean isHeader {get;set;}

    public Rows( Integer rowIndex, List<String> dataValues ) {
        this.rowIndex = rowIndex;
        this.isHeader = ( rowIndex == -1 );
        columnData  = new List<Columns>();
        Integer i = 0;
        for( String dataValue : dataValues ) {
            this.columnData.add( new Columns( i, dataValue ));
            i++;                    
        }
    }
}

}
4
  • What have you tried? Where are you stuck? What does your VF markup look like? We will need something a lot more specific than a wishful image and a large dump of code to assist you with your issue. – Mark Pond Jul 15 '15 at 7:19
  • Above you will find the visualfoce code. At the moment I just get all in a list. But I want to add this results into the table, but I don't know how I can do that, because how can I told the table to put into cell B2 the value from accountowner1 with recordtype1? – peXeq Jul 15 '15 at 7:39
  • Your Summary - think of it as a row - will need to hold an array of values, one for each column. – Keith C Jul 15 '15 at 8:17
  • Ok, may you give me a code snippet? Thanks. – peXeq Jul 15 '15 at 8:36
0

Try this:

  <table class="list" border="0" cellpadding="0" cellspacing="0">
  <tr class="headerRow">
      <td></td>
      <apex:repeat value="{!Summaries}" var="v">
      <td>{!v.Productname}</td>
      </apex:repeat>
  </tr>
  <apex:repeat value="{!Summaries}" var="summary">
  <tr>
  <td>{!summary.Ownername}</td>
  <apex:repeat value="{!Summaries}" var="val">
  <td>{!val.Value}</td>
  </apex:repeat>
  </tr>
  </apex:repeat>

In your controller avoid SOQL within the for loop. In this line FOR (RecordType RType : RTypeList) {

Store the record type id's in a list instead and use that in your query.

Also you can put the code for initializing Summaries and RTypeList within the constructor in your controller.

8
  • So I changed a bit the code. But the result is not realy good. I changed the code above also and I post a pic of the current output. May you help me a bit more. – peXeq Jul 16 '15 at 11:56
  • Please post a clear picture of the output. – Jaya Jul 16 '15 at 16:56
  • I have edited the code. Let me know if it works.. – Jaya Jul 16 '15 at 17:41
  • I changed the code as mentioned, but I this is not what I need (see pic above). I think the repeat function is ok, but I want in the first column just one time each account owner and in the next columns the values of each record type. At the moment return for each record type entry a row. – peXeq Jul 16 '15 at 19:04
  • I changed a bit the class and the page and get the result as posted above. I looks better, but the values assigned incorrectly and the row values repeats for all accountowner. Any ideas? – peXeq Jul 17 '15 at 6:22

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.