Reporting is one of the most powerful features of Salesforce. I want to teach you about Salesforce reporting considerations in a step by step process so it does not seem overwhelming, and so you can get the most out of the powerful tool that is Salesforce reporting.
While it can seem overwhelming, just like anything else, it is really a matter of getting used to the interface, and understanding how reporting works. When approaching any report in Salesforce, there are three concepts that should always be in the forefront of thought:
- What data needs to be reported on – If the data is not being catalogued, it cannot be reported. Also, if the data is not being captured in a logical manner, it will be very difficult to report on it. For example, fields that represent a number, percentage, or currency should be in their respective format. This will open up greater ability to report on and more importantly, aggregate, the data.
- Where does the data reside – What object (data table) houses the data. Is the data being reported on more than one object?
- How is the data connected – This is the most important of the three basic concepts of reporting, as this determines the report type. For example, a standard Opportunity report does not give access to activities, but Activities with Opportunities will give access to both data tables. There is a caveats to this; Contact and Opportunity based reports will always give full access to the corresponding Account record.
The first two concepts are relatively straight forward, but the third has a few more moving parts. Objects in Salesforce can be related through lookup relationships. There are two primary types of lookup relationships. The first is the standard lookup. This will relate two objects together, but the individual records can exist independent of each other.
The second is the master-detail relationship. This is similar to the native Account lookup field on the Opportunity. Aside from very specific, extenuating circumstances, an Opportunity record cannot be created without being linked to an Account. I.E. the individual Opportunity record cannot be created and saved unless it is placed in a direct relationship with the Account. This can happen automatically when converting a Lead or creating a new Opportunity from an Account or Contact record, or it can be done manually when creating the Opportunity. The relationship between the Opportunity and the Account is a master-detail relationship, although a native one. These relationships can be created as well. Reporting on the child in the master detail relationship will always give access to the master object.
Since these relationships are what tie the various Objects in Salesforce together, they should always be taken into consideration when approaching any report.
Even if the report is set as all time, it is still technically in a date range based on a single date field. For example, any Opportunity report will default to being based off of the Close Date. Any date field accessible on any of the Objects being reported on can be used. With the example of an Opportunity report, that would comprise any date field on the Opportunity or the Account. The primary date field selected should represent the base of the report such as “All Open Opportunities Closing This Month”.
In this example, the report is based off of the Close Date on the Opportunity. It is recommended that dynamic date ranges are used on any report to maintain a more sustainable report, or one that does not need to be continually updated and re-saved. This will also facilitate more sustainable dashboards. These ranges can be found in the “Range” picklist on the report customization screen, and are represented by all values except “Custom”.
These date ranges will update automatically each time the report is run.
Not all reports are based on a single date range. If the desired report is “All Opportunities Created and Closing this Month”, both the Create Date and the Close Date would need to have a dynamic value in order to make the report more sustainable. This is where relative date values and custom filters come in.
Any field on any object in the report can be filtered on. In the example given above, the custom filter would be regarding the Create Date. While any date field reference will give the user the option to select, or manually enter, a specific calendar day, these are generally static. Relative date values circumvent the seemingly static nature of a date filters, in order to create that more sustainable report mentioned above.
These relative date values can be utilized to dive deeper into the data. They can also be used in conjunction with the “Greater Than”, “Greater or Equal”, “Less Than”, “Less Than or Equal”, and “Equals” filter operators to provide reports that would seemingly not be possible otherwise, such as “Opportunities with a Close Date in the Past”. In this second example, the filters would appear as the following:
A complete list of relative date values can be found via the link below:
Tune in next time, for Part II, where I will cover approaches to report formats and field considerations.