In my last article Which is the best way to report on a Multi-Row-Variable-Set (MRVS)? I pondered the different ways to report on Multi-row variable sets. I explored that Remote tables, my most recently discovered plaything, can be used to report on anything, including multi-row variable sets.

In this article, I’ll outline an approach to easily create no-fuss remote tables to allow users to report on MRVS.

Example MRVS

Using the same MRVS from my previous article, I’ve created an MRVS named Demo MRVS. You can follow along with your own MRVS, as this tutorial can be applied to any MRVS.

MRVS with three variables

MRVS in Employee Service Centre

Create Scoped Application

It’s generally considered good practice to create scoped applications when creating custom functionality. While it’s not necessary for a Remote Table, and quite possibly overkill, it does make this approach simpler when coding the Remote Table.

For this example, I have created a scoped application simply because it won’t prefix my remote table fields with “u_”, which will make things easier for us a little later.

Create a Virtual Table

There doesn’t appear to be a way to create virtual tables from ServiceNow Studio, so you will need to create them in the core UI. To create a virtual table, navigate to System Definition > Remote Tables > Tables and select New, labelling and naming your table accordingly, ensuring the Remote Table flag remains checked.

This table is virtual (not real), so it is not considered a custom table. As always, if unsure, speak to your Account Manager.

Adding Columns

Create a field called Requested item [requested_item] with a Type of Reference. This will be used later to link our results to the Requested Item.

If you didn’t create a scoped application, it won’t be possible to create fields with the same name as your variables – they will all be prefixed with “u_”. You’ll need to code for this later in the definition.

For each Variable in your Variable Set, create a new field with the same name, using the most appropriate data type. If your Variable names are greater than 80 characters, you’ll have to truncate them.

Creating a Virtual Table

The Sys ID field is generated automatically.

To effectively report on Variables that allow multiple selection, you’ll need to use the Choice type and populate it with the same choice labels and values available on your Catalog item. This is double-handling, so the choice (pun intended) is yours.

Virtual Choices

Now that we have our virtual table, we need to create the definition to populate it with data.

Create Definition

Remote Table Definitions can be created from ServiceNow Studio using file type Remote Table or by navigating to System Definition > Remote Tables > Definitions. The created Remote Table will come under “Other” in ServiceNow Studio.

Link your Remote Table definition to the Virtual table you created earlier via the Table field. You can disregard the IntegrationHub message as we are not calling data outside of our instance.

Now, here is the amazing part. Paste the following code into the Script field:

(function executeQuery(v_table, v_query) {

    var result = [];
    var variableSetSysID = 'c95760b2c3167e50992433bc050131e5'; // Your variable set sys_id HERE
    var multiRowQuestionAnswerGR = new GlideRecordSecure('sc_multi_row_question_answer');
    multiRowQuestionAnswerGR.addEncodedQuery(`variable_set=${current.variableSetSysID}`);
    multiRowQuestionAnswerGR.orderBy('parent_id');
    multiRowQuestionAnswerGR.query();
    while(multiRowQuestionAnswerGR.next()) {
        var index = multiRowQuestionAnswerGR.getValue('row_index');
        var myResult = result[index] || {requested_item: multiRowQuestionAnswerGR.getValue('parent_id') };
        myResult[multiRowQuestionAnswerGR.item_option_new.name + ''] = multiRowQuestionAnswerGR.getValue('value');
        myResult['sys_id'] = gs.generateGUID();
        result[index] = myResult; // Assign back to result
    }

    for (var d in result) {
        v_table.addRow(result[d]);
    }

})(v_table, v_query);

Update the variableSetSysIDcolumn to match the sys_id of your Variable Set, and this code will work for any Variable Set, provided you have matched the field names exactly. If not using scoped applications, you can update the myResult[multiRowQuestionAnswerGR.item_option_new.name + ''] code to use the “u_” prefix instead.

What about Security?

You may have noticed that GlideRecordSecure is used to ensure ACLs are enforced as the user running the report. To prevent data leakage to other users via the cache, ensure Cache Isolation Level is set to Cache per User.

You’ll also need to ensure the appropriate ACLs are in place to allow users to read and report_on your remote table. Some of these may have been automatically created with a generated role associated with your application.

Reporting

With your MRVS, Remote Table, Definition and Security in place, you can view your populated table from a list as follows:

List of MRVS

From here, your users can create reports, dot walking off the Requested Item field as required, and filter based on the defined choices.

Creating a report

What’s next?

The next time your customer is looking for an MRVS report – or a report that displays everything by row – you’ve got a new tool in your toolbelt to use to delight your customers

Is there anything that MRVS can’t do?

Let me know your use cases!

The post How to use Remote Tables to Report on MRVS appeared first on The SN Nerd.