Reporting - Transpose catalog variable question field as column and variable value in row

[Community Alums]

I'm working on the report to display Catalog Variables along with Task, Item and Request. I've created a database view to join sc_req_item, sc_task and sc_item_option_mtom tables. The report displays all variables with value in the same row. Is it possible to display Question field from 'Variable Ownership' table as columns and 'Value' field in row? 

[Uncle Rob]

It's a gawd-awful royal pain in the sit-downer, but you CAN report on variables from the RITM table with a conventional report.

[Uncle Rob]

Turns out same is true for sc_task

[Community Alums]

Hi Robert,

Thanks for your response. I can get the variables from task or item table; but cannot add more than 25 variables in the report due to system limitation. I got about 80 variables and also dependent on Category/Sub-Category variable fields. The reason, I'm going with Variable Ownership table due to we can get only those variables which are filled in while creating a request and eliminate those which are not filled in.

[Thomas_Davis]

So I did something similar with Assessment data.  Where I had the question has a column header and the answer in the column and every row was an assessment.  If you are familiar enough with the Catalog Variable tables that you are working with, I am sure the same thought process will get it working.

I created a database view that I joined from the assessment instance (survey) to the assessment instance question. My first join was just to the assessment instance table, my second was from the assessment instance question table back to the assessment instance table. I grabbed the sys_id of the type of assessment (survey) and then grabbed the sys_id of the question that was tied to the sys_id of the assessment (survey)

inst.metric_type='502a2c44d7211100158ba6859e6103a3' && quest1.instance=inst.sys_id && quest1.metric='942a2c44d7211100158ba6859e6103a4'

When you do your second join, add your View Fields to that join as well. (You only have to do this once)

I then added all the questions that I needed.

This is what it looks like when I click Try It

You can see the last 3 columns are the questions (although abbreviated). 

When you get your columns correct and hopefully this has helped you, you might want to take a look at this Blog that will show you how you can rename the column headers (values) if they come back as like string_value or something else that really doesn't work.

https://community.servicenow.com/community?id=community_blog&sys_id=d044d1321bbe1c908672ea89bd4bcb32

This is what the view looks like:

Again, I am pretty sure you can use the same logic with the Catalog Variables.  I will try and see if I can build that type of view out as well.

Hope this helps,