Visualforce page to display opportunities along with their products

Question

I am currently working on a Visualforce page to completely recreate and customize salesforce's 'customizable forecast' tab to make it work with our instance of CPQ as well as make a few business-specific changes. The current problem I am running into is as follows:

I need to display a filtered list of opportunities along with their respective opportunity products. How do I use dot notation (or other method) to display the products alongside their related opportunity?

I used SOQL in my custom controller to query the opportunities using the following query:

 BulkOpportunities =        [SELECT Id, Name, Account.Name, Owner.Name, StageName, Owner.Sales_Area__c, CloseDate, ForecastCategoryName, 
                                        (SELECT opportunity.ForecastCategoryName, Product_Family__c, Product2.Category__c, Quantity, UnitPrice, TotalPrice, Annual_Revenue__c 
                                            FROM OpportunityLineItems)
                                        FROM Opportunity    
                                        WHERE Owner.Id IN :myTeamMembers
                                        AND Owner.Sales_Area__c LIKE :selectedSalesArea 
                                        AND (Id IN 
                                                (SELECT OpportunityId FROM OpportunityLineItem WHERE Quantity > 0))
                                        AND (CloseDate >= :rangeStartDate AND CloseDate <= :rangeStartDate.addMonths(integer.valueOf(selectedRangeLength)))

                                        ORDER BY CloseDate, ForecastCategoryName];

This successfully returns the opportunities I need to display. My Visualforce snippet is below:

<apex:pageBlockTable title="Opportunities" value="{!BulkOpportunities}" var="o">
            <apex:column headerValue="Opportunity Name" value="{!o.Name}"/>
            <apex:column headerValue="Account" value="{!o.Account.Name}"/>
            <apex:column headerValue="Owner" value="{!o.Owner.Name}"/>
            <apex:column headerValue="Stage" value="{!o.StageName}"/>
            <apex:column headerValue="Close Date" value="{!o.CloseDate}"/> 
            <!-- This line fails --><apex:column headerValue="Product" value="{!o.product2.name}"/> 
            <apex:column headerValue="Forecast Category" value="{!o.ForecastCategoryName}"/> 
        </apex:pageBlockTable>

How can I display each opportunities related products in line with the opportunities? There can be multiple products per opportunity. I have tried to replace value="{!o.product2.name} with value="{!o.Product2}, value="{!o.OpportunityLineItem.name} etc with no luck.

I also tried the following, per Salesforce Development Forum

<apex:pageBlockTable title="Opportunities" value="{!BulkOpportunities}" var="o">
        <apex:column headerValue="Opportunity Name" value="{!o.Name}"/>
        <apex:column headerValue="Account" value="{!o.Account.Name}"/>
        <apex:column headerValue="Owner" value="{!o.Owner.Name}"/>
        <apex:column headerValue="Stage" value="{!o.StageName}"/>
        <apex:column headerValue="Close Date" value="{!o.CloseDate}"/> 
        <apex:column headerValue="Products"/>
            <apex:outputText value="{!o.OpportunityLineItems}"/>  
        <apex:column headerValue="Forecast Category" value="{!o.ForecastCategoryName}"/> 
    </apex:pageBlockTable>

This code saved, but the field in the displayed table is blank where I know products are linked.

I am really at a loss for how to make this work. Any ideas would be greatly appreciated!

Answer 1

This line of code is outside of an apex:column, and so will not be rendered.

        <apex:outputText value="{!o.OpportunityLineItems}"/>

As an aside, this probably won't output what you expect, because it's a list. Instead, you'll want to render another table inside the column:

    <apex:column headerValue="Products">
        <apex:dataTable value="{!o.OpportunityLineItems}" var="lineitem">
            <apex:column value="{!lineitem.name}" />
            ...
        </apex:dataTable>
    </apex:column>

Answer 2

Try this controller:

public class OppDev {
public List<Opportunity> oppList;

public OppDev(){
    oppList=[SELECT ID, NAME, Account.Name,
             (SELECT Quantity, UnitPrice, PriceBookEntry.Product2.Name FROM OpportunityLineItems)
             FROM OPPORTUNITY WHERE ID=: ApexPages.currentPage().getParameters().get('opptyId')];

}

public List<Opportunity> getOpptyList(){
    return oppList;
}}

You cannot obtain the product name directly from the opportunity line item, you need to get it from the pricebookentry this opportunity uses.