Unifying Date Filters for Table-based Data Visualizations and PA Indicators in UI Builder

kelvensilva · 3 weeks ago

Hi everyone,

I am currently migrating a dashboard from Platform Analytics to UI Builder (Next Experience) for a Project Management (SPM) use case. I’ve encountered a challenge regarding Date Filters and I'm looking for the best architectural approach to solve it.

Current Setup: On the original dashboard, I have two separate filters due to how the data sources behave:

Standard Date Filter: Used for table-based Data Visualizations (Reports). It maps to fields like Time Card.Week starts on or Resource Aggregate Monthly.Month starts on.
Single Select (Breakdown) Filter: Used for Performance Analytics (PA) Indicators, where the filtering is done through a Date Breakdown.

The Goal: I want to unify these into a single filter component in UI Builder so the user doesn't have to select a date range twice. The user should select a date once, and it should simultaneously filter both the table-based reports and the PA-based indicators.

What I've Tried:

I looked into using Client State Parameters to store the filter value, but I'm struggling to find a way to make a single filter component emit a payload that satisfies both the table-based filter requirement (start/end date range) and the PA Breakdown requirement (which usually expects a specific breakdown element).

Question: Is there a way to configure a single Filter component (or a custom solution using Client Scripts/State) that can act as both a standard date filter and a PA Breakdown selector?

Thanks in advance.

jeffrubinoff · 3 weeks ago

Pardon a stupid question, but have you tried creating a Date filter? https://www.servicenow.com/docs/bundle/zurich-now-intelligence/page/use/par-for-workspace/task/creat...

kelvensilva · 3 weeks ago

Thank you for your reply!
Yes, I am already using the standard Date filter component. The challenge is that this filter works well for table-based visualizations, but it does not filter PA Indicators (Performance Analytics), which require a breakdown filter instead.
That’s why I end up needing two separate filters—one for tables and another for PA indicators.
My goal is to find a way to unify both use cases into a single filter component, so the user only needs to select the date once.

If you have any suggestions or workarounds for this scenario, I’d really appreciate it!

jeffrubinoff · 3 weeks ago

I am trying to describe your case to devs. What is the breakdown source of the Date Breakdown? Are you breaking down on when the week or the month starts, like with the table data?

kelvensilva · 2 weeks ago

Thank you for your follow-up and for helping to clarify the scenario with the devs!

Here are the details about my Date Breakdown setup:

Breakdown Source:
The Date Breakdown uses the breakdown source called Date.month.or.more.

This breakdown source is based on the facts table pa_buckets and the field Sys ID.
The source is filtered by the bucket group "Dates of the month for more", which contains buckets like "Last 12 months", "Last quarter", "This month", etc. (see attached screenshots for the full list).

Bucket Group:
The bucket group "Dates of the month for more" defines all the time ranges available for selection, such as:

Last 12 months
Last 2 quarters
Last 3 months
This month
This year
...and several others

Breakdown Mappings:
For each facts table that needs to be filtered by date, I have a Breakdown Mapping that links the breakdown to the appropriate date field.
For example, for the time_card table, the mapping is to the field week_starts_on.
Each mapping uses a script to determine which bucket(s) the record's date falls into.

Script Example:
Here is the script (adjusted for each table/field) that is used in the Breakdown Mapping. It checks which bucket(s) the record's date belongs to:

var getDates = function(date){
	var dateList = [];
	var gdt = new GlideDateTime(date);
	gdt.setTZ(gs.getSession().getTimeZone());
	var offset = gdt.getTZOffset()/1000;
	gdt.addSeconds(-1*offset);
	var dates = {
		0:  (gdt >= gs.beginningOfLast12Months() && gdt <= gs.endOfLast12Months()),
		1:  (gdt >= gs.beginningOfLast2Quarters() && gdt <= gs.endOfLast2Quarters()),
		2:  (gdt >= gs.beginningOfLast2Years() && gdt <= gs.endOfLast2Years()),
		3:  (gdt >= gs.beginningOfLast3Months() && gdt <= gs.endOfLast3Months()),
		4:  (gdt >= gs.beginningOfLast6Months() && gdt <= gs.endOfLast6Months()),
		5:  (gdt >= gs.beginningOfLast9Months() && gdt <= gs.endOfLast9Months()),
		6:  (gdt >= gs.beginningOfLastMonth() && gdt <= gs.endOfLastMonth()),
		7:  (gdt >= gs.beginningOfLastQuarter() && gdt <= gs.endOfLastQuarter()),
		8:  (gdt >= gs.beginningOfLastYear() && gdt <= gs.endOfLastYear()),
		9:  (gdt >= gs.beginningOfNextMonth() && gdt <= gs.endOfNextMonth()),
		10: (gdt >= gs.beginningOfNextQuarter() && gdt <= gs.endOfNextQuarter()),
		11: (gdt >= gs.beginningOfNextYear() && gdt <= gs.endOfNextYear()),
		12: (gdt >= gs.beginningOfOneYearAgo() && gdt <= gs.endOfOneYearAgo()),
		13: (gdt >= gs.beginningOfThisMonth() && gdt <= gs.endOfThisMonth()),
		14: (gdt >= gs.beginningOfThisQuarter() && gdt <= gs.endOfThisQuarter()),
		15: (gdt >= gs.beginningOfThisYear() && gdt <= gs.endOfThisYear())
	};
	for (var i in dates){
		if (dates[i] === true){
			dateList.push(parseInt(i,10));
		}
	}
	return dateList;
};

getDates(current.week_starts_on);

This script is adapted for each table, using the relevant date field (e.g., current.week_starts_on for time_card, or another field for other tables).

Breakdown Mappings in Use:
Currently, I have mappings for the following tables/fields (see screenshot):

u_user_monthly_hours.tc_week_starts_on
time_card.week_starts_on
resource_aggregate_monthly.month_starts_on
pm_project_task.actual_start_date
rm_sprint.sprint_start_date

Let me know if you need any more details or screenshots!
Thanks again for your help.