Reporting in Salesforce is one of the most powerful features you have when trying to demonstrate value to the business. Building key reports in a matter of minutes with a simple drag and drop interface that anyone can use is extremely effective. Being such a vital part of any business, It is essential that as an Administrator you have a good idea of all the options available to you when reporting requirements open up.
Let’s have a look at some of the more advanced reporting options that Salesforce offer us beyond the drag and drop interface.
1. Custom Report Types
By default, Salesforce gives us standard report types. Standard report types are already present for all of our standard objects e.g. “Contacts & 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 e.g. 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 2 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 4 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 & without Opportunities.
2. 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 can be 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. For examples Accounts without Opportunities. We can even take this further by filtering on specific fields on the child object using Sub-filters to create a report such as this..
From the 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 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 lets look at an example that will help put this into context.
Custom Summary Formulas are added into a report like any other field, once dragged into the report, we can begin creating the formula. You have access to most of the functions available to 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 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!
4. Analytical Snapshots
Analytical snapshots capture data at scheduled points in time, which then allow you to build historical reports. This is especially useful if you need to see long term trends in your data that is just not possible with standard functionality. The normal reports in Salesforce allow you to see the data “as is” in realtime. But if for example you wanted to see how many cases are open on a single day and the trend that this creates, we will 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 3 Steps..
- Firstly 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.
- Secondly we need to create a custom object (this is where it gets clever). This custom object is used to hold the data from our source report so that means whenever you set the snapshot to run, it will load all data into a new record in this object, this then 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).
- Lastly we can create the Snapshot! This will include naming the snapshot, selecting the source report, selecting the target custom object and settings the frequency that this should run.
5. Historical Trend Reporting
Historical Trending is a very new feature that was first released in Winter ’14. It is a niche feature but can really make your life easier if you need to track small changes day to day or week to week in your data. Historical trends allow you to track up to 8 fields on the Opportunity object and up to 3 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. Find out more about Historical Trend Reporting.