Reporting in Salesforce is one of your most powerful features when you must prove value to the business. Key reports built in a matter of minutes with a simple drag-and-drop, easy-to-use interface is extremely effective. As an administrator, you must have a solid grasp of all the Salesforce reporting options available when reporting requirements open up.
Let’s have a look at some of the more advanced reporting options that Salesforce offers us beyond the drag-and-drop interface.
Custom report types
By default, Salesforce gives us standard report types. Standard report types are already present for all of our standard objects, such as “Contacts and Accounts” or “Opportunities with Products.” Salesforce also automatically created standard report types for every custom object and relationship we build. The type of report Salesforce created, such as with a “with” or “and” relationship, will depend on if you are using a Lookup or Master-Detail relationship.
But what if you need to report on more than two objects at a time? What if you don’t want to report on only “with” relationships? This is where custom report types come in.
Creating a custom report type involves selecting the relationships and the objects you would like to include. Depending on which primary object you choose (in this example, it is “Account”), the wizard will give you access to its child objects to also report on. You can also report on grandchild objects, up to a total of four objects. You will notice that we can also include whether the object selected “has” to have related records to show up in the report. This is useful if you would like to show a report such as Accounts with and without Opportunities.
Exception reports (cross filters)
Exception reports are used to show you where data does not exist and are built by using cross filters. Cross filters can be found in the normal filter drop-down and created off of any report type that has a child object related to it. Exception reports work by using cross filters to filter the child objects related to the primary object, such as “Accounts without Opportunities.” We can even take this further by filtering specific fields on the child object using sub-filters to create a report such as this:
From the picture above, you can see we are looking at Accounts without Opportunities that are closed equal false, showing us all accounts with open opportunities.
Custom summary formulas
Custom summary formulas are used exclusively on reports and can be positioned to calculate complex data from your summary levels. At first this seemed a bit daunting to me, but let’s look at an example that will help put this into context.
Custom summary formulas are added into a report like any other field, and once dragged into the report, we can begin creating the formula. You have access to most of the functions available when you are creating a normal formula field with a few extras to deal with specific situations in a report.
The formula I have created in the diagram uses the PARENTGROUPVAL report specific function, which allows us to grab a previous grouping in the report. I’m dividing this by the SUM of the Amount field, which gives us a percentage. When put into a report it looks like the example below, and from this simple formula we can calculate how much each account contributes to the total revenue for new customers:
This is just a small glimpse into what can be created with custom summary formulas; we can create averages, moving averages, totals of different groupings and more.
Analytical snapshots capture data at scheduled points in time, which then allow you to build historical reports on that data. This is especially useful if you need to see long-term trends in your data that is not possible with standard functionality. The normal reports in Salesforce allow you to see the data “as-is” in real time. But if, for example, you wanted to see how many cases are open on a single day and the trend that this creates, you would need to use analytical snapshots.
Analytical snapshots are extremely clever in the way they work and make full use of the Salesforce platform. They are created in three steps, shown in the following picture:
- First, you will need to create a source report. This report will contain the data that you want to historically trend on, so if we are sticking to our historic open cases example, we will need to create a source report that shows us all open cases open today.
- Next, we need to create a custom object. This custom object is used to hold the data from our source report, so whenever you set the snapshot to run, it will load all data into a new record in this object, which allows us to report on this object and its data. (You will need to create custom fields for each field in the report you are reporting on.)
- Finally, we can create the snapshot, which includes naming the snapshot, selecting the source report, selecting the target custom object and setting the frequency that this should run.
Historical trend reporting
Historical trending is a new feature that was first released in Winter ’14. It is a niche feature but can make your life easier if you need to track small daily or weekly data changes. Historical trend reports allow you to track up to eight fields on the Opportunity object and up to three custom objects. You can select up to five snapshots dates, such as five business days or five business weeks. This data can then be reported on and can be viewed in the same row to see the direct changes to the data.
Ben McCarthy is a certified Salesforce admin and developer. He writes the popular Salesforce Blog, Salesforce Ben. Ben has a wealth of experience in the Salesforce ecosystem as a Business Analyst, Head of CRM and Certified Sales Cloud consultant.