Assistance related to report creation

[anandsuresh]

Hello Everyone,

We have a Service Catalog form that includes standard fields such as text, choice, and reference. Additionally, it contains a field that allows submission of multi-row values (MRVS – Multi-Row Variable Set).

I am trying to build a standard report that can be scheduled to automatically generate a list of all tickets raised in the previous month, to be delivered on the 1st of each month.

While creating the report using the Requested Items (sc_req_item) table, I can see only the standard field values. However, the MRVS data is not visible. After searching in Community, I found that MRVS values are stored in a separate table: sc_multi_row_question_answer

How can I combine both tables so that I can view all the data, including MRVS values, in a single report?

[anandsuresh]

@Bhuvan  and @lauri457 

I followed the steps exactly as outlined in the link. I was able to get halfway through, but I got stuck. I was able to retrieve the question, but for some entries, the "Question Answer" field was showing a sysID instead of the actual value, and the particular field type was a choice field.

Additionally, while the other normal values (non-MVRS) stored in the same Service Catalog are visible in the Variables section during standard report creation, they do not appear in the database view table (I cannot find the Variables while selecting the database view as the table). As a result, I'm unable to access the values stored in those variables. Any suggestions?

[lauri457]

Not sure what kind of requirements you have for your reporting but I doubt you will achieve that with database views. If you were to join [sc_item_option_mtom] and [sc_multi_row_question_answer] to [sc_req_item] you would have a row for every variable in the two tables.

Compare this to the way variables are loaded with the requested items where we have a column for every variable which is probably what you are after? 

With remote tables you can define the columns and their types as you would a normal table and apply any transforms you need (keeping in mind performance). I've also used import set tables and associated data sources with scheduled loads for similar purposes.

It's also good to consider how the service catalog is built in terms of categories and items. For example should one have a generic computer order catalog item with a model variable or use the hardware product catalog and have hw catalog items per model. This of course depends a lot on maturity and resourcing what is beneficial but relying on complex catalog items is usually not ideal as it can make seemingly simple things such as reporting difficult.

[anandsuresh]

@lauri457 - As I am unable to share my PROD info, I have re-created (replicated) a sample demo catalog in my personal PDI to explain what I am trying to do. Here, you can see, there is a container named Variables, and inside that, we have single-line variables followed by variable sets. So, I want all five field values (Your name, Your location, Operation, Window, Callout) in the same report. 

As I am unfamiliar with remote tables, I went with Database View and created a view like below by joining both tables.

Once I created the above view, I went to the normal report creation and chose the above one as my table source, and I was able to get the details of the MVRS fields (Operation, Window, callout) but the other two variable values (Your name, and your location) are not coming up in the Choose Columns options but when I try to create two reports separately, I could see the normal variables data in one report and MVRS values in another reports as shown below.

Right now, my only option is to schedule both these reports and then download them manually, and then do a vlookup in Excel, and then send the consolidated report to the team, but this is very time-consuming and manual work.
So, I just want to see if I am making any mistakes on the above, which is restricting me from getting those normal variable values, or is this the limitation in ServiceNow? I appreciate your suggestion.