Multi-Row Variable Set - Reporting - Display Rows in to Columns

[Ahsan]

Hello Community,

I am reaching out for some support.  We have been leveraging the multi-row variable set for many of our catalog items with out consider some of the challenges in reporting on the date.  

Challenge #1 - there is no direct relationship to the sc_req_item or overall any request related table.  

--- We are leveraging a work around via database view.

Challenge #2 - Reporting Challenge: When you report on the MRVS, You get the data leveraging columns that are native to MRVS answer table.  However, for customers they want to see their data (rows) flipped to what they are used to across the platform in to columns.  So NOT seeing questions, answer, typ as columns. 

I have see many discussions on this topic from 2/3 years ago but I have not found a answer that speaks to challenge #2.  I look forward to hearing what you all have done to address this need. 

Ty

[Maik Skoddow]

Hi

due to the nature of a MRVS, reporting in the way you are requesting, is not possible. Already a catalog variable is not part of the sc_req_item table and a MVRS is nothing else than a JSON structure in a catalog variable.

Maik

[Carmen Galante]

I've had a simliar ask/challenge in the same area. We have a request and the form has a MRVS that has 6 questions that the user must fill in. The idea was to have a report to pull the data with the RITM and then a column for each of the 6 answers, similar to how they display in the RITM, 1 row per variable set response. I used a database view to achieve this. I joined the tables as follows:

Table	Order	Variable Prefix	Where Clause	Left Join
sc_req_item	100	ri		false
sc_multi_row_question_answer	200	quest1	(quest1_parent_id = ri_sys_id && quest1_item_option_new='9a8976cc70ae309004ed95d200c3b771')	false
sc_multi_row_question_answer	300	quest2	(quest1_row_index = quest2_row_index && quest2_item_option_new='5f486a9c70a6b09004ed95d200c3b766')	false
sc_multi_row_question_answer	400	quest3	(quest1_row_index = quest3_row_index && quest3_item_option_new='935a724070ee309004ed95d200c3b7a5')	false
sc_multi_row_question_answer	etc...	etc..	etc..	false

The first table is the REQ ITEM table, and second being the normal join that brings in the MRVS table, the clause here though only shows where the question SYSID is the first column/field in my MRVS. If you run this as it is, you will have a list of RITMS where the MRVS question 1 is answer. The 3rd row in the database view and beyond is joining the next question with the same row index as the first question, this ensures that your MRVS rows are matched up, and the SYSID of the 2nd question, and so on. You can name your variable prefix whatever you like that makes sense to you.

When you are finished building and testing this new view, you can go to System Definition > Language file and rename your column headers to be more friendly as they will all say VALUE by default. The table name will be your database view name, the Label field would be whatever you choose. The element will be your variable prefix from above then _value, ie quest1_value, quest2_value, and so on. 

I hope this is helpful, as like you I did a lot of searching to find nothing in solving this as well.

[Max Nowak]

Hi Carmen,

That was indeed very helpful, thank you. There's only one thing that's still missing for me: If my MRVS contains questions that are references, I'll only see the sys_id of the referenced records with this method.

In the example above, I quickly created a MRVS with the questions "Affected CI" (reference to cmdb_ci) and "Problem description" (string) for testing purposes, and added it to the default incident record producer. As you can see, for the field "Affected Ci", I'm only getting the sys id (as this is saved in the sc_multi_row_question_answer table), not the display value of the CI.

I guess I could join all referenced tables to this database view, I'm just wondering if there's a better way.