Report on variable values with group by value

Biddaum · 3 weeks ago

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

Moedeb · 2 weeks ago

@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.

View solution in original post

Aanchal Agarwa1 · 3 weeks ago

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.

Biddaum · 2 weeks ago

@Aanchal Agarwa1

So I have the database view and when I open a new report using that view there is no option in the group by to choose any sort of value.

Can you be more specific as to what I need to choose where? For example the filter that you've suggested, I can find the question and the value to filter, but there is still no way I can find to actually group by any sort of value

Juhi Poddar · 3 weeks ago

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

Thank You!
Juhi Poddar

Biddaum · 3 weeks ago

@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.