Reporting Services has the following features, that is impossible to achieve in TOPICA Reports XML format, and/or would require extensive programming to implement in Custom Reports ASPX format.

• WISYWIG design tools (Report Designer in Business Intelligence Developer Studio (which is actually Visual Studio in disguise) - and Report Builder, that is accessible from Report Manager (web user interface).

• Possibility to "nest" datasets, e.g. construct "master/detail" reports.

• Cross tabulation, multiple graph types, etc.

• Extensive export capabilites, including PDF, TIFF, XML, Excel, and CSV formats.

• The possibility for the end user to interactively develop "ad hoc" reports (using Report Builder).

• Scheduling (reports may be run automatically outside office hours).

• Subscription (generated reports may be sent by mail to subscribers automatically).

This document will not contain information on how to develop reports in Reporting Services. The reader is referred to the extensive documentation that comes with the product. Also, a lot of information regarding Reporting Services (e.g. "how to" YouTube videos) may be found on the internet, and in numerous books. There is also a vast selection of courses available (online and class room).

This document will only describe the issuses necessary to integrate the reports into a TOPICA application.

Reporting Services reports might be run by just redirecting to a URL referring to the requested report on the report server. Reporting Services contains a web interface (the Report Manager application), that lets end-users start reports, including entering parameters. Why not rely on these "out of the box" mechanisms?

The reason for using the TOPICA Report Format XML files to start Reporting Services reports are as follows:

• Security in Reporting Services is built on Windows authentication. This causes a number of problems:

  • If the server hosting Reporting Services is in same domain as the web server running the TOPICA framework, the same Windows user might be used.

    If the servers are NOT in the same domain (which is typcialy hte case in production environments), local Windows user(s) must be created on the Reporting Services server.

  • The web home running the TOPICA framework will typically run using one Windows user (= the "Application Pool user"). This Windows user obviously has nothing to do with the TOPICA user.

  • It is not possible to "forward" different logged in users to Reporting Services. So the standard security mechanism in Reporting Services cannot be used.

  • So, Reporting Services does not "know" anything about security in TOPICA. If the built-in Reporting Services facilites for entering parameters were used, this might compromise the security of the system. The end user might be able to specifiy parameters in such a way, that (s)he could read some data, (s)he should not have access to.

• The integration mechanism using "stubs" in the TOPICA Report Format makes it possible run Reporting Services without compromising the security requirements.

  The report designer (configurator) uses TOPICA Basic to set up parameters - based on the context in the TOPICA application.

Integration of Reporting Services reports into an application built with TOPICA is currently done like this:

• The report designer (configurator) declares variables in the Reporting Services report to handle the report context (and any parameters that the user may type in when starting a report) passed from the TOPICA application.

  It is the responsibility of the report designer (configurator) to construct the SQL queries (using the passed parameters) in such a way, that security is not compromised. For example - if the report is designed to run in context of an organizational unit, only data related to the organisational unit whose key is passed in context parameters may be displayed.

• The (configurator) creates a "stub" file, that is placed in the Configuration Folder Structure according to the context that should be passed to the report (see Report Context).

  This "stub file" must be an XML-file in the TOPICA Reports XML format - using a subset of the available elements (particularly the ReportReportingService element).

  The "stub" must define the same parameters as those declared into the Reporting Services report (1-1 correspondence).

  Important
  Count, type and names MUST match exactly - including "casing".

• Each "stub" appears as a separate report in the list of reports availiable in the given context.

  One Reporting Services report may be started by several different "stubs" (with different context parameters). So one Reporting Services report may look like several reports for end end user of the TOPICA application. Conversely, one "stub" may launch different Reporting Services reports (depending on the context and/or an input parameter).

  When the end user clicks the "stub", the framework displays the report start-up form, that may contain standard date interval selection and/or additional parameters to be entered.

  When the end user clicks the "OK" button in report start-up form (or if AutoSubmit=True), the framework passes context, user selected parameters, log-in information, and other environment settings from e.g. .config files to a report viewer control, that is used to render the Reporting Services report.

When designing Reporting Services reports, most of the time you will work in the tools that come with Reporting Services, most importantly Report Designer (= Visual Studio). Most of the testing takes place using the Preview feature in Report Designer .

When the report design is finished (including parameter definitions), the report is deployed, and it may be tested once again - this time in the Report Manager application.

Note that there is a bug in Reporting Services (both in Report Designer and Report Manager) regarding international (= non-US) data formats: month and year values entered in Report Designer and Report Manager is "swapped".

The final step in the test is to make sure, that the integration into your TOPICA application works. This is done using the standard end user method for Running Reports.