by Grant Fritchey

Microsoft introduced its own branded reporting engine, SQL Server Reporting Services (SSRS), in 2004 as an add-on to SQL Server 2000. Since that time, each new release of SQL Server has seen a new release of Reporting Services right alongside. Over that same period, Microsoft has imbedded Reporting Services and Reporting Services reports within a large range of the products it sells and supports. Although many companies have embraced and implemented Reporting Services as their enterprise-wide report management system, some resistance to implement SSRS remains.

The goal of this tip is two-fold: First, it will show that although an official implementation of SSRS may not have occurred within your enterprise, if you are using any of a wide range of Microsoft products, you already have Reporting Services working for you. Second, the tip will show that expanding the use of SSRS within your enterprise through the products that already consume and produce SSRS reports and through the Reporting Services authoring and management interfaces is easily accomplished. You might already have SQL Server Reporting Services at work within your business, so it just makes sense to take advantage of the power and flexibility that it offers for you at no additional cost.

The Silent Service

The nickname for the submarine service within the US Navy is “The Silent Service.” So-called because submarines are supposed to hide out in the ocean, sneaking around until needed. It’s not difficult to make the case for Reporting Services being the Silent Service of the Microsoft suite of tools. Only SSRS hides in plain sight. It shouldn’t come as a surprise to find that all the reports generated within SQL Server are SSRS reports. After all, the two “S’s” in the name of SSRS represent SQL Server. What frequently surprises people are all the other places where Reporting Services reports are to be found. The following list represents a sample of the Microsoft programs where SSRS can be found, quietly at work:

• Dynamics CRM
• Team Foundation Server
• System Center Operations Manager
• SharePoint
• Visual Studio
• Identity Lifecycle Management
• Threat Management Gateway
• Exchange Hosted Services
• System Center Data Protection

If you are working with any of these products within your IT infrastructure, you’re using Reporting Services whether you were aware of it or not. An overview of some of these products will show the depth and breadth that SSRS has penetrated within your organization, silently.

Dynamics CRM

Microsoft’s Dynamics CRM is its enterprise-level Customer Relationship Management tool. CRM is targeted to support sales, services, and marketing, and CRM runs on top of SQL Server. The full SSRS management server is required as a part of the CRM install. Thus, not only are detailed and interactive reports available to the CRM users but also the management of these reports is enabled for the CRM administrators and developers. Among other things, having the SSRS management interface supplies the ability to pre-generate canned reports through a scheduling process. Reports can be emailed to CRM users through the management interface. All the reports can be customized and controlled through the Report Builder, a simplified report building and editing tool that will be addressed in detail later. Most users of the CRM system are not aware of the fact that the dashboards and charts they’re looking at have been generated through SSRS.

CRM is a fully customizable interface. As the interface is customized and developed through the tools exposed from Microsoft, it will become necessary to build and expand on the reports for the users of the system. These reports will have to be developed and deployed through the SSRS toolset.

Team Foundation Server

The Team Foundation Server (TFS) is Microsoft’s source control system that replaced, and expanded on, Visual SourceSafe. TFS runs on top of SQL Server, like most major Microsoft products. It also relies on SSRS reports. TFS does not run the SSRS server, but it uses the ability of SSRS reports to be embedded in Web pages or Windows applications to display information about the files managed through source control in TFS. TFS introduced Team Management as well as code management to Microsoft managed software development. The reports and charts that show unfixed bugs or other information are all running off of .RDL files. RDL represents Report Definition Language, an XML implementation that describes how reports are supposed to be rendered by the embedded SSRS report viewer.

TFS, like the rest of Visual Studio, is fully extensible. This means that new functionality can be created using an embedded Application Program Interface (API). As new functionality is added, additional reports may need to be developed. Adding reports through TFS extensibility would require using one of the methods of creating new RDL files exposed through the Report Builder, mentioned earlier, or through the Report Designer, a fully programmable report design interface that is built into Visual Studio—more details on this later.

System Center Operations Manager

Microsoft’s enterprise monitoring and alerting software is System Center Operations Manager (OM). The OM install requires a SQL Server instance and a Reporting Services server. The reports used by OM are both embedded directly into the software and available through the Reporting Services server. The full functionality of the server can be taken advantage of. Reports that export data to Excel or Adobe Reader can be set up to automatically run as part of an enterprise monitoring scheme.

The reports can be generated from either reporting tool, Report Builder or Report Designer. The data is stored in SQL Server directly, so it’s readily available for use in the reports.

SharePoint

Microsoft’s business collaboration platform, SharePoint, has a very large number of hooks into Reporting Services. Since so many companies are beginning to take advantage of SharePoint to manage internal and external web sites, document versioning, discussion groups, etc., it becomes a very natural fit to begin to expand the use of Reporting Services within SharePoint. Integration with SharePoint allows you to report on the data managed by SharePoint itself and offers up other enterprise information, formatted and maintained by Reporting Services, but displayed and managed through SharePoint itself.

SharePoint works through a combination of application servers displaying various web pages through web services. Integration with Reporting Services is done through a special web service specifically for Reporting Services. This service comes with SharePoint at no additional cost and can be put to work immediately. Integrating Reporting Services with SharePoint allows you take advantage of the strengths of both tools, using the security and management process of SharePoint while offering up the Reports, Models and defined data sources of Reporting Services. Even more importantly, the reports used by Reporting Services, when integrated with SharePoint, are stored within the SharePoint document management system. Documents managed by SharePoint can be searched using the SharePoint document indexing software, basically making the reports in your system an integrated part of the enterprise information and data, not simply a consumer of that data.

Reports themselves are created in the normal fashion using either of the tools available, Report Builder or Report Designer. The difference is that the Reporting Services server itself has to be set up to work exclusively with SharePoint. Management and display of reports is no longer done only by SSRS, but is a tightly coupled part of the SharePoint architecture.

The tight coupling between these two tool sets makes each one more powerful than it would be without the other. The abilities offered by Reporting Services reports, drill-down, interactive dashboards, maps, and other things, are all managed and displayed through the SharePoint web services platform. The combined abilities are one of the biggest reasons to understand Reporting Services and begin to expand its use within your organization.

Summary

The preceding provides just a small sampling of the various software products from Microsoft that might be at work within your organization. If you are working with these tools, and many others, you are already working with Reporting Services. With this software already in your enterprise, there is every reason to find more ways to put it to work, offering up the data available. Since the integration is provided for you, and no additional programming or configuration is required, all that is left is learning some of the basics of how to use Reporting Services.

About the Author

Grant Fritchey, Microsoft SQL Server MVP, works for an industry-leading engineering and insurance company as a principal DBA. He's done development of large-scale applications in languages such as Visual Basic, C#, and Java, and has worked with SQL Server since version 6.0. Grant spends a lot of time involved in the SQL Server community, including speaking and blogging, and he is an active participant in the SQLServerCentral.com forums. He is the author of SQL Server Execution Plans (Simple Talk Publishing, 2008) and SQL Server Query Performance Tuning Distilled (Apress, 2008).