How to Compare Date Field with Another Dot-Walked Date field in a Report?

[Dennis25]

Hi,

I need to report on the following:

Incidents where Change Request was created AFTER Incident was Resolved.

Report Criteria:

Table: Incident

 Incidents in State = Resolved or Closed

Incident.Change_Request is not empty

 Incident.Resolved < Incident.Change_request.Created

I am not able to select and compare these 2 date fields (Incident.Resolved and Incident.Change_request.Created) in the filter criteria.

Any suggestions?

Thank you.

[Community Alums]

Hello Dennis,

Since fields are present on two different table, report will not allow to compare. I will suggest you two solution - 

1) Not sure on exact requirement but if possible, write script include by gliding tables, adding queries and comparing two dates and return those filtered incident records syd ids. Call this script include in the report's filter condition.

Ex. Sys ID is one of javascript: new scriptincludename().functionname();

2) Create database view of two tables and then you will be able create report on database view and compare dates.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Chad Hall · Accepted solution]

There is another way that I've used.

1. While editing your report, click the option at the top to Switch to Classic UI.
2. In the filter editor, select Show Related Fields from the drop-down of the field you are wanting to change.
3. Click it a second time and choose Change Request --> Change Request fields.
4. Click it a third time and choose Created.
5. Run the report to verify results.
6. Save the report.
7. Click the link to Switch to New UI.

[Dennis25]

Hi Chad,

The solution you provided worked for me!

Thank you.