How to Construct the List report URL

nataraj1 · ‎04-11-2014

Hi Everyone,

I am trying to change the SLA Availability Gadget reports.

My requirement is I need to generate a link to open LIST report with pre-populated table name and some filter conditions.

Below Availability and Calender type report URLs are working perfectly when I click my constructed URLs.Results are attached.

Type: Calendar

var url = 'sys_report_template.do?sysparm_table=change_request$[AMP]sysparm_type=calendar$[AMP]sysparm_cal_field=end_date$[AMP]sysparm_from_list=true$[AMP]sysparm_manual_labor=true$[AMP]sysparm_query=^end_date%3Ejavascript:gs.endOfLastMonth()';

Type: Availability

var filter = "service_commitment=" + avail.service_commitment;

filter += "^service_offering=" + avail.service_offering;

filter += "^type=daily^startBETWEENjavascript:gs.daysAgoStart(7)@javascript:gs.daysAgoEnd(0)^EQ^TRENDBYstart,date";

var url = 'sys_report_template.do?sysparm_type=availability';

url += '&sysparm_title=${URL:avail.service_offering.name} SLA Results (Last 7 days) ${URL:avail.service_commitment.name}';

url += '&sysparm_table=service_sla_result';

url += '&sysparm_chart_size=large';

url += '&sysparm_manual_labor=true';

url += '&sysparm_trend_field=start';

url += '&sysparm_trend_interval=date';

url += '&sysparm_aggregate=AVG';

url += '&sysparm_sumfield=absolute_percentage';

url += '&sysparm_query=' + filter;

url;

I tried to modify above both codes to generate LIST type report, but either code is not working!

Try : 1

var url = 'sys_report_template.do?sysparm_table=service_sla_result$[AMP]sysparm_type=list$[AMP]sysparm_manual_labor=true$[AMP]sysparm_query=^startBETWEENjavascript:gs.daysAgoStart(7)@javascript:gs.daysAgoEnd(0);

Try: 2

var filter = "service_commitment=" + avail.service_commitment;

filter += "^service_offering=" + avail.service_offering;

filter += "^type=daily^startBETWEENjavascript:gs.daysAgoStart(7)@javascript:gs.daysAgoEnd(0)";

var url = 'sys_report_template.do?sysparm_type=list';

url += '&sysparm_title=${URL:avail.service_offering.name} SLA Results (Last 7 days) ${URL:avail.service_commitment.name}';

url += '&sysparm_table=service_sla_result';

url += '&sysparm_manual_labor=true';

url += '&sysparm_query=' + filter;

url;

Could anyone please help me on how to generate/construct LIST report URL?

ralphho · ‎11-26-2014

Hello Nataraj,

I have examined how to create a list report in ServiceNow with a URL. This is what I've discovered:

The URL to create a list report has the following system parameters:

"manual_labor", value = "true"
"form", value = "list"
"query", value = <fields you want to filter on and (optional) on which field the results should be ordered>
"field_list", value = <the fields that you want to see in the resulting list>
"type", value = "list"
"table", value = <table name>

Sample 1 (replace the <company name> in the URL):

Creating a list of incidents resolved between 26 October 2014 and 1 November 2014, showing the fields "Active", "Number", "Short Description", "Created" and "Resolved", ordered by "Resolved":

https://<company name>.service-now.com/sys_report_template.do?sysparm_manual_labor=true&sysparm_form=list&sysparm_query=u_resolvedBETWEENjavascript:gs.dateGenerate('2014-10-26','00:00:00')@javascript:gs.dateGenerate('2014-11-01','23:59:59') ^EQ^ORDERBYu_resolved&sysparm_field_list=active,number,short_description,sys_created_on,u_resolved&sysparm_type=list&sysparm_table=incident

Sample 2 (replace the <company name> in the URL):

Creating a list of incidents, created today, which are not resolved or closed, showing the fields "Number", "Short Description", "Assignment Group" and "Assigned To":

https://<company name>.service-now.com/sys_report_template.do?sysparm_manual_labor=true&sysparm_form=list&sysparm_query=sys_created_onONToday@javascript:gs.daysAgoStart(0)@javascript:gs.daysAgoEnd(0)^stateNOT IN6,7^EQ&sysparm_field_list=number,short_description,assignment_group,assigned_to&sysparm_type=list&sysparm_table=incident

Note 1: Make sure that your date settings are set to US (mm/dd/yyyy)
Note 2: You can not use the word EXCEL in the URL to download the report. The selected fields will not be downloaded in this case. You need to create a sessions in code and use this session to download the report. I managed to get this working, but it was quite a puzzle.

Kindest regards,

Ralph Hodenius

nataraj1 · ‎12-17-2014

Thanks a lot Ralph. Much Appreciated!

lajakkal · ‎04-16-2015

Hi Ralph,

Thank you so much! This has brought me a step closer to what I was trying to achieve. But now I am trying to do the same to generate a bar or pie chart but it keeps complaining that "GroupBy" field is not defined. I have added a parameter for Group by in your url and changed the type to bar. But it doesnt seem to be working. Have you tried to generate a chart using the url before? Please let me know. Appreciate it!

northernb · ‎11-11-2016

Hi Laxmi,

I realise it is some time since you originally posted but I thought I would reply both for your benefit and for anyone else that reads.

Are you using

...&sysparm_group=<fieldname>&...

or

...&sysparm_query=...^GROUPBY<fieldname>...&...

I haven't tried but I suspect it should be the latter

As you are doing graphs if you have seen anything like:

ERROR: Cannot generate chart data: Missing parameter sysparm_trend_interval for series 0

Then I managed to find that including in the url:

...&sysparm_from_list=true&...

stopped this.

I also found that

...&sysparm_interval=...

does not work and you need to use

...&sysparm_trend_interval=...

to set the "per" field of "trend by".

Mark