Dynamic summary formula

Question

I want to create a summary formula on a report that changes it's value based on the entered date from the filters.

Imagine this, a formula that multiplies a value with the number of days, like: Value * X days. X days value is returned from the date filter interval.

I know that this probably is not supported but, anyone has some idea on how to get this to work dynamically.

Answer 1

To show a report like this, you would need to have multiple date-based object records in your database to summarize up.

For example, you want to report on Revenue on a monthly basis, and want your report to show how much revenue you made over a span of 6 months.

• Create a custom object called Revenue Entry that has a date and a currency field on it, and looks up to Opportunity.
• Each Opportunity will have multiple Revenue Entries: one for each month it is generating revenue.
• The report in this case is over Revenue Entries, and is grouped at the Opportunity level. The date filter is the range you're looking for, and there will be 6 Revenue Entries per Opportunity if you Show Details, since there are 6 months in the date filters.

In this situation, you can use standard Salesforce reports to show the information you require, but you will probably want some kind of automation to create all those individual date-related records. Creation of these detail-type records could be accomplished with a trigger or scheduled batch process. If you need to report down to the day, you will need an individual record for every day. Obviously, this could cause an explosion in data storage, and may not be practical for your use case.

Answer 2

You correctly identify that this not possible with standard report. You can Write a VF page that can give you this result. you can create wrapper class that process and find value using your formula and entered filter.