I have created a custom report for pulling quite a bit of data from Salesforce. I have a special circumstance that requires a VF component and a 2nd Class to get some data due to the way the Objects are related using a Joint Object, Many to Many relationship. When I go and run the report it gives me a SOQL query limit error. Without the 2nd class it works just fine. I used the Dev Console and found that only 2 SOQL queries run when it is just the primary Class but 101 run when I add in the 2nd class. I am not sure what is causing this. Any help would be appreciated. Thanks so much. I have added all the data below hope it is not too much. Thanks!
Main VF Page:
<apex:page standardController="Case__c" extensions="MonthlySeizureController">
<style type="text/css">
table.myTable{
border-collapse: collapse;
}
table.myTable td{
border: 1px solid black;
padding: 5px;
}
table.myTable th{
border: 2px solid black;
padding: 5px;
}
a.linkAsBtn,
a.linkAsBtn:hover {
text-decoration: none;
padding: 3px 6px;
}
</style>
<apex:form >
<apex:panelGrid columns="8" id="dates1">
<b>Start Date: </b><apex:inputfield value="{!taskRec.ActivityDate__c}" required="true"/>
<b>End Date: </b><apex:inputfield value="{!taskRec1.ActivityDate__c}" required="true"/>
<apex:commandLink value="Submit" rerender="test" styleClass="btn linkAsBtn"/>
<apex:commandLink value="Printable View" action="{!next}" id="cmdNext" target="_blank" styleClass="btn linkAsBtn"/>
<apex:commandLink value="Save as PDF" action="{!next1}" id="cmdNext1" target="_blank" styleClass="btn linkAsBtn"/>
</apex:panelGrid>
<apex:outputPanel id="test">
<table class="myTable">
<tr>
<th>BPR Case</th>
<th>BP MGR</th>
</tr>
<apex:repeat value="{!ca}" var="c" id="case">
<tr>
<td>
<apex:outputText value="{0,date,MM'-'dd'-'yyyy}"><apex:param value="{!c.Notification_Date__c}"/></apex:outputText>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
<apex:outputText value="{!bus.Business__r.Business_Name__c}" rendered="{!bus.Business__r.Business_Type__c = 'Trading Company'}"></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
<apex:outputText value="{!bus.Business__r.Business_Name__c}" rendered="{!bus.Business__r.Business_Type__c = 'Exporter'}"></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.Subject_for_Cases__r}" var="sub">
<apex:outputText value="{!sub.Subject_Name__r.Subject_Name__c}" rendered="{!sub.Subject_Name__r.Subject_Type__c = 'Exporter'}"></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
<c:Business_Shipper_Contact_Info BusinessID="{!bus.Business__r.Id}" rendered="{!bus.Business__r.Business_Type__c = 'Exporter'}"/><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.Products__r}" var="pro">
<apex:outputText value="{!pro.Brand__c}"></apex:outputText> <apex:outputText value="{!pro.Product_Types__c}"></apex:outputText> (<apex:outputText value="{0,number,###,###}"><apex:param value="{!pro.Total_Product_Quantity__c}"/></apex:outputText>)<br></br>
</apex:repeat>
</td>
<td>
<apex:outputText value="{!c.Total_Product__c}"></apex:outputText>
</td>
<td>
<apex:repeat value="{!c.Incident_Locations__r}" var="loc">
<apex:outputText value="{!loc.Container_Type__c}"></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:outputText value="{!c.Comments_Postal_Express__c}"></apex:outputText>
</td>
</tr>
</apex:repeat>
</table>
</apex:outputPanel>
</apex:form>
</apex:page>
Main Class:
public class MonthlySeizureController{
public task taskRec{get; set;}
public task taskRec1{get; set;}
public MonthlySeizureController(ApexPages.StandardController sc){
taskRec= new task (ActivityDate__c = Date.today().addMonths(-1));
taskRec1= new task (ActivityDate__c = Date.today());
}
public Case__c[] ca {
get {
Case__c[] cases =
[SELECT Id, Name, CreatedDate, LastModifiedDate, Owner.FirstName, Owner.LastName, Notification_Date__c, Incident_City__c, Incident_Country__r.Name,
Total_Product__c, Comments_Postal_Express__c,
(SELECT RecordType.Name, Container__c, Ship_Date__c, Date_of_Retention__c, Carrier__c, Port_of_Loading__c, Port_of_Discharge__c, Container_Type__c,
Place_of_Retention__c,
Vessel__c, Master_BOL__c, House_BOL__c FROM Incident_Locations__r),
(SELECT Brand__c, Product_Detail__c, Product_Types__c, Total_Product_Quantity__c FROM Products__r),
(SELECT Business__r.Business_Name__c, Business__r.Business_Type__c FROM BusinessCasesAssociations__r),
(SELECT Subject_Name__r.Subject_Name__c, Subject_Name__r.Subject_Type__c FROM Subject_for_Cases__r)
FROM Case__c
WHERE Supply_Chain_Type__c = 'SEA'
AND Notification_Date__c >= :taskRec.ActivityDate__c
AND Notification_Date__c <= :taskRec1.ActivityDate__c
ORDER BY Notification_Date__c ASC];
return cases;
}
}
public PageReference next(){
PageReference nextpage = new PageReference('/apex/securityreport1ppv');
return nextpage;
}
public PageReference next1(){
PageReference nextpage = new PageReference('/apex/securityreport1sap');
return nextpage;
}
}
VF Component:
<apex:component controller="BusinessContactController" access="global">
<apex:attribute id="recordID" assignTo="{!recordID}" name="BusinessID" type="Id" description="The Business's SF Id"/>
<apex:repeat value="{!bus1}" var="addr1">
<apex:outputText value="{!addr1.Street__c}"></apex:outputText>
<apex:outputText value="{!addr1.Business_City__c}"></apex:outputText>
<apex:outputText value="{!addr1.State_Province__c}"></apex:outputText>
<apex:outputText value="{!addr1.Country__c}"></apex:outputText>
<apex:outputText value="{!addr1.Zip_Postal_Code__c}"></apex:outputText>
</apex:repeat>
</apex:component>
2nd Class:
public with sharing class BusinessContactController {
public BPR_Business_Addresses__c[] bus1 {
get {
BPR_Business_Addresses__c[] AddressList =
[SELECT Street__c, Business_City__c, State_Province__c, Country__c, Zip_Postal_Code__c, Business__r.Id
FROM BPR_Business_Addresses__c
WHERE (Business__r.Id = :recordId)];
return AddressList ;
}
set;
}
Public string recordId {get;set;}
}
