Create Reports based on Multiple Datasets - Report Designer Interface


Description

A common question asked is whether it is possible to create and display a report 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 Report Designer User Interface. Refer to Create Reports based on Multiple Datasets - Report Builder Interface for similar steps using the original Report Builder interface

Procedure

Before beginning take a moment to plan your report, specifically the data that should be displayed, any necessary limiting criteria ad how this data should be rendered to the 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 as per a specific date range and display the results as a bar chart, showing both counts on the same chart.

A multi-table report

  1. Once you are ready to design the report, log in to the instance with an account having the necessary permissions to create reports as well as to the records and data you intend to report on.
  2. Using the Menu Navigator on the instance, browse to the location: Reports -> Create New.

The new report form will appear.  Give the report a descriptive name and select a source type (for this example we will be querying the tables directly, so we have selected a table).  

If selecting the table as the source type, select one of the tables you intend to query and display data from (i.e. Incident).

 Beginning creation of the report

              3. Click the Next button to advance to the Type tab of the Report Creation utility.

              4. On the Type tab, select the appropriate report display type you intend to have both datasets in the report adhere to.  In this example case, we will select Bar graph.  

               5. After selecting the report type, click the Next button to advance to the Configure tab.  

From this tab, the specifics of the data to be displayed can be selected and modified.  

For our example, we will select to Group by the Assignment Group field, and we will choose to Aggregate on Count.  

Clicking the Filter icon on the right pane will allow us to specify criteria to limit the data which will appear in the report (for our example we have specified to display tickets that are currently in Active status).

Configuring the report

                6. Click the Next button to advance to the last tab, Style.  

On this tab, various changes can be made to the report that modifies the way it displays for the end-user (such as including data labels, colors used, etc).  

                7. Once satisfied with the display of the report, click the Save button to save this first portion of the report.

At this point, we currently have a complete (albeit very simple) report that could be used, shared, and published if necessary.

However, we can also associate the report to additional data sets to allow the display of data from different tables and sources on the screen.  

Thus, to add an additional data set, continue with the following steps:

              1. Re-open for editing the report created above.

              2. Click the Show report structure icon in the upright right corner of the report record display.

Show report structure icon 

               3. The report structure section of the page will expand, showing the current structure of this report, including additional data sets used as well as drill-down reports associated with this report.

Report structure section

               4. Click the button titled Add dataset.  A new, blank dataset record form will appear.

New Dataset record

               5. Give the new Dataset record a name in the Dataset name field.  

Note: The name provided for this field will also appear in the legend for the final report.

               6. Select the appropriate Source type and specific Report source or Table for which this subset of data will pull its information.  

For this example, we will select Table as the Source Type and Problem as the Table to pull the data from.

Dataset record

               7. After filling in the appropriate data, click the Next button to advance to the Type tab of the Dataset record.  

Note: Each sub-set of data for a table must be of the same type as the parent report, therefore only one type will appear in this tab which is already pre-selected.  

               8. Click the Next button to advance to the next tab.

Type tab for the Dataset

                 9. The Configure tab of the new Dataset record should appear.  Select the appropriate Group by value for this report.  

For dataset records that are of a type of Bar chart, the Group by field of each associated data-set must contain the same value in the Group By field as the original report (for our example, Assignment Group).

                 10. Select any requested Stack By and Aggregation options you want for the Dataset.  For the example, we did not specify a Stack by column and chose to Aggregate on the same aggregation function as the parent report (in our example, Count).

                 11. To add filter criteria to this additional dataset, click the filter icon on the right pane, and use the Criteria Builder to select the necessary criteria for the dataset.  

For this example, we will select the same criteria as we did for the parent table (which is the usual best option in most cases).

Configuration tab of new Dataset record

                  12. Click the Next button to advance to the Style tab, in which options can be configured regarding the color and data labels on this secondary data set.  

                  13. Once satisfied with all the changes, click the Save dataset button. The screen will then return to the main report screen.  

                  14. Click the Run button to view the current state of the report. If everything appears as expected,

                  15. Click the Save button.  

You have now created a multi-data set report.  

Additional datasets (up to a maximum of 5) can be added to this same report.

Note: Before saving the report, you will probably want to return to the Data tab of the main report and provide a name for the primary Dataset as associated with this report.  

This name is what will appear in the Legend as associated with the data pertaining directly to the query for the table on the main report.

A completed Multi-Dataset report

Additional Information

There are several restrictions that must be kept in mind when creating multi-dataset reports.

  1. Up to a maximum of 5 additional datasets can be added to any particular report.  Keep in mind that each additional dataset will require additional processing and querying of the database, so if a particular report is experiencing performance issues, it could be due to the fact the report has multiple data sets associated.
  2. All datasets associated with a parent report must be of the same Type (i.e. bar chart, donut, pie, etc) as the parent report.
  3. For multiple datasets associated with a time series chart, all additional data sets must have the same setting in the Per field as the parent report.
  4. For multiple datasets on a Bar or Horizontal Bar chart, all associated datasets must have the same Group By value.
  5. The Show Legends option is always, by default, displayed on a report with multiple datasets, even if the parent report has this option unselected.

Refer to Add an additional dataset to a report for similar procedures and example creation of a report that uses multiple datasets.