This post is about overcoming one of the limitations of Salesforce report builder: Filtering data based on dynamic date ranges e.g. last 200 days or next 3 weeks and not just the following available options:
# To appreciate the solution, I hereby request all my readers to try to solve/create the report (described in the use-case below) themselves before jumping to see the solution.
# Developers keep away: No code required here (pun intended)
Use case: Create a report to display sum of last 5 month sales for every user. The report should not hard code any date values in the filter.
Please refer to **Notes** above, if you haven’t already
Some theory (I promise only a little):
We will create a formula field on the opportunity object, called ‘close date age (in months)’. This field will tell whether the opportunity is 5 month old or not.
Now to the practical:
Step 1. Create a formula field of number type on Opportunity object like this:
Step 2: Create the report with the following filters:
1. Don’t forget to add the stage filter
2. For last 5 months, we assume it meant current month and previous 4 months
And that’s about it! Here is the same report I created:
- Like we created the field ‘close date age (in months)’, we can create field to represent days, years and even weeks and quarters. Of course, the formula will be different
- In reality, if I had to create this report on opportunity, I would not create the field named ‘close date age’. Instead, I would use the Opportunity history standard report type and effectively use the stage history information that Salesforce stores for us anyway. However, this post was for illustration of the mechanism that can be used on any object.
I hope you enjoyed this post.
Please leave any feedback in the comments section.