This article is relevant for the following ServiceNow releases: Yokohama, Zurich.

---

Multi-Row Variable Sets (MRVS) are a fantastic way to add dynamic functionality to the humble Catalog item. They allow end users to add multiple rows of predefined variables to a form, opening up use cases such as data import, sub-requests and more! But how do you use this data effectively? Reporting, of course!

Did you know there are multiple methods to report on Mult-Row Variable Sets in ServiceNow? Not all of them work, of course, and some are better than others.

You may have noticed that if you try to report on them using legacy reporting or data visualisations, you can add the individual variables as report columns.

Adding MRVS variables to a report

However, nothing will show! That is because they are rows (there can be multiple per RITM) and therefore cannot be displayed.

Trying to report on a Multi-Row Variable Set

So how do you report on them?

Approaches

Database View

In his article How to Report from a Multi-Row Variable Set, fellow Australian ServiceNow enthusiast David McDonald outlines how to report on MRVS by creating a Database view. This will require the admin role and a solid understanding of ServiceNow data structures (or follow his instructions).

Remote Table

A remote table is a table populated via script that is stored in memory and persistent based on its cache settings. I outline how it can be used in my blog, How to report on anything and document how it can be used (and misused) in The Good, the Bad and the Ugly: Lessons Learnt using Remote Tables for reporting. Remote tables can be reported on like any other table (with a few limitations)

Consideration

We have two approaches – what must we consider when choosing an approach?

Complexity

Database views require a deep understanding of the ServiceNow database schema, and Remote tables require an understanding of ServiceNow scripting and performance best practices. Both are out of reach of the standard end user.

Effort

Reports that take too long to build may not be justifiable when compared to other requests on the backlog. Remote tables require scripting, so that is going to take more effort than a Database view.

Reference fields

Reference variables are stored in the Multi Row Question Answer [sc_multi_row_question_answer] table as a sys_id. This won’t be very helpful to humans reading a report. How do we handle this?

Display per row

With Requested Items having multiple rows, how will you display this to users? Per cell, per row, or per ticket?

Performance

Multiple tables may be involved, with Requested Items having potentially multiple MRVS rows containing multiple questions. Performance must be considered to ensure the report is usable for end users. Database views can be very performant when effectively constructed. Remote tables can be even faster when cached (but slower when first run).

Re-usability

Chances are, you have many MRVS across several Catalog items. Having customers need to request a special report for each MRVS is quite an overhead on the support team. Can it be avoided? Or baked into the development cost?

Security

You don’t want to be exposing data that users would otherwise not be able to view. Remote tables are scripted, so anything goes, and the data is cached, which could bypass even GlideRecordSecure.

Example MRVS

A demo MRVS has been created as follows:

• User -> Reference [sys_user]
• Cover -> Gold, Silver or Bronze
• Justification -> Multi-line text

MRVS as populated in a Catalog item

Database View

Once our database view is created (see How to Report from a Multi Row Variable Set), we need to filter where Multi Row Set = Demo MRVS.

List view of Database View displaying MRVS

Note that each value is displayed per row, making visualising and reporting on the data quite challenging. If you have a reference field, the user will be presented with a sys_id. However, the database view can be reused on any MRVS.

Remote Table

Once our Remote Table table and definition are created to add a row per MRVS row, navigate to the list of your Remote Table.

List view of Remote Table displaying a MRVS

Note that each MRVS row is displayed per row, and reference fields are shown as their display values. This makes reporting much easier; however, this remote table will only work for Catalog items that use the example MRVS.

Comparison

Let’s compare the two approaches side by side, so we can determine the best approach.

Considerations	Database View	Remote Table
Complexity	Low Code	Pro Code
Effort	Low	High
Reference fields	Shows sys_id	Shows display value
Display	Per variable	Per row
Performance	Fast	Slow
Reusabiltiy	Any MRVS	Single MRVS
Security	Be cautious	Can expose non-reference data

Conclusion

Like all development on the ServiceNow platform, every approach has its own unique considerations, advantages and disadvantages. The implementation approach taken will depend on the requirements given, the effort required, and the availability of development resources when compared to other demands.

If you are only concerned with a few text variables without complex filters, database views may be the best option. These can also be reused across all Catalog items without the need for additional reports.

However, having all variables on a single row and the ability to view and easily dot walk off reference fields make Remote Tables the superior solution, despite requiring a remote table for each MRVS. Remote tables will give the best UX, with a few trade-offs. Cleverly coded Remote Table definitions can still be reused across different MRVS, with only the schema needing to be defined.

Stay tuned for my next blog, where I will outline how to create a remote table for an MRVS in the most reusable way.

The post Which is the best way to report on a Multi-Row-Variable-Set (MRVS) ? appeared first on The SN Nerd.