Reporting on Catalog item variables

[JagjeetSingh]

Hi All,

I have seen few questions about reporting on catalog item variables. Here is the solution. I'll keep it short and simple.

Understanding the structure

There are three main tables which we can use to map the catalog variables with requested items.

1.  Variable Ownership [sc_item_option_mtom] - This table has below two main fields that we are going to use:

- Parent Item - store the RITM number in reference field from Requested items[sc_req_item] table.

- Dependent item - stores the sys_id in reference field from Options[sc_item_option] table.

2. Options[sc_item_option] - This table stores the Question(catalog variable) and the value entered by user. After mapping this table with Variable Ownership table using Dependent item field, we'll have access to all values entered by user for a particular question.

3. Requested Item[sc_req_item]- We are familiar with this one. We'll use this to map with "parent item" field from Variable ownership table. As result we'll have mapping of Parent item field with RITM number and we can access all the fields from Requested item table.

Database view

Now lets just combine all three tables in a database view to get the desired result.

 

The resultant view will give you all the variables from catalog item and from requested items to report on.

If you want to include the variables from Order guide also then you have to use cascade variables functionality and then you'll see those variables as well in database view.

Feel free to give your ideas on improvement on this article. 🙂

Please do mark helpful and bookmark if possible. Thank you for reading.

[Vigneshwar Red1]

Nice information on this topic..

[DJL2]

Hi Jagjeet,

This is REALLY good, as Catalog Variables can very fiddly.

I actually have a use case on the work log which requires extraction of a common variable (finance code) against parent Items selected from a catalogue of some 300 or so types.  As you'd imagine, this it would be a nightmare to build such a report at the RITM level.

Your screenshot of the database view isn't showing anymore; would you mind re-uploading it?

Thanks,

DJL

[JagjeetSingh]

Thank you for your valuable feedback. I've uploaded the snapshot again.

[MattKing]

Hi Jagjeet,

We have followed your guide and have created the database view. We were hoping to create an indicator that sums one of the catalog item variables using PA, but can't see the variables from the requested item that we need in the 'field' dropdown. We have selected the database view that we created as the 'Facts table' on the indicator source, and have tried setting the 'view table' field to each of the 3 tables we have combined but can still only see fields on the requested item, no variables. 

Do you have any advice on how to do this, or whether it is possible?

Thanks

Matty

[JagjeetSingh]

I just tried this on my PDI. I can see the fields from all three tables to in my conditions.

Did you check the database view if it is populating the correct records.

Also, 'view table' won't impact the fields available in conditions. It works when you click "Show records" in the Analytics hub.

[Asep]

Hi @JagjeetSingh 

Kindly share the guidance step by step, currently i working on Utah version with same requirement to add catalog item variable into report, the use case is to get information which assets is requested along with the ticket and variables included.

Thanks!

really appreciate

[Shrey hurana]

Thank you for the article.

But is it possible to get the ritm number and the values in multi row variable set (MRVS) entered by user while raising the request in this same report

[Sean Witt]

@JagjeetSingh FYI looks like you can now accomplish this directly in reporting without having to create/add a new database view. The steps are described pretty well on the Product Documentation site on this page, called Create a list report with variable columns. So for anyone that would rather not go down the path of adding the database view, this may be an option worth exploring.

[Eoghan]

Hey Jagjeet, thanks for this!

Can you export a list view report to Excel using this?

[Shivani_singh]

Hi @JagjeetSingh 
I am trying to combine survey table and sc_req_item to get the variable field value in survey table.
But i am not able to do, have attach the screenshot.
Can you please help me to fix it.