Custom interactive filter date range by fiscal period, using script include

[Badamski]

My requirement is to create a dynamic interactive filter that allows users to to filter reports and dashboards by fiscal quarter, months or years from the fiscal_period table.

I have a script include which builds an object with the relevant information from the fiscal period table but I'm struggling with how this connects to an interactive filter. I thought there would be a way to populate my filter criteria for an interactive filter by calling the script include (similar to how one could call a script include in a reference qualifer on a variable) but I don't see any way to do this—despite finding another post that recommended choosing the "advanced" type on an interactive filter record which would allow use of a script include to populate the filter criteria. (our instance is Vancouver patch4)

I found this community post https://www.servicenow.com/community/performance-analytics-blog/intro-to-custom-interactive-filters-... which seems to indicate the script include logic should be utilized in a dynamic content block or a ui page or ui macro. I'm happy to dive back into some jelly to get that working if it needs to be done but an interactive filter date range using fiscal periods feels like a common enough ask that there must be a less complicated solution...maybe?

Updates:

I removed the script from the post since it may not even be relevant at least in that state for a viable solution.

I'm still unclear on how to call a script include in an interactive filter, I've seen references to clicking the advanced checkbox or selecting the scripted type on the interactive filter record but haven't seen that in my instance or PDI.

Some more digging led me two other possibilities I'm reviewing:

1. Creating a custom interactive filter widget and macro per https://www.servicenow.com/community/platform-analytics-forum/how-to-create-a-date-range-interactive... I have this working in a pdi and it definitely has potential, but it seems like it will still be quite a lift to leverage the fiscal periods as date ranges—just manually inputting them using the between range options works, but doesn't feel like a complete solution at all.

2. This post caught my attention https://www.servicenow.com/community/developer-forum/how-to-add-a-literal-static-date-value-in-a-dat... and I like the idea of essentially adding the fiscal period ranges to this business rule that populates the options in the date range filter. If this can be done, it checks more boxes for me. The stakeholders want this fiscal date range option basically available in all reports and dashboards that use the ootb date range filter, so if we could include the fiscal period ranges there, that would be great. It raises architectural questions about whether this should be hardcoded in the business rule, in a new custom business rule and brought into a new interactive filter that basically replicates the ootb date range filter but for fiscal date ranges (if that's possible. How the business rule is even explicitly called by the date range filter is unclear to me) or if there's a way to populate them dynamically from the fiscal period table in a way that is a bit cleaner and scalable.

This feels like something that if not entirely out of the box, should be included as an interactive fiscal date filter that comes with the fiscal calendar plugin but I'm really surprised at how little community posts and documentation I'm finding on what I would expect to be a pretty common business case. 

My biggest questions are:

• How is a script include called by an interactive filter? can it be?
• How could I modify the "Get Date Filter Options for Date Filters" business rule to include fiscal period date ranges in the ootb Date Range filter? OR How could I replicate it's functionality to be utilized in a new Fiscal Date Range filter?
• Is creating a custom widget or macro really the best way to let users leverage the existing fiscal period table data for filtering data? Say it isn't so.

[Tsura Andreeva]

Would it help if you can add your fiscal year to the Date ootb interactive filter by updating the Business Rules?

https://docs.servicenow.com/bundle/tokyo-now-intelligence/page/use/dashboards/reference/r_CustomPubl...

[Badamski]

Yes! That's exactly what I'm trying to do now but I can't seem to get a date range query that works when it applies to a filter. 

    // answer.add('002_FY24@01/01/2024 00:00:00@01/31/24 23.59:59', gs.getMessage('FY2024'));
    // renders no results
    answer.add('004_FY2024@^01/01/2024@12/31/2024', gs.getMessage('FY2024'));
    /// renders all results
   answer.add('002_FY24M04@01-01-2024@01-31-2024', gs.getMessage('FY2024M04'));
    /// renders all results

These last two seem like the right syntax for all of 2024 or January 2024 but both result in the same results, showing incidents (I'm using the incident table to test this) from December 2023 to February 2024 (all of the results in my Dev instance).

[Tsura Andreeva]

I am not very good with java script, but I did take your code and it does not seem to work, one idea I can give, take a look at the fiscal calendar - after you install the plugin it you will be able to use the conditions directly into the reports https://www.youtube.com/watch?v=tYsXf3dNdSI

[Badamski]

Unfortunately I've already gone through that video and while we do currently have that behavior on list views it isn't customizable so that we can allow users to select options like specific fiscal periods and the ask is to provide that filtering in reports and dashboards through an interactive filter. There's even a comment on that video asking about how to apply that to an interactive filter and the creator responded that they spent a day trying to figure it out before giving up 😅

It's so hard to imagine this hasn't been solved by someone or that ServiceNow has no ootb option for applying fiscal periods to interactive filters but the only even remotely viable proof of concept I've had is creating a custom widget and that's way too much for such a simple requirement.