Ever tried to create a Custom Report in Salesforce, only to realize that you couldn’t quite get the data elements you wanted together on the same report? Ever noticed that the out of the box Salesforce report types, such as Accounts with Opportunities, will only show Account records when a corresponding Opportunity record is available? Ever wanted to change the label of a field, just for reporting purposes? Custom Report Types, available in Professional, Enterprise, Unlimited, and Developer Editions can help us solve each of these reporting challenges in Salesforce.com.
Custom Report Types are created by a System Administrator, or by a User whose Profile includes the “Manage Custom Report Types” permission. They allow us to define a foundation of objects and fields, which can be leveraged by your system’s Users when creating Reports. Custom Report Types are our primary means of creating “outer-joins” in reports within Salesforce.com- an outer-join allows us to report on records with or without related records, such as accounts with or without contacts. This can be very useful when trying to assess the penetration within target accounts, for example, to show all Target Accounts and their Contacts, whether or not there are any Contacts.
Five very useful Custom Report Types you can create in your Salesforce.com system include:
- Accounts with or without Contacts - Use this to create Reports on Accounts and their Contacts, perfect for learning which sales territories have names to market to.
- Accounts with or without Opportunities - Use this to create Reports on Accounts and their Opportunities, perfect for understanding target account or territory penetration for Sales Reps.
- Accounts with or without Cases - Use this to create Reports on Accounts and their Cases, whether or not they have any Cases.
- Opportunities with or without Contacts – Use this to create Reports on Opps and the Contacts that have been assigned to them under Contact Roles.
- Users with or without Activities - Use this to create Reports on Users and the Activities that they have or have not created.
Within the OpFocus Salesforce.com instance, we have many other Custom Report Types defined, supporting reporting relationships among both Standard and Custom Objects.
To create the Custom Report Type “Accounts with or without Contacts” listed above:
- Navigate under Setup to Create/Report Types.
- Check to see if the Report Type you are intending to create is already there… no sense creating duplicates.
- Click the Create New Custom Report Type button
- Select the Primary Object – in this case “Accounts”. This is an important step- a Record from this Object will always be present in the data in the report. (Put in database jargon, Custom Report Types support INNER and LEFT OUTER JOINS, but not RIGHT OUTER JOINS).
- Enter a Report Type Label – in this case “Accounts with or without Contacts”. Note that this can be up to 255 characters long. The Report Type Name will auto populate once you click out of the Report Type Label field.
- Provide a Description for your Custom Report Type. These can be up to 1000 characters long, and might include examples of Reports that this Report Type might support, or just general information on the Objects supported by the Report Type.
- Store in a Category- in this case “Accounts and Contacts”. As of now we cannot create custom Report Categories. Two Ideas to VOTE for on the Idea Exchange include: Create Custom Report Categories and Override Standard Report Types.
- Set the Deployment Status to Deployed. If left as “In Development”, only other System Administrators and Users with the “Manage Custom Report Types” Profile permission can see and use this when creating Custom Reports.
Now you define the Object Relationships for this Custom Report Type:
- Look for and click on the box with the dotted border which says (Click to relate to another object). In this example, select the Contact Object from the list.
- Now specify that “A” records may or may not have related “B” records. This supports the outer join, where, in this example, we’ll be able to Report on all Accounts and their Contacts, whether or not they have any Contacts.
- Skip relating this to another Object and click Save.
Controlling Fields Available on Reports
A secondary benefit of Custom Report Types is that we can control which fields are available to the Report writer, which ones are displayed by default, and what labels are displayed for each field. One caveat to this is that going forward any Custom Fields added to Objects in Custom Report Types must be added to those Custom Report Types. Put another way, new fields you add to your system will NOT automatically be available when creating Custom Reports on the Objects in your Custom Report Types. Once you’ve created your Custom Report Type, you can edit the Fields Available for Reports by clicking on the Edit Layout button and dragging fields on or off the layout, double clicking them to adjust the default setting and field label for Reports.
Gotchas with Custom Report Types
- Your Custom Report Types are added to the bottom of whatever Report Category you add them to. You might need to alert relevant Users to their availability so that they can find and use them in their Reports.
- Unlike standard report types, with Custom Report Types any new fields you add to your object aren’t automatically visible in the Custom Report Type. You’ll need to add them manually.
- When creating Custom Report Types, it is important to label them clearly, and write a good description of what each can be used for.
- Be sure that your Custom Report Type is DEPLOYED – otherwise, only other System Administrators or Users whose Profile includes the “Manage Custom Report Types” permission can see or use the Custom Report Type.
- Rather than editing an existing Custom Report Type (which may wreak havoc with Reports created with it), Salesforce recommends that you Clone any Custom Report Type you wish to edit the data structure of- and edit the clone instead.
Another blog article you may be interested in focuses on some examples of things within Salesforce that can ONLY be reported on via Custom Report Types.