Hi everyone,
I’m working on a requirement where I need to display Year-to-Date (YTD) values in a ServiceNow report, based on fiscal months.
Here’s the scenario:
Whenever a new record is created in my custom table, an RPN value gets calculated. Using that RPN, I need to calculate YTD using this formula:
YTD = (Sum of RPN for the current fiscal year: April–March) / (Fiscal Month Count)
(Example: April = 1, May = 2, June = 3, etc.)
The tricky part:
Whenever a new record is added and the RPN total increases, the YTD value should automatically update, and my bar chart should always show the latest YTD for the current month.
What I tried earlier
I used an after insert Business Rule that calculated the YTD, stored it on the record, and set a “latest month” flag. Then in the report, I filtered by that flag and displayed SUM(YTD).
This worked initially… until I had to apply additional filters in the report (like unit, status: open/closed, etc.). Since the YTD was stored only on one flagged record per month, the filtering completely broke the logic.
What I need help with
How do I design this so the YTD always reflects the right value, even when users apply extra filters in the report?
Should this calculation be done dynamically in a report source? Scripted data source? A scheduled job? Or is there a better approach?
Any suggestions or best practices would really help. Thanks!
