Interactive Filters Based on Client Information

[daburges]

Hello all. I am developing Interactive Filters for my University's IT Department and our Desktop Support and Support Desk staff would really benefit from Interactive Filters that could filter Incident tickets by qualities of the Clients.

For Example: I would like to see all the Incident Tickets in which the client is from the literature department

we also have choice list fields for whether or not the client is an active employee, student etc...

The way I want to formalize this request is that I am looking to create an interactive filter that acts on the [sys_user] object (the client) and filters based on the qualities found in this object.

I am hoping to have a multiple choice reference filter that will list all available departments or all the choices possible for whether or not a client is an employee and filter based on the selected department in the Incident table.

I am afraid this may be too difficult to reproduce with Interactive Filters but If we could get this information quickly it would save our Desktop Tech's hours of figuring out where all of their tickets are located.

[pieter_goris]

Darrion,

for my understanding;

-you want to use the interactive filters for filtering on incident reports;

-have an interactive filter on a choice list of the sys_user table that list clients/incident caller as active employee, student, etc

-have an interactive filter that shows the department of the clients/incident caller. What type of field is this department (choice list or reference) and is it available directly on the incident form, or is it available as an attribute on the sys_user table?

Cheers, Pieter

[daburges]

Hi Peiter,

Thanks for taking the time to answer my questions.

Yes, Interactive filters for filtering on incident reports;

A help desk staff can see the details of the client within the incident ticket with a widget that pulls key fields from the Client's sys_user table entry.

Within the sys_user table. a person's department is a reference field while the employee/faculty fields are all choice lists

[Josh Cooper]

Applying the breakdown dynamically may be a challenge, but to get the filter itself working, you should be able to do a new automated breakdown for any field that lives on the sys_user table (from Incident).   Because of the way ServiceNow does the lookup, you can dotwalk that one layer in - so you can add a breakdown for Student/Faculty/etc provided that's a choice list on the sys_user table.

Department will be trickier, because it's another reference field, so to get that additional level of dot-walking, you'll have to merge the first two in a database view and report on THAT.   That said, since you're doing a Database view anyway, it makes more sense to do the breakdown (for the sys_choice list) directly against that Incident-User database view table that you create.

It has to do with the way we handle those lookups, anything beyond 2 steps abstraction is just a sys_id, with no Display Value, so for a table (current), you can see anything on current and current+1 (incident.field, and incident.caller.field) but anything beyond that (which includes Reference fields, because you're really dotwalking to the Display field for that reference) you have to join.   So to get current+2 fields (like incident.caller.department.name, or incident.caller.department.getDisplayValue() ) then you'll have to merge those first two into a single database view, and report on that (database views become reportable and act in the system just like tables.)

So if you merge incident and user together, then now all of your user fields for the incident will be available on that top level (current.user_department.name instead of current.caller.department.name), so department is now just +1 instead of +2.)

If you needed to get the manager of that Department (another dot-walk) then you'd have to merge the first 3 tables, etc...

Database views may sound complicated, but they're really not that bad - it's basically a join in the database, and ServiceNow helps you build them out.   In the Database views section, you pick a table, and give it a prefix, then a second table, and give it a prefix, then the condition that links them.   In this case, if you made the prefix for Incident 'inc' and the prefix for sys_user 'user' then you condition would be something like 'inc_caller = user_sys_id' (matching the actual value of the caller reference field to the record on the user table).

Just a reminder, once you've put that together, you'll be reporting on the database view instead of the incident table directly, and the field names will all have the prefix added to them.

[pieter_goris]

Hi Darrion,

Reference interactive filter

the interactive filter on incident reports for the user's department should not be a problem. The user's department is a reference field on the sys_user table. When you define a 'reference' interactive filter, you can also define (after initial save) how it should be referenced from which table. (You can make references from more than 1 table.)

Choice list interactive filter

This involves a little more.   If an interactive filter needs to filter the results of a report, but the filter is based on a choice list that is not defined on the same table as the report is built on, then a custom interactive filter is needed. You could define a new 'Dynamic Content Block' to achieve that.

In a test instance I've created a user-defined choice list on the sys_user table called 'Client Type':

On the user form:

Dynamic Content Block definition

1. For a 'checkboxes' interactive filter you could define the following:

Find attached a file ('Custom Interactive Filter - Checkboxes') that holds the dynamic content for this block. Best to replace the orange, italic text with the name of your choice list. You must replace the blue, italic text with the appropriate table and column names that reflect the config in your instance.

2. For a 'Select Multiple Input' interactive filter you could define the following:

Once more, find attached a file ('Custom Interactive Filter - Select Multiple Input') that holds the dynamic content for this block. Best to replace the orange, italic text with the name of your choice list. You must replace the blue, italic text with the appropriate table and column names that reflect the config in your instance.

So in my instance the result looks like:

The filters from the user (and department) table work on the incident report.

As you probably notice, at the top of the dashboard I've added the 'Debug homepage filters' widget. This helps a lot when you are developing custom interactive filters. Select values from your filters and see the query changes in the debug widget (and in the report!):

Finally, don't forget to set the report to follow the interactive filters from the report widget config form:

and:

Hope this helps.

Cheers, Pieter