Was frustrated with the lack of solutions to all the posts about a Fiscal Period interactive filter on dashboards, so decided to dig in, solve it and post the solution for others to use.
The closest thing to a solution previously posted was the reference to KB0749822 (How to create a custom date filter for use in an Interactive Filter) [If you don't have access, details of that article are posted in this comment on a thread about this topic]. The problem with that KB is that it only provides a solution for Fiscal Years, and the solution uses some undocumented function with an unknown SysID in it, with no explanation about what it's referencing.
Nevertheless, that KB was still useful and my solution uses the same construct: Update the 'Get Date Filter Options for Date Filters' business rule with new filter options, and pair it with a Script Include for the filtering function.
Prerequisites:
1. Install the Fiscal Calendar plugin (com.snc.fiscal_calendar)
2. Configure/Generate your Fiscal Calendar
3. Pair the business calendar with packages to enable fiscal calendar date filters for your application
(in my case I paired it with Time Card Management and Expense Lines, since that's what I need interactive filters for)
The above three prerequisites are well covered in this YouTube video, which was referenced by a lot of other posts on this topic.
Now onto solving for fiscal period filters in interactive filters. My solution provides the following filter options:
- Last Fiscal Month
- This Fiscal Month
- Last Fiscal Quarter
- This Fiscal Quarter
- Last Fiscal Year
- This Fiscal Year
Adding other options (e.g. last 3 fiscal months) should be easy enough for you to figure out from the code structure.
Solution Steps:
1. Script Include for Fiscal Period Filters
Create a new Script Include, called df_GetFiscalPeriod, with the 'client-callable' option checked. (If you want your own name, don't forget to change it in the code and business rule.)
Insert the following code into it (explained later on):
var df_GetFiscalPeriod = Class.create();
df_GetFiscalPeriod.prototype = Object.extendsObject(AbstractAjaxProcessor, {
getStartofLastFiscalMonth: function (){
return sn_bc.GlideBusinessCalendarUtil.getCalendarStart("Fiscal Month", "-1", "Last Fiscal Month")
},
getEndofLastFiscalMonth: function (){
return sn_bc.GlideBusinessCalendarUtil.getCalendarEnd("Fiscal Month", "-1", "Last Fiscal Month")
},
getStartofThisFiscalMonth: function (){
return sn_bc.GlideBusinessCalendarUtil.getCalendarStart("Fiscal Month", "0", "This Fiscal Month")
},
getEndofThisFiscalMonth: function (){
return sn_bc.GlideBusinessCalendarUtil.getCalendarEnd("Fiscal Month", "0", "This Fiscal Month")
},
getStartofLastFiscalQuarter: function (){
return sn_bc.GlideBusinessCalendarUtil.getCalendarStart("Fiscal Quarter", "-1", "Last Fiscal Quarter")
},
getEndofLastFiscalQuarter: function (){
return sn_bc.GlideBusinessCalendarUtil.getCalendarEnd("Fiscal Quarter", "-1", "Last Fiscal Quarter")
},
getStartofThisFiscalQuarter: function (){
return sn_bc.GlideBusinessCalendarUtil.getCalendarStart("Fiscal Quarter", "0", "This Fiscal Quarter")
},
getEndofThisFiscalQuarter: function (){
return sn_bc.GlideBusinessCalendarUtil.getCalendarEnd("Fiscal Quarter", "0", "This Fiscal Quarter")
},
getStartofLastFiscalYear: function (){
return sn_bc.GlideBusinessCalendarUtil.getCalendarStart("Fiscal Year", "-1", "Last Fiscal Year")
},
getEndofLastFiscalYear: function (){
return sn_bc.GlideBusinessCalendarUtil.getCalendarEnd("Fiscal Year", "-1", "Last Fiscal Year")
},
getStartofThisFiscalYear: function (){
return sn_bc.GlideBusinessCalendarUtil.getCalendarStart("Fiscal Year", "0", "This Fiscal Year")
},
getEndofThisFiscalYear: function (){
return sn_bc.GlideBusinessCalendarUtil.getCalendarEnd("Fiscal Year", "0", "This Fiscal Year")
},
type: 'df_GetFiscalPeriod'
});
2. Add filter options to the Business Rule
Update the 'Get Date Filter Options for Date Filters' Business Rule, and add the following lines at the bottom of the script to use the filters defined in our script include:
answer.add('190_Last Fiscal Month@javascript:new df_GetFiscalPeriod().getStartofLastFiscalMonth()@javascript:new df_GetFiscalPeriod().getEndofLastFiscalMonth()', gs.getMessage('Last Fiscal Month'));
answer.add('195_This Fiscal Month@javascript:new df_GetFiscalPeriod().getStartofThisFiscalMonth()@javascript:new df_GetFiscalPeriod().getEndofThisFiscalMonth()', gs.getMessage('This Fiscal Month'));
answer.add('200_Last Fiscal Quarter@javascript:new df_GetFiscalPeriod().getStartofLastFiscalQuarter()@javascript:new df_GetFiscalPeriod().getEndofLastFiscalQuarter()', gs.getMessage('Last Fiscal Quarter'));
answer.add('205_This Fiscal Quarter@javascript:new df_GetFiscalPeriod().getStartofThisFiscalQuarter()@javascript:new df_GetFiscalPeriod().getEndofThisFiscalQuarter()', gs.getMessage('This Fiscal Quarter'));
answer.add('210_Last Fiscal Year@javascript:new df_GetFiscalPeriod().getStartofLastFiscalYear()@javascript:new df_GetFiscalPeriod().getEndofLastFiscalYear()', gs.getMessage('Last Fiscal Year'));
answer.add('215_This Fiscal Year@javascript:new df_GetFiscalPeriod().getStartofThisFiscalYear()@javascript:new df_GetFiscalPeriod().getEndofThisFiscalYear()', gs.getMessage('This Fiscal Year'));
Using the Solution:
In your Dashboard, select Interactive Filter, and choose the Date one.
In the interactive filter, you will see the new fiscal period options available in the select list for Dates:
--
Explanation for the Script Include code:
I got the filter syntax for this script by copying the filter Query values of the Fiscal Period filters on regular list views (enabled by the calendar pairing, in prerequisite step 3):
Query copied from this screenshot:
time_card.approved_onONThis Fiscal Month@javascript:sn_bc.GlideBusinessCalendarUtil.getCalendarStart('Fiscal Month', '0', 'This Fiscal Month')@javascript:sn_bc.GlideBusinessCalendarUtil.getCalendarEnd('Fiscal Month', '0', 'This Fiscal Month')
These filters seem to use a hidden class and its functions, which are pretty straight-forward to understand:
sn_bc.GlideBusinessCalendarUtil.getCalendarStart('<Fiscal Period Type>','<number of periods to offset>','<Filter Name>')
sn_bc.GlideBusinessCalendarUtil.getCalendarEnd('<Fiscal Period Type>','<number of periods to offset>','<Filter Name>')
Okay, I got curious from your question, so quickly migrated my dashboard in our Dev environment.
Bad news, the filtering in PA is getting it's relative values from somewhere else, as my fiscal filters don't show up:
Going to need to figure out where it's pulling that list from, and how to update it.
Edit: Just found this text on the Docs page for creating PA Date filters:
Says they don't allow adding your own predefined ranges. So no Fiscal Periods in PA as of now. 😕
