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

[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

[Biddaum]

@Tanushree Maiti whilst that information is helpful, it still doesn't allow me to "group by" the value, which is the ultimate goal here.

[Tanushree Maiti]

Hi @Biddaum 

Check this article:

Add custom choices in "Max number of groups"

https://www.servicenow.com/community/platform-analytics-forum/in-a-pie-chart-report-how-to-group-sec...

[Moedeb · Accepted solution]

@Biddaum I know you said that you don't want to add a new field to the sc_req_item table, but you could consider adding a new value filed to the sc_item_option table (which is where the questions and answers "values" are stored).

If you create a new string field on that table and limit the string size to ~100 then it will be indexable and can be used for grouping in your report.
Call it something like Display name: Value (Reportable) and Column name: u_value_reportable and type string, max length 99

Then create a business rule:

Name: Variable value reportable

Table: sc_item_option

Advanced = true

When to run: Before / update

Note: If you only want to record for this one question type, then add a filter condition like "question is 'your question name'

if you want it to always run don't have a filter and maybe change the when to: before/insert & update

Then add this script:

(function executeRule(current, previous /*null when async*/) {

    current.u_value_reportable = current.value;

})(current, previous);

Then create your report

Source type: Table

Table: sc_item_option

Make the type Pie as you indicated you want (will be the same for other types)

Configure: Group by - select the new field you created "Value (Reportable)

Aggregation: count

Add a filter to show value (reportable) not empty and run your report, that should be it unless you want to add other filter options such as the question you want to see.

For this option you are creating a new field on an OOTB table, but it is only one field and it will continue to work for other values if you don't add conditions to your business rule.

You also don't need to create a database view this way although you could if you wanted to, the new value (Reportable) field would also show on the database view.

Please mark correct if this has helped you.