Running a Microsoft Reporting Services Report

Introduction

The ‘Run Microsoft Reporting Services’ is a format tool used to run a pre-designed report. It outputs the report in one of a range of formats which can be consumed by other tools in TaskCentre. The tool can consume a recordset or an XML document which may then be transformed within the step and used as parameters for generating a report.

Using TaskCentre reports can be run in one of two ways:

  • The ‘Run Reporting Services’ step can be used standalone and without the need for a preceding query step. An already completed report is run in to the tool, this way TaskCentre can be used to automatically run a single existing report that until now has had to be run manually.
    4
  • Alternatively, multiple reports can automatically be generated by using a TaskCentre database query to filter specific results required for the report, one report produced for each row returned by the query recordset. The same applies for when using an XML document.
    3

Example: A TaskCentre query is setup to find account numbers in a database which have exceeded their credit limit. It will locate all accounts which have gone over and by using parameters the account number will be directly mapped to a parameter field in the report. The report builder will use the account number and query the database to obtain more information on the account to complete the report. The number of accounts located by the TaskCentre query will dictate the number of reports generated.

The report generated by the step can automatically be delivered via email, saved to a location or published to form part of Web or Intranet content.

Configuring Run Microsoft Reporting Services

In its default state no global configuration is required, however, TaskCentre can be setup to allow the tool to connect to a reporting services server to access and run stored reports.

This is highly recommended as it is a very convenient management system for reports. It allows the storage of reports in an addressable format which means they can easily be accessed by TaskCentre. In addition, the report server also provides its own output of a report in a variety of formats.

The following link outlines how to setup this connection:

Microsoft Reporting Services – Configuring Report Server Mode

Using the tool

From the task browser under Format tools drag and drop the Run Microsoft Reporting Services tool into the task planner. This will automatically open the tool for configuration.

General Tab

Within the General tab you enter an appropriate name and description for the step.

If you want to generate multiple reports from a TaskCentre query or from an XML source, then a recordset or an XML output from another step will need to be selected. Using the Mapping tab, the data is mapped to parameters in the report and automatically inserted at task runtime.

Tick the box Use a recordset or XML output from another step to utilise the output from another step in the task.

Alternatively, if you do not require the use of task generated data then simply leave the tick box blank if an existing and complete report is to be run.
Capture1(1)

Report Tab – Selecting the report

The Report tab allows a report to be selected and run from one of two sources, Server mode and Local mode. The tab provides you with three options which specify where the report will be run from, a definition of each option is provided further down the page.

The report is loaded by clicking Select, Import or Browse. The buttons available will differ depending on which of the options are selected.

If you are using an existing report which is ready to be run, i.e. using it’s own data source, then once imported in to the tool no further action needs to be taken and the step can be closed by clicking OK. The report can now be automatically run based on a pre-defined schedule.

If you will be inserting data into the report from TaskCentre then you will need to proceed to the Mapping tab.

The 3 options available for selection are as follows:

‘Run Report on a Microsoft Report Server’

This option runs a report from a reporting server running SQL Server. Reporting services on this server can access data on the local server or on other servers (including non SQL Server data sources). The reports are held centrally on the reporting server which has some advantages when it comes to maintaining connections and security for data sources, i.e. a copy of the report does not need to exist on the TaskCentre server.

NOTE: To use the Report Server Mode the report server URL must have previously been configured. A report server and a report held on the server can then be selected.

‘Run Imported Report in Local Mode’

This option imports a report as a copy into the TaskCentre store and will be used to run the report.

‘Run Report Held Centrally in Local Mode’

If the report files are held centrally then a copy is not made in the TaskCentre store. In this case the report can either be taken from a network location or from a web server.

When running in local mode the report is rendered by a locally installed Microsoft component. This component is delivered with this tool and must be deployed on the TaskCentre server using the TaskCentre Configurator.

In local mode the reporting services tool makes use of the Microsoft Report Viewer redistributable 2012. This can process reports created with Microsoft Report Builder 1, 2 or 3.

Select from Report Server

Capture5

Select from Local Mode

Note: This selection gives you the option to edit a report using MS SQL Server Report Builder, this will only work if the report builder is installed on the machine. Any amendments can be made and saved without the need to import the report again.

Mapping Tab – Parameters

The Mapping tab is used to populate the report with data obtained from either a database query recordset or an XML document. Links are created between the source data of the step and the parameters in the report. These links define where data will be inserted. It is necessary to insert a function which will enable multiple reports to be generated, this will be covered further down in this section.

The source data, i.e. recordset or XML, is displayed on the left in the Data Input pane and the available parameters in the report are displayed on the right in the Tool Input pane. The Transformation Mappings pane in the middle will display the visual links between the two.

Note: A parameter must first be created in the report before it is imported into the tool.

Capture7

Creating a Parameter Mapping

To create a parameter mapping you simply drag and drop a data field from the Data Input pane on the left, to a parameter in the Tool Input pane on the right

Capture8

This will create a link between the two and will be shown in the middle Transformation Mappings pane.

15

A link has now been created and data obtained by a query has now been mapped to the parameter field in the report.

Functions – Generating Multiple Reports

If the task was to be run at this stage it would only produce one report, the tool will process the first row and then stop. If the recordset has produced multiple rows then a ‘Function’ will need to be created to tell the tool to generate a report for each row. This function is known as ‘Simple Looping’.

  • To create a simple loop function, from the Data Input pane on the left you select the folder above the data fields and drag it across to the folder above the parameter fields in Tool Input pane on the right.

16

A link has now been created between the two folders and a ‘looping’ function has been placed in between them. Now, after a row has been processed and a report created, the tool will now complete the procedure again until all rows have been processed.

Note: If you want to use one of the recordset columns in a subsequent Send Email (SMTP) step, as the recipient for example, then this will need to be passed into the report as a parameter. Parameters can be created in the report but not actually used for the report.

This completes the Run Microsoft Reporting Services configuration.

To read about tasks which make use of this tool see:

Learn about: Building a task that sends a Microsoft Reporting Services Report by email