Microsoft SQL Server Reporting Services Overview

Microsoft has released Microsoft Dynamics GP 2010 with almost 200 SQL Server Reporting Services (“SSRS” or “SRS”) reports.  They are accessible from the home page as a dashboard consisting of customizable metrics (SRS reports) with drilldown capabilities.  They can also be deployed to a Microsoft SharePoint Services site where consumers outside of the Dynamics GP users can utilize these key performance indicators.  Microsoft Dynamics CRM relies on SRS reports as the key business intelligence tool.

Microsoft released SQL Reporting Services back in 2004 as a free download to SQL Server 2000 that allowed Web-based reporting using the SQL Server platform.  Report definitions are stored in two SQL Server databases, ReportServer and ReportServerTempDB, and displayed in Internet Explorer using Web services.  Given its name and storage technology, one common misnomer of SRS is that it can only report on data stored in SQL Server.  This is incorrect since SRS can report on virtually any data source, as long as an ODBC driver exists for the data to be output.  This includes, of course, SQL Server, but it also encompasses Microsoft Excel files, Microsoft Access databases, text files, Oracle, and just about any other data storage technology that exists.  If an ODBC driver exists, SRS can retrieve and display the data in textual format or with any number of included charts and graphs.

For anyone that has used the Access report writer tool, the SRS interface should appear similar.  In fact, reports written in Access can be imported into the SRS Integrated Development Environment (“IDE”) and converted to the SRS report definition language (“RDL”).  The data sources would need to be changed since the reports are being pulled out of the Access environment into SRS, but the general layout of the report, which many users say is one of the more difficult aspects of report writing, would remain.  Defining a data source in SRS follows the now-standard Microsoft method of encapsulating connection information by prompting the user for the ODBC driver and credential information.  The DSN attached to the ODBC driver must only exist on the server, thereby giving SRS and the related reports a minimal footprint on the client machines (more later).

SQL Reporting Services operates under three principles:  data sources, data sets, and presentation.  The most granular level of definition with an SRS report is the data source, which defines the location of requested data.  Data sets, on the other hand, are tied to data sources and define the data that will be pulled.  These are synonymous with queries.  The presentation or rendering of an SRS report pulls data from the data sets and lays it out on the report as tables, lists, and free-form formats.  Multiple data sets can be sorted and grouped on one report, allowing heterogeneous data to be displayed in a homogeneous fashion.  Adding to the power of SRS is the ability to have dynamic parameters—having one or more parameters define the possible values of one or more other parameters—displayed to the end user to drive the data retrieval.  Parameters themselves can be tied to data sets, giving the users lists of values for a given entry.  An example of this is a data set that pulls a list of customers from a data source and displays these as a drop-down list to the user.  We could then tie a dynamic parameter to this list such that selecting a customer will fill another parameter with a list of possible invoices for the selected customer.  Selecting from the list of invoices—a parameter tied to another data source—will populate a report displaying the invoice in a tabular format in a Web page, thereby displaying the power of SRS.

Reports written in SRS are delivered via two Internet Information Services (“IIS”) sites:  ReportServer and Report Manager.  The ReportServer site is a collection of Web services that can be called from applications to render reports on the Web and in a number of other flat formats, such as PDF files and Excel files.  (Microsoft SQL Server 2008 also allows reports to be rendered as Word documents.)  The Report Manager site is the out-of-the box Web site containing all deployed reports, organized as a folder hierarchy with a full security model.  User would browse to an internal site with a name similar to http://SRSServer/Reports, and this site would display the data sources, top-level folders, and any reports deployed (published) to the root site.  Navigating through SRS is identical to navigating Web sites, which makes for a nominal learning curve for end users.  Further, since all reports are deployed to a centralized server, there is no need to update client machines whenever a data source or report definition changes.  The only component downloaded to the clients is an ActiveX applet that allows local printing and previewing of the SRS reports.  This feature was added in Service Pack 2 of the initial release of SRS and has been maintained in all subsequent versions.  The SRS site can also be made into a domain-enabled extranet to allow users outside the corporate firewall to view and print the deployed reports.

Developing SRS reports can be tricky for folks without a report design or development background since it uses a variation of the Microsoft Visual Studio IDE called Business Intelligence Development Studio, colloquially known as “BIDS.”  However, with a bit of training, it is not difficult to learn to use the SRS tools.  Creating a new report invokes the New Report Wizard, which takes the user through the sequence of required steps, including the creation and definition of data sources and data sets, to create a new SRS report.  The report can then be modified and tested from within the development environment to make certain data is retrieved, sorted, grouped, and displayed properly.  To publish the reports to the reporting server, the server configuration needs to be entered as part of a setup option and then the report can be deployed via the Deploy context menu option.  This activity will deploy reports to the configured location for the current environment specification, whether it is Production, Debug, or Local.  These options allow different SRS servers to be set up for development (Local), testing/QA (Debug), and production (Production) scenarios.  The environment specifications can be changed and new ones can be added to ensure that in-progress SRS reports do not accidentally overwrite production SRS reports.  However, since SRS is tied to the Visual Studio IDE, source code control can be wrapped around reporting projects, allowing current versions to be rolled back to previous versions within the IDE in the event of an accidental overwrite.

Finally, SRS is much more than a simple reporting engine.  Programmatic logic can be incorporated into reports so that launching a report will invoke other activities.  An example of this is to update a “times printed” column in a SQL database every time a report is printed.  By nature, SRS is an extensible technology via .NET assemblies or SQL Server stored procedures.  Given this extensibility, entire applications can be written solely using SRS and deployed to the Report Manager Web site to drive business processes, such as automatically running a data migration/integration from one SQL Server to another SQL Server.  E-mail can be sent automatically whenever reports are printed and, since SRS is a Web application, hyperlinks can be included in the published reports to make the reports truly dynamic and data driven.  Additionally, sub-reports can be added to published reports and all of these components working in concert provide a rich reporting and BI experience.

Crestwood has been working with SQL Reporting Services since it was released in 2004 and has written hundreds of reports for Dynamics GP and CRM.  Please contact us at sales@crestwood.com for more information about using this tool.

Written by William Schroeder, Practice Manager, at your Chicago, IL area Gold Certified Microsoft Dynamics partner.

Microsoft SQL Server Reporting Services Overview is a post from: ERP Software Blog

Related Posts

1. Crystal Reports vs SQL Server Reporting Services: Clash of the Microsoft Dynamics GP Reporting Titans
2. FAQ: Integration of SQL Server Reporting Services with Microsoft Dynamics GP 2010
3. Microsoft Dynamics GP Development Environment Overview