Help creating report or dashboard using javascript created today at or before 8:30am?

khogan
Tera Contributor

4 hours ago

Hello,

 

I am trying to create report against the table change_request_metric.  That returns records in Authorized state, at or before today 8:30am. Also remove duplicate records, in the metric table only return the latest record in authorized. I can get the time 8:30am just fine in a list view. Example below. But I cannot do it in a report or dashboard. Looks like when I use created and trend, every hour is available but not half hours.

 

They want this as a dashboard for change review meetings, they do not want a scheduled report emailed.

 

Any suggestions would be appreciated.

 

/change_request_metric_list.do?sysparm_query=chg_state%3D-3%5Echg_active%3Dtrue%5Emi_definition%3Dc0f4f1e51bc36d509d932f88624bcbd9%5Emi_value%3DAuthorize%5Emi_sys_created_on<javascript&colon;gs.dateGenerate(gs.now()%2C%2708:30:00%27)&sysparm_first_row=1&sysparm_view=

0 Helpfuls
  • 157 Views
1 REPLY 1

Matthew_13
Tera Guru

49m ago

Hi,

You’re not missing anything that I see — this is a limitation of ServiceNow reporting.

What works in a list view (like gs.dateGenerate() and exact times such as 8:30 AM) unfortunately does not work in reports or dashboards. The report engine only supports hour-level time filtering, not minutes, which is why you can’t select 08:30 in a report even though you can see it in the list.

The second issue is the metric table itself. change_request_metric keeps multiple rows per change, and reports can’t reliably:

  • remove duplicates

  • and only return the latest Authorized metric

  • while also applying a time cutoff

That combination just isn’t supported out of the box.

What usually works best is creating a Database View that only exposes the latest Authorized metric per change. Once you do that, reports and dashboards behave normally and you can use “Today” filters without weird results. This is the cleanest solution and works well for CAB/change review dashboards.

If that’s too heavy, the only other practical workaround is:

  • filter to Today

  • and use a cutoff like before 9:00 AM (since half-hours aren’t supported)

That’s often “good enough” for meetings, even though it’s not perfect.

Unfortunately, there isn’t a way to do this purely in a dashboard report with minute-level precision today. It’s a platform limitation, not your query.

Hope that helps and saves you some time chasing it.

 

@khogan - Please mark as Solution Accepted and Thumbs Up if you find Helpful!!

0 Helpfuls