How to use the UUID from the pa_score_notes table to build a report

[Daniel Falarde1]

Hello ServiceNow Community fellows;

I am trying to build a report on the Comments that Analysts put inside the Analytics Hub, on specific data points / indicators / breakdown, elements.

The comments are stored in a table called pa_score_comments and a there is a UUID in this table that is a concatenation of 3 sys_id's separated by a colon (:) character.

The first portion is the indicator sys_id, the second portion is the breakdown sys_id and the third is the element (note you can get deeper into the second level breakdown and element also but I am not going there). Refer to this link to understand how ServiceNow builds the UUIDs.

What I need is a script or something that will allow me to make use of this UUID to build a report.

The report should contain the following : 

• Indicator name (first part of the UUID);
• Breakdown name (second part of the UUID);
• Element name (third part of the UUID);
• Date of the data point that has a comment;
• Target;
• Actual score;
• Comment;
• Comment date;
• User name that created the comment;

At this point I was able to build a good portion of my report by first building a Database View of the following tables :

Then reporting on the different columns I needed. Thus far, I am able to get the following :

As you can see, I am missing lots of important information, hence me trying to un-concatenate the UUID.

In addition to this, I am able to build and use an Interactive filter on the "Comment created on" as this is a real date/time type field however, I also need to create an Interactive filter on the Month and Year of the data point but, the start_at field in the pa_score_notes table is not of type date/time but rather Integer date. I need to reformat this into a real date/time field so I can build a Date type Interactive Filter and need to present the resulting date in a mmm-yyyy format (i.e.: May 2022).

Ok ok this is a very technical, detailed, long winded question however, as you may see, I made an effort to at least get somewhere.

Does anyone have ideas to help me achieve the intended result ?

Thanks in advance to all my fellow community members who will be willing to give it a try.

[Adam Stout]

Due to potential performance issues, I would recommend not querying the pa_scores tables directly.  I see that isn't there now, but make sure to leave that out.

I'm not sure that a database view will meet your needs here since you need data transformation which this doesn't do.

Depending on the data volume, I would look at using a Remote Table.  This will allow you to do any data transformations you need as well as call the PA Scorecard API to get the actual scores as needed.  The only catch here is that you are limited to 1,000 rows at a time (or you need to do the pagination) and you need to handle the filters.  A plus side is you can do things like transform the integer date to a real date and use a normal date interactive filter.

[Daniel Falarde1]

Hi Adam,

Thank you for your quick reply.

In using a remote table, I guess that instead of getting data from an external source, I could re-use my DB-View that I already built as a starting point ?

Or do you use what the Scorecard API returns to populate the Remote Table ?

If so, do you call it (the API) with a business rule on the pa_score_notes table, like "on create new record do..."?

Thank you !

[Adam Stout]

You could reuse your view but that isn't always the more performant since it joins all the data then we would filter on it.

Take a look at "Catalog Items and Variable Sets" as an example of a Remote Table definition.  You'll run the base query, then for each row look up other needed data (like calling the PA API to get the actual score).