Create Reports based on Multiple Datasets - Report Builder Interface - Support and Troubleshooting

Create Reports based on Multiple Datasets - Report Builder Interface Issue  Description A commonly asked question is whether it is possible to create and display reports based on multiple datasets.

The answer to this question is definitely yes, and this article will detail a simple example of creating a report based on multiple-datasets.

This article is for use with the classic Report Builder User Interface. However, please see article https://support.servicenow.com/kb_view.do?sysparm_article=KB0718493 for similar steps using the newer Report Designer interface.

Procedure Before beginning it can be very helpful to spend a moment planning your report, specifically the data that should be displayed, any necessary limiting criteria, and how this data should be rendered to end users.

In the example used in this article, we will be creating a very basic report displaying the number of Incidents and Problems which are assigned to various assignment groups in the system. We will filter this report on a specific criteria and display the results in a bar chart, showing the counts for both the Problem records and the Incident records on the same chart.

Once you are ready to begin the actual design of the report on the instance, login to that instance with an account having necessary permissions to create reports as well as to the records and data you intend to base the report on.

Using the Menu Navigator on the instance, browse to the location: Reports -> Create New .

A form will appear for creating a new report Give the report a descriptive name and, in the two part data field first select the source type for the report (for this example we will be querying the tables directly, so we have selected table) and then in the second part of the field select one of the Tables or Report Data Sources you intend to report on with this multi-dataset report (for this example we will select Incident).

Next, select the report type you want to use for this multi-dataset report. For this example we will select Bar chart.

Note that only certain report types can support multiple datasets, currently this is Bar, Horizontal Bar, Line, Column, Area, and Spline type reports.

Select any Group By options and Stacked By options for the report. Four our example report we will opt to Group By Assignment group but not select a Stacked By option.

If you would prefer to have the grid displayed in the report display, the Show Grid checkbox can be selected.

Select an Aggregation type (for this example we will use Count), as well as the Number of Groups to display in the report (the default setting is 12).

Add any necessary filter conditions for the report using the Condition builder at the lower half of the Report Builder interface. In this example we will filter the report to show Active tickets.

Clicking the cog icon to the right of the Type field will allow further customization of the repot type (i.e. chart colors, display of labels, etc).

Once satisfied with the conditions and other configurations for the report, click the Save button to save the report.

At this point we currently have a complete (albeit simple) report that could be used, shared and published if necessary. However, we can also associate the report to additional data sets to allow display of data from differing tables and report sources. Thus, to add an additional data set, continue with the following steps:

Reopen the report created in the above steps.

Once the report has been opened, click the pulldown menu to the right of the Save button.

From this menu, select the option Multiple dataset .

A sub-window will then open, titled Add extra data series to chart .

Give this new Data series a distinguishing Series Name . As with the original report, we will specify the Data Source Type and the Table name or Report Source name for this subset of data. For our example we have selected a data source type of Table and the Problem table as the table source for this secondary data source.

The report Type must be the same as the parent report and as such, this type is pre-populated as the only type selectable (for our example this will be Bar chart).

Select appropriate Group By and Stacked By fields for this additional dataset. There are certain restrictions on Grouping and Stacking, which are described further below. For this example, we are selecting the same values as selected on the parent report (Group By Assignment Group and no Stacked By selection).

Select an Aggregation type for this sub dataset. For this example, we have selected Count as per the original report.

Other options controlling the display of this additional dataset can also be configured from this form, such as the chart colors, label displays and the axis to use for display of this additional dataset.

Lastly, a criteria can be specified for this subset of data using the Condition Builder at the bottom of the form. Similar to the parent report, we will select to limit to Active records.

Once satisfied with the configuration of this sub-set of data, click the Save button.

The sub-form will reload. If additional data sets also need to be added to this report, click the New Series tab and fill out the necessary data for the next data series. Repeat for as many subsets are necessary. However, note that there is a default system configured limit on the instance of 5 additional data sets for any report.

When all the necessary additional data sets have been added, click the X icon in the upper right corner of the "Add extra data series to the chart" sub-window to close that window. Click the Save button to save and reload the finished report.

The report will now display showing all the configured data sets on the same chart. The report can be shared and published as any other report.

Applicable Versions This article is specific to creating a multiple dataset report using the original, classic Report Builder user interface. A different article is also published which will show similar steps using the newer Report Designer interface.

Additional Information There are a few restrictions and limitations that should be noted when working with reports that use multiple datasets, as follows:

All additional datasets associated with the report must be of the same chart type which will also be the same chart type as the parent report record. If the chart type is a Bar or Horizontal Bar type chart, all the datasets must use the same Group By setting (if one is selected) which again must be the same as set for the parent report. If the chart is a time series chart, each dataset in the series (including the parent) must use the same Per field setting. Reports which contain multiple datasets will always display a Legend, regardless of whether the Show Legend option has been disabled in the parent report record. If the instance is specifically configured to disallow usage of the classic report builder interface, it will not be able to be accessed for creation or editing of reports. This can be controlled through configuration of the glide.ui.report.old_report_builder system property. If that property does not exist or the property does exist and has a value of true , the classic Report Builder User Interface can still be used on that instance.