question Re: how do report on multi row variable set in Developer forum

how do report on multi row variable set

MMKK — Thu, 31 Jan 2019 17:54:30 GMT

Hi All,

I wanted to know if there is any way that i can report on Multi row variable set variables. I really need this feature and i see that there is some limitation.

Please share any alternate way.

Re: how do report on multi row variable set

Shiraz2 — Wed, 29 May 2019 20:51:23 GMT

Hey MMKK, were you able to find any solution for your issue listed above? if you did, can you share please?

Re: how do report on multi row variable set

User658787 — Thu, 30 May 2019 21:00:50 GMT

You need to build a new database view joining the sc_multi_row_question_answer table and the other tables you are using. As an example:

Re: how do report on multi row variable set

Shiraz2 — Fri, 31 May 2019 17:12:22 GMT

Thank you Jesse Cregan. I appreciate your assistance.

Re: how do report on multi row variable set

Tadz — Fri, 06 Sep 2019 10:27:21 GMT

Hi has there been a solution for this?

Re: how do report on multi row variable set

Tadz — Fri, 06 Sep 2019 10:41:55 GMT

Hi upon checking the documentation there is a limitation.

Link: https://docs.servicenow.com/bundle/london-it-service-management/page/product/service-catalog-management/concept/c_ServiceCatalogVariableSets.html

The only thing that i can think of is creating a custom table to store the values for reporting.

Re: how do report on multi row variable set

Kevin133 — Mon, 16 Mar 2020 21:28:57 GMT

You can actually report on MRVS by creating a database view, then use that view to report on

Create a new Database view (System Definition -> Database Views)
1. Give it a name like 'u_sc_req_item_multi_var'
2. Label like "Multi Row Variable Set"
3. Plural like "Multi Row Variable Sets"
4. Description like "Joins the sc_req_item to the Multi Row variable set table to be able to report"
Save
Under View Tables click "New"
1. Table: Requested Item [sc_req_item]
2. Variable Prefix: sci
3. order: 100
4. Where clause: mrqa_parent_id = sci_sys_id
5. Click "Submit"
Under View Tables click "New"
1. Table: Multi Row Question Answer [sc_multi_row_question_answer]
2. Variable Prefix: mrqa
3. order: 100
4. Where clause: keep this field blank
5. Click "Submit"

You should now have a DBV that looks something like this:

Create a new report

Select Table as source and select your new Database view (Multi Row Variable Set) as the table

You should now be able to create a query and select the catalog item that you want to report on that has the MRVS, as well as adjust your columns to display the Question and Value as well as any other columns you want on your report.

Please mark this answer as correct or mark has Helpful if helpful.

Re: how do report on multi row variable set

dj7887 — Thu, 09 Jul 2020 19:52:33 GMT

Hi Kevin,

I created report mentioned in steps. However i also want to have RITM variables on same report.

Could you please guide me to get RITM variables on report combined to Multi row set data.

Thanks,

Dhawal.

Re: how do report on multi row variable set

kevinray — Fri, 10 Jul 2020 14:45:46 GMT

Sure, but keep in mind your going to get a lot more records returned because your going to have an MRVS row for every variable now.

Add the following three rows to your database view. NOTE: You may have to add the "Left Join" column to your view and/or form (depending how you are creating these)

Assuming you use the same naming convention:

Question(sio_item_option_new) will be the label of your variable
Value(sio_value) will be the value of the variable.

Re: how do report on multi row variable set

KaMmILa__ — Wed, 27 Jan 2021 23:24:26 GMT

We have a similar requirement , Thanks for all the info above, I am able to report using the above mentioned solution but we have a reference field where the values in that are displaying as sys_ID , is there a way we can get the values for that instead of sys_id ?

We tried to create a string field and on change/selection of the reference field the value is copied from that reference field to string field , using this we can see the values in the report but we are not able to hide the string field from the form as there is a limitation from servicenow( we don't want to show the same field twice on the form)

Is there any alternate solution for that ?

Re: how do report on multi row variable set

kevinray — Tue, 16 Feb 2021 20:21:52 GMT

Hi.

Unfortunately that SYS is what is stored in the value field of the MRSV table. It's not a traditional reference field field that does the value lookup for you.

I think your best bet here would be to add a string variable to your variable set. (just hide it from your catalog and request item views (so people dont see it) Then create a client script to set the new variable to the value of the reference field. something like this:

function onChange(control, oldValue, newValue, isLoading) {
    var refField = g_form.getReference('field_that_is_a_ref',setValueToRef);

function setValueToRef(refField){
    g_form.setValue('new_string_field', refField.name);
  }

}

Then you can use this new field in your report instead of the actual reference field with the sys_id

I'm suggesting you do the call back method (with function) so it's asynchronous. You don't have to do this but it helps performance.

Here someone asked the same question: https://community.servicenow.com/community?id=community_question&sys_id=29476a85dbc273c00be6a345ca961965

Re: how do report on multi row variable set

Sharad9 — Wed, 02 Feb 2022 12:14:28 GMT

Hello,

In the multi-row variable set we have 2 questions and both are coming but in the report we want only 1 question.

Can someone please help on this to achieve.

Thanks,

Sharad

Re: how do report on multi row variable set

kevinray — Fri, 04 Feb 2022 16:09:25 GMT

Just add the filter to your report.

Question IS [variable you want]

Question IS NOT [Variable you don't want]

Re: how do report on multi row variable set

Julian12 — Wed, 02 Mar 2022 19:03:19 GMT

Hey i used your response and worked.

But i had to correct the where clause from mrqu to mrqa to match on the prefix of the other table...

Could you correct that for others?

Re: how do report on multi row variable set

Kevin133 — Wed, 02 Mar 2022 21:18:25 GMT

Yep. Thanks for catching the typo.

Re: how do report on multi row variable set

Samarendra Naya — Fri, 20 May 2022 05:25:24 GMT

Hi All,

I have configured as below, but it did not worked for me as i am not getting any record.

How can I take all the variables(variables in multirow variable set and in RITM) in row (like list view) and their respective values , I am unable to find the variables to add it.

Please help

Thanks

Samarendra Nayak

Re: how do report on multi row variable set

Kevin133 — Fri, 20 May 2022 15:51:28 GMT

Hello Samarendra,

It's impossible to troubleshoot why your not getting values. It could be a million things.

First makes sure you are using an MRVS for a sc_req_item. This database view is for catalog items only. not record producers. Are you getting results if you don't filter down to a specific Item? If no, then the issue is your database view. Make sure you don't have any typos in your Where Clause

The "Question" column is the variable name and "Value" is the value

Re: how do report on multi row variable set

Samarendra Naya — Sun, 22 May 2022 05:02:31 GMT

Hello Kevin,

Thanks for replay,

I have now able to get the records but all are multiple records for same request and I am unable find all the variable name(catalog variables & variable set variables) to select as I want all the variables as row.

In my catalog item, there are some variables which for end user to fill at the time of submit the request and the multi row variable set is available for fulfiller(task) to fill at the time of fulfil the task.

Thanks,

Samarendra Nayak

Re: how do report on multi row variable set

Samarendra Naya — Mon, 23 May 2022 09:00:00 GMT

Hello Kevin,

I want to create the database view with sc_task table, as my MRVS is available in task and an engineer will fill those at the time of closing this task.

I have tried as below, Please suggest.

Thank,

Samarendra Nayak

Re: how do report on multi row variable set

Samarendra Naya — Mon, 23 May 2022 09:13:56 GMT