How to Report on Variable Editor Variables from Tables Other Than Requested Item
By Jack Thomas
However, there’s actually data in that field if we look at the record:
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:
Second, add the “Left join” field to the “View Table” form by configuring it:
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.
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
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
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!
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:
