Adam Stout
ServiceNow Employee
ServiceNow Employee

In the Performance Analytics & Reporting Office Hours on July 23rd, we discussed different ways to query data from multiple tables, including Dot Walking, Related List Conditions, and Database Views. One of the use cases reviewed was pulling data from multiple m2m tables and joining data with non-reference joins using a Database View to report on what Reports are on what Dashboards.

In this post, we’ll review in more detail how to achieve this. I’ll assume you already know the basics of Database Views. If not, be sure to listen to the record from Office Hours before continuing.

Create a Database View

Navigate to Database Views and click “New”.

Note: Creating a database view requires admin access.

Once there, give the view a Name and fill out the Label, Plural (Label), and Description.

find_real_file.png

What these are isn’t necessary since they are just labels but remember what you used. Once this is complete, right-click on the header and click “Save”.

Add the Tables

Now that we have a view, we need to specify what tables to use. This view requires more tables than I usually use, given the complexity of the data. Here are the tables we need to add along with the variable prefix and where condition.

Table 

Order 

Prefix 

Where 

Left Join 

pa_m2m_dashboard_tabs 

 

100 

dt 

 

false 

pa_tabs 

200 

t 

dt.tab = t.sys_id 

false 

sys_portal 

300 

p 

t.page = p.page 

false 

sys_portal_preferences 

400 

prefrend 

p.sys_id = prefrend.portal_section && prefrend.name = 'renderer' && prefrend.value = 'com.glide.ui.portal.RenderReport' 

false 

sys_portal_preferences 

500 

prefrep 

p.sys_id = prefrep.portal_section && prefrep.name = 'sys_id' 

false 

sys_report 

600 

rep 

prefrep.value = rep.sys_id 

false 

report_stats 

700 

repstat 

rep.sys_id = repstat.report_sys_id 

true 

Tip: You’ll notice that pa_dashboards is NOT included in this view. This is not needed because pa_m2m_dashboard_tabs includes a reference to Dashboards so you can dot-walk to the Dashboard record if you want information from that record (like Owner or Group).

What my final view looks like:

find_real_file.png

Restrict the Fields

While not required, I recommend going back into each table record and set the “View Fields” to only the fields I need in the view. If you don’t, you have all the fields from all seven tables, making reporting a bit messy. The exceptions for this view are sys_report and report_stats, which I do not specify fields for, so all of the table fields will be included.

Here is an example of what the first table looks like:

find_real_file.png

Relabeling View Fields

For this view, the field labels still make sense even when combined, but if you needed to change the labels, check out this post on how to do that quickly and easily.

Verify the Results

We should be all set, so now click on the UI Action “Try It” to view the results.

find_real_file.png

Which gives us:

find_real_file.png

What’s Next

Now that we have this Database View, we use it where needed. Perhaps we add it as a related list on the Dashboard properties page or on the Report form (although this isn’t viewed very often). Maybe some reports and a dashboard based on this will help you as well.

Leave me a note in the comments about how you are using this.

23 Comments