The Scenario
Have you or your customers ever had a need to filter a dashboard according to a custom date, such as a fiscal period?
What is a fiscal period? It is an arbitrary period of time in the sense that it does not align to the Gregorian calendar. For instance, some businesses may consider December 21st thru January 18th to be their "fiscal January".
My Usual Solutions
Up to now, these have been the solutions that I have had available:
1. Hardcode the desired period into a report and update it every time we roll over into the next period. (Yuck!)
2. Create a custom date script to present a rolling desired period in a given report(s).
3. Provide a standard date interactive filter that lets them select their periods by modifying the OOTB "Get Date Filter Options for Date Filters" business rule. (See KB0749822)
4. Create a custom filter that lets them select their periods but has some issues with defaults/user-preference values.
I don't know about you, but I'm not satisfied with any of those four options; they are either too rigid, too invasive, or lacking standard features. Thankfully, I've discovered a new solution recently that I'd like to share.
A New Solution: Ad-Hoc Custom Options
Here's a potentially fresh thought: though the OOTB BR controls the form-view's select boxes' available options for these fields, WE can control these field's options via list-editing these fields. Isn't that intriguing? Here's a visual to get us all on the same page.
What am I getting at? Well, from list view we can list-edit the Date Filter and Default value fields to add our own custom options and thereby introduce ad-hoc options without modifying any OOTB foundations. Meaning we can do this in a very minimally invasive way!
To craft your own custom option, follow this format: (join options with commas, no new lines)
[n]_[Alias]@javascript:[function of your choice](arg1of1)@javascript:[function of your choice](arg1of1)
Here is a string of sample fiscal date options: (The use of 4/3:59:59 rather than 0/23:59:59 is due to our UTC offset; see the related note below on the gs.dateGenerate issue.)
010_FY 2020 (10/01-09/30)@javascript:new GlideDateTime('2019-10-01 04:00:00')@javascript:new GlideDateTime('2020-10-01 03:59:59'),020_FY 2021 (10/01-09/30)@javascript:new GlideDateTime('2020-10-01 04:00:00')@javascript:new GlideDateTime('2021-10-01 03:59:59'),030_FY 2022 (10/01-09/30)@javascript:new GlideDateTime('2021-10-01 04:00:00')@javascript:new GlideDateTime('2022-10-01 03:59:59')
NOTE: Please note that this field is incompatible with multi-argument date functions. There is downstream logic that apparently does a String.split(',') on this field's value which will mutilate into fragmented options any options that have function calls utilizing a comma to separate their arguments. Therefore, I suggest the following workaround for gs.dateGenerate's required 2nd argument: (See above sample, too, as it is utilizing this format.)
[n]_[Alias]@javascript:new GlideDateTime(desiredStartPlusUtcOffset)@javascript:new GlideDateTime(desiredEndPlusUtcOffset)
You can use this information to build your own new date filters with ad-hoc lists of custom options. Just be careful to be very precise in your syntax, as this will be unforgiving of typos! I suggest building your strings methodically such as via utilizing a helper Excel spreadsheet.
Here are the pros and cons of this method:
Pros
1. Dashboards can now freely be filtered by custom date options.
2. Very user-friendly, with no customer impacts resulting from its cons.
3. Supports default values/user preferences. (unlike custom filters)
4. Facilitates delegation by being configurable by report admins rather than being in an admin-only BR edit.
5. Minimally invasive; does not modify any OOTB structures such as BRs!
6. Allows large amounts of custom options without cluttering the OOTB UI with multitudes of one-off options.
7. Once a custom list of options has been built - which is the hard part - it is easily re-used via copy-and-paste.
Cons
1. Higher technical requirement to configure.
2. Must be built manually and are easily affected by typos.
3. Experimental and therefore subject to change.
4. Can only be moderated from list view. (Our custom options will fail to render correctly in form view.)
Conclusion
Via this method report admins can build ad-hoc date options without modifying any OOTB BRs, making it much easier to filter dashboards by fiscal periods. Report admins may now be able to fulfill many previously-impossible fiscal period-related filter designs that customers have requested. I hope this helps you and your colleagues to help your customers!
Thanks for reading, have a great day!
P.S. I apologize if this was already common knowledge. I had not seen it mentioned before and so it seemed like a breakthrough to me when I realized it is possible.
Kind Regards,
Joseph
