Report on variable values with group by value

[Biddaum]

I have a variable set that is being used to identify specific streams of work for a number of catalog items.

I have been able to create a report that will display the value of the variables in the variable set for example in a list view. However in order display the total number of requests for each stream I need to find a way to collate the values and display those totals for each value type.

The values are all for a single select box question, with the exact same set of values each time it is ansered.

EG:

What stream is selected?

- Stream 1

- Stream 2

- Stream 3

- Stream 4

I then want to show the total number in a single report where each stream total is shown, eg a pie graph

I thought it would be as easy as group by value, but it isn't an option.

I have tried this on a number of tables without success:

sc_item_option_mtom

sc_item_option

item_option_new

sc_task

sc_req_item

[Aanchal Agarwa1]

Hi @Biddaum   ,

The reason Group By doesn't work directly on those tables is that variable values are stored as rows in sc_item_option, not as columns — so a standard report has no single field to aggregate against. The fix is a Database View that stitches the variable value back to the RITM, giving the report engine something it can group and count.

Create a Database View with the following table joins in this exact order:

Table Variable Prefix Order Where Clause

Options (sc_item_option)	opts	100	(leave blank)
Variable Ownership (sc_item_option_mtom)	varown	200	varown_sc_item_option = opts_sys_id
Requested Item (sc_req_item)	ritm	300	varown_request_item = ritm_sys_id

Then build your report on this view:

• Type: Pie Chart (or Bar for easier reading across 4 streams)
• Group By: opts_value — this is the actual answer selected by the user
• Count: ritm_sys_id
• Filter: Add a condition on opts_element = your variable name to ensure only that specific select box question is being counted, otherwise the view will pull values from all variables across all catalog items using that variable set

The filter on opts_element is the key step most setups miss — without it the totals will be inflated.

If this response was helpful, please mark it as Helpful and if it resolves your question, mark it as Correct so others can find it easily.

[Juhi Poddar]

Hello @Biddaum 

Maybe one approach you could consider is creating a field on the Requested Item (sc_req_item) table to store the selected stream value. You can populate this field using a Flow Designer flow, Business Rule, or Catalog Item mapping when the request is submitted.

Once the value is stored as a regular field on the RITM record, you can easily create reports such as:

• Pie charts
• Bar charts
• Trend reports

and use Group By on the stream field to display counts for each stream.

Hope this helps!

Thank You!

Juhi Poddar

[Biddaum]

@Juhi Poddar  I understand that would make life simple with this, but this is for a somewhat small subset of all catalog items that are being created and I don't want to add custom fields if it can be avoided, just as I don't want to add a custom table for example to store the stream names.

[Tanushree Maiti]

Hi @Biddaum 

Create a Database view and create Report from that DB view.

Refer: 

Report on Item Variables in the Service Catalog with a Database View

Reporting on Catalog item variables

Also check 

Catalog variable dependency on referenced fields (Simple)

https://www.youtube.com/watch?v=xZIMd0AurkQ