Pages

Sunday, August 28, 2016

Salesforce: Report Type Object Relationships


When you relate Object in Custom Report Type, you have option of:
  1. with or without related records (may or may not have related records)
  2. with at least one related records (must have at least one record)
The 1st option require at least one child record, otherwise the parent record will not show in the report generated, while the 2nd option do not require any child record. You can easily change the relation from 1st to 2nd, or 2nd to 1st anytime.

Let's look at the metadata for the different, it is just in a <outerJoin>

1. with or without related records
 <?xml version="1.0" encoding="UTF-8"?>  
 <ReportType xmlns="http://soap.sforce.com/2006/04/metadata">  
   <baseObject>Account</baseObject>  
   <category>accounts</category>  
   <deployed>false</deployed>  
   <description>Account with Contacts only</description>  
   <join>  
     <outerJoin>false</outerJoin>  
     <relationship>Contacts</relationship>  
   </join>  
   <label>Account w/ Contacts</label>  
   <sections>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>Id</field>  
       <table>Account</table>  
     </columns>  
     <columns>  
       <checkedByDefault>true</checkedByDefault>  
       <field>Name</field>  
       <table>Account</table>  
     </columns>  
     <masterLabel>Accounts</masterLabel>  
   </sections>  
   <sections>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>FirstName</field>  
       <table>Account.Contacts</table>  
     </columns>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>LastName</field>  
       <table>Account.Contacts</table>  
     </columns>  
     <masterLabel>Contacts</masterLabel>  
   </sections>  
 </ReportType>  

2. with at least one related records
 <?xml version="1.0" encoding="UTF-8"?>  
 <ReportType xmlns="http://soap.sforce.com/2006/04/metadata">  
   <baseObject>Account</baseObject>  
   <category>accounts</category>  
   <deployed>false</deployed>  
   <description>Account with Contacts only</description>  
   <join>  
     <outerJoin>true</outerJoin>  
     <relationship>Contacts</relationship>  
   </join>  
   <label>Account w/ Contacts</label>  
   <sections>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>Id</field>  
       <table>Account</table>  
     </columns>  
     <columns>  
       <checkedByDefault>true</checkedByDefault>  
       <field>Name</field>  
       <table>Account</table>  
     </columns>  
     <masterLabel>Accounts</masterLabel>  
   </sections>  
   <sections>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>FirstName</field>  
       <table>Account.Contacts</table>  
     </columns>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>LastName</field>  
       <table>Account.Contacts</table>  
     </columns>  
     <masterLabel>Contacts</masterLabel>  
   </sections>  
 </ReportType>  

Without Object Relationship
Now, let's take a look on simpler report type metadata which contain no object relationship - only Account for this sample, there is no <join> tag and no second <section> tag for related objects.
 <?xml version="1.0" encoding="UTF-8"?>  
 <ReportType xmlns="http://soap.sforce.com/2006/04/metadata">  
   <baseObject>Account</baseObject>  
   <category>accounts</category>  
   <deployed>false</deployed>  
   <description>Account Only</description>  
   <label>Account Only</label>  
   <sections>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>Id</field>  
       <table>Account</table>  
     </columns>  
     <columns>  
       <checkedByDefault>true</checkedByDefault>  
       <field>Name</field>  
       <table>Account</table>  
     </columns>  
     <masterLabel>Accounts</masterLabel>  
   </sections>  
 </ReportType>  

Second Object Relationship
When the first object relationship is "must have at least one related", you have both the same option for second object relationship.


But, when the first object relationship is "may or may not have related record", you only have option "may or may not have related record" too for second object relationship.




Let's take a look on the metadata, we have additional <join> tag within <join> tag, and additional <section> tag.
 <?xml version="1.0" encoding="UTF-8"?>  
 <ReportType xmlns="http://soap.sforce.com/2006/04/metadata">  
   <baseObject>Account</baseObject>  
   <category>accounts</category>  
   <deployed>false</deployed>  
   <description>Account with Contacts with &amp; without Campaign History</description>  
   <join>  
     <join>  
       <outerJoin>true</outerJoin>  
       <relationship>CampaignMembers</relationship>  
     </join>  
     <outerJoin>false</outerJoin>  
     <relationship>Contacts</relationship>  
   </join>  
   <label>Account w/ Contacts w/ &amp; wo/ Campaign History</label>  
   <sections>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>Id</field>  
       <table>Account</table>  
     </columns>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>Name</field>  
       <table>Account</table>  
     </columns>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>Owner.Department</field>  
       <table>Account</table>  
     </columns>  
     <masterLabel>Accounts</masterLabel>  
   </sections>  
   <sections>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>FirstName</field>  
       <table>Account.Contacts</table>  
     </columns>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>LastName</field>  
       <table>Account.Contacts</table>  
     </columns>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>Account</field>  
       <table>Account.Contacts</table>  
     </columns>  
     <masterLabel>Contacts</masterLabel>  
   </sections>  
   <sections>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>Name</field>  
       <table>Account.Contacts.CampaignMembers</table>  
     </columns>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>CampaignMemberRecordType</field>  
       <table>Account.Contacts.CampaignMembers</table>  
     </columns>  
     <columns>  
       <checkedByDefault>false</checkedByDefault>  
       <field>Status</field>  
       <table>Account.Contacts.CampaignMembers</table>  
     </columns>  
     <masterLabel>Campaign History</masterLabel>  
   </sections>  
 </ReportType>