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.

[lauri457]

Variable columns do not show on List Report when selected table is a Database View - Known Error

Not having variables loaded automatically in db views is a limitation in the platform, see above. But you can join the variable m2m to your db view like in the below screenshot. From the dependent item field in the m2m you dotwalk and get the variables.

You can't dot walk in the where clauses so you have to filter out the empty value [sc_item_option] records related to your MRVS variables in your actual report to avoid a bunch of useless rows. 

Another option similar to the remote table is to just use for example a scheduled script to create a csv with your data in a format that makes most sense and then send it.
Generate CSV file through script - ServiceNow Community

[Ravi Gaurav]

Hi @anandsuresh 

You can’t directly create a single out-of-the-box report that joins sc_req_item with the MRVS table (sc_multi_row_question_answer) because ServiceNow’s reporting engine doesn’t support table joins the way SQL does.

If you want a native report:

1. Navigate to System Definition → Database Views.

2. Create a new view joining sc_req_item and sc_multi_row_question_answer:

   • Primary table: sc_req_item

   • Secondary table: sc_multi_row_question_answer

   • Join condition: sc_multi_row_question_answer.parent = sc_req_item.sys_id

3. Name it something like RITM_with_MRVS_View.

4. Build your report using this view instead of the base table.

This lets you use the standard report builder and even schedule it like any other report.

And you can Build a Scheduled Scripted Report that collects both the RITM data and MRVS answers in a GlideRecord script, merges them, and exports the result as CSV or PDF.
If you need code let me know