How can I report on this month next year, relative to the beginning of the month

[Tony DiRienzo]

I am attempting to create a report that shows all records with a date field which is in a specific month next year.  For example, all contracts which end this month next year.  If the report is viewed on August 14, 2018, it should display all contracts with an Ends date between August 1, 2019 and August 31, 2019. 

I know how to accomplish this with a script, but I would like to know if anyone is aware of a way to create this result with the existing report filters.

The closest I have come is with the "relative" filter, but it provides results relative to the day the report is run and doesn't provide the option to use beginning of month.  Using my example above, it returns results from August 14, 2019 through September 13, 2019, which is not the desired result set.

Again, I'm wondering if there is a way our users can do this with the available filters, without requiring our development team to write a script.  Thank you for any help you can offer.

[Tony DiRienzo · Accepted solution]

I found a method of doing this without writing a custom script include, which was my goal.  It's not something I would be able to expect the average user to do, though, so it's not quite the ideal solution I was hoping for.  In case anyone else is looking for the answer to this question, here's an explanation of the solution I found:

Problem:

Imagine you would like to know all changes with a planned start date in the month 3 months from the current month.  It is August 15, so your desired result set is a list of changes with a planned start any time in November.  But on September 1, the report should display the changes with planned start anytime in December.  Using the "relative" filter won't work for this, because it is relative to the current day, and you can't make it relative to the current month.

The date filters are limited, and don't meet every need, and there isn't a way to easily add custom filters.  Since there is no "is (dynamic)" option on the date filter, and none of the filters allow entering arbitrary text, it isn't possible to enter custom javascript from the filters.

Solution:

You can build a javascript condition on the filter by directly modifying the query string in the URL.

For the example I gave above, you would use these steps:

1. Go to the list view of the change_request table.  It will be easiest if you don't have the nav_to.do wrapper around the list, so use this url: 

   your_instance.service-now.com/change_request_list.do​

2. Add a filter for "Planned Start on Today" AND "Planned Start on Today".  It doesn't matter that they are the same, since you will be modifying the URL. 
3. Run the filter.  It should give you a result like this:

   your_instance.service-now.com/change_request_list.do?sysparm_query=start_dateONToday@javascript:gs.beginningOfToday()@javascript:gs.endOfToday()^start_dateONToday@javascript:gs.beginningOfToday()@javascript:gs.endOfToday()​

4. Now modify the filter to have the conditions you need.  In this example, we will want to use gs.monthsAgoStart() and gs.monthsAgoEnd(), so the following would work:

   your_instance.service-now.com/change_request_list.do?sysparm_query=start_date>=javascript:gs.monthsAgoStart(-3)^start_date<=javascript:gs.monthsAgoEnd(-3)​

5. From the list view, right-click one of the column headers and select the "Pie Chart" option.  This will create a report from the filtered list, grouped by which ever column heading you right-clicked, and presented as a pie chart.
6. Change the report type to List and the group by to None.
7. Make any other changes to the report that are needed, give it a name, and save.

I hope this is helpful to someone.

[mcconnellsj]

Genius!