How to Report on Variable Editor Variables from Tables Other Than Requested Item

By Jack Thomas


Using the instructions here, you can add the variable editor to any task-extended table. However, if you try to report on those values in the usual way (i.e., what’s documented here), they’ll appear empty. Notice how the “User Reference” column seems empty in the screenshot below:

Catalog Task Report with columns for Number, Short Description, User Reference; includes SCTASK0010003, 'Just a Task', and '(empty)' details.

However, there’s actually data in that field if we look at the record:

Software interface with task number SCTASK0010003 and user reference "Abel Tuter."

So how do we get that variable into a report? By using a database view!

In this article, I’ll show you how to build a Database View that pulls in a reference variable on a Catalog Task record. Of course, if you want to use a different source or referenced table, modify the configuration accordingly. Also, if the variable you want to include isn’t a reference variable, you can skip adding the third View Table record. I’ll note this below.

Database

The solution here is to build a database view that uses a series of left joins to walk from Catalog Task [sc_task] to Question Answer [question_answer] (and then to a referenced table if necessary). We’ll essentially follow these instructions for our specific use case. 

First, create the Database View by following these instructions. It will look something like this:

Database view configuration screen with name 'u_sc_task_view', application set to 'Global', and description 'Catalog Task user reference on a database view'.

Second, add the “Left join” field to the “View Table” form by configuring it:

Interface showing lists of available and selected fields with navigation buttons in a software application.

Third, create the first View Table record:

  • Table: Catalog Task [sc_task]

  • Variable prefix: sctask

  • Order: 100

  • Left join: false

  • Where clause: (empty)

    • Yes, empty. If you only want to report on a subset of records on your report (which you likely do because the variable you’re adding might not be on every record), you’ll have to apply that filter on the report – not here.

Screenshot of a form interface displaying a dropdown for "Table" with "Catalog Task [sc_task]" selected, input fields for "Variable prefix" labeled as "sctask," and "Order" labeled as "100." Additional fields for "Application," "View," "Left join" checkbox, and a "Where clause" textarea are visible.

Fourth, add a second View Table record (left joined to the first):

  • Table: Question Answer [question_answer]

  • Variable prefix: qa

  • Order: 200

  • Left join: true

  • Where clause: qa_table_sys_id=sctask_sys_id

Screenshot of a configuration interface with fields for Table, Variable Prefix, Order, Where Clause, Application, View, and a Left Join checkbox.

Fifth, if the variable you’re trying to add to your report is a reference variable, add a third View Table record (left joined to the second). If it’s not a reference variable, you can skip this step.

  • Table: User [sys_user]

  • Variable prefix: user

  • Order: 300

  • Left join: true

  • Where clause: user_sys_id=qa_value

ServiceNow configuration panel showing table selection and parameters.

Optionally, you can now preview your Database View by clicking the “Try It” related link on the Database View record. Here’s what mine looks like (once I personalized my list only to show the relevant fields). Unfortunately, the label no longer matches the variable’s label. We now have “Name” instead of “User Reference,” but it’s better than an empty field!

ServiceNow task table with columns for Number, Short description, and Name. Contains task number SCTASK0010003 with description 'Just a Task' and name 'Abel Tutter'. Search fields are visible for each column.

If you wanted to take this one step further, you could also use View Field records to limit the returned fields in the table. It’s beyond the scope of this article, but you’d need to follow these instructions.

Finally, we can update our report. Instead of reporting on the Catalog Task [sc_task] table, we’ll report on our Database View:

And then we can build our report! Here’s what mine looks like: