Use service catalog variables in a report

In reports on service catalog data, stack and group by variables, use variables as columns in list reports, and as columns and rows in multilevel pivot tables.

Before you begin

Role required: itil, report_user, report_global for global reports, or report_group for group reports.

Users with the report_admin role can report on service catalog variables. Add the report_admin role to the ACLs for the Options [sc_item_option], Variable Ownership [sc_item_option_mtom], and Variables [item_option_new] tables.

Note:

• The report you want to use the variable for must be based on the Requested Items table [sc_req_item] or the Catalog Task table [sc_task]. Using other types of variables causes an error when generating the report.
• The Oracle Database CLOB field does not have the functionality to retrieve the variables data in reports. For a column string field of less than 4000 bytes, you can change the column type to varchar2. To compare the CLOB columns of a smaller size, use the to_char() function. For a larger size CLOB, you must get a substring for comparison using the dbms_lob.substr functions.

About this task

For primary Group by and Stack by, these steps are intuitive.

Figure 1. Variable use in Group by and Stack by fields

Note:

List, Box, Trendbox, and Pivot reports cannot use service catalog variables as a primary or secondary Group by. Single Score, Calendar, Control, and Map reports do not support Group by on any fields. List reports can use service catalog variables as columns.

Use a variable as an extra Group by, as a column in a list report, or as a column or row in a multilevel pivot table.

Procedure

1. Navigate to All > Reports > View / Create and open the report to add the variable to.
2. Do one of the following.

   Option	Description
   Add as a column in a list report	On the Configure tab, move Variables+ to the Selected column.
   Add as an extra Group by	On the Configure tab, click Additional group by, then move Variables+ at the bottom of the Selected of the Additional group by list.
   Add as a column or row in a multilevel pivot table	Click Select columns or Select rows, then select Variables+ at the bottom of the list. Note: If you first create the report with the variable as the main Group By first, it will always behave as an inner join. If you first create the report with a regular field for primary grouping, and a secondary grouping on the variable, it will work as an outer join.

3. Click the structure icon () and then the plus sign that appears.
   A list of service catalog items appears.
4. Select a catalog item with the variable.
   The variables for that item appear in the Available list.
5. Move the variable that you want to use to the Selected column.
6. Save the report.