by Grant Fritchey

As has been mentioned earlier, Reporting Services breaks down into three basic applications, the Reporting Services server, which manages the reports, shared data sources, security and other things, the Report Builder, a lightweight web interface for creating reports, and the Report Designer, a plug-in for Visual Studio that provides a rich and thorough interface for creating reports and models for creating other reports. These tools work together extremely well and are your principal means of creating and maintaining reports over time.

Report Server

The Report Server does a lot more than simply manage reports. Access to the Report Server is through a web interface. The interface allows for management of the server itself, reports and other information and functionality around reports. The Report Server allows you to configure security for the reports themselves, limiting access to only the users or groups that you wish, and to the underlying data.

Reporting Services, through the Report Server, provides a mechanism for creating report snapshots. A snapshot is basically a method of retaining the information within a report at a point in time, like taking a picture of it. Snapshots are saved instead of regenerating the data displayed by the report each time the report is accessed. This is useful if the data represented in the report takes a long time to gather and present. You can schedule a snapshot to be created during off-hours and then use the snapshot during normal business hours to cut down on the amount of time your business users wait for the report to display. Snapshots are also useful for creating historical views of the data. Snapshots can be stored and compared to each other.

When you create a report, the report has to have a data source, a place to go and get the data. This can be almost anything from an Excel file to a DB2 database. A mechanism is provided through the Report Server to create and maintain shared data sources. These data sources can be consumed by any report that has access to them. Security can be set on the shared data source, as well as on the underlying data, so that only users or programmers that you wish to have access can see or use the data source. Connections and passwords can be stored with these data sources, or not, as defined by the business needs.

A database is used by the Report Server to aid in maintaining the information about the reports. This database also acts as a repository of access, giving you the ability to know which reports are used, when, by what user or application. For an enterprise system that may have to meet regulatory auditing requirements, this becomes absolutely necessary.

Except when using a report viewer web interface or windows interface for use with individual report files, all reports are generated and displayed by the Report Server. This is, of course, not true in the event of integration with SharePoint.

Report Builder

The Report Builder is a lightweight web interface that allows for creating simple reports and performing ad hoc reporting. The Report Builder can open a Reporting Services report, regardless of where it was created, and these reports can be edited. The Report Builder works within an interface that is similar to that found in Microsoft Office. The interface is an attempt to make it easier for your average business user to be able to create or edit reports as needed. The full functionality of the Report Designer, discussed below, is not available with the Report Builder.

A set of wizards are available that can walk the user through the creation of fairly sophisticated reports that include interactive graphics such as maps or gauge. The wizards make Report Builder a tool that is easy to use, which makes support and training on the tool simple and quick. Wizards help to reduce the cost of implementing expanded use of Reporting Services within the enterprise.

The Report Builder works best when a set of Models have been created using the Report Designer. Models are an easy way for the user to access data without having to know any T-SQL language commands. The T-SQL required to access the data is embedded within the model definition. All the user of Report Builder needs to do is drag and drop the information from the model into their report. Again, the emphasis is on making this as simple as possible in order to facilitate use.

Report Designer

To realize the full capabilities of Reporting Services reports, Report Designer has to be installed and put to use. The Report Designer works within the Visual Studio environment and is able to take advantage of the full range of extensibility that Visual Studio offers. The familiarity of Visual Studio for most people who develop with the Microsoft suite of tools helps to make learning how to use the Report Designer somewhat easier.

The full suite of possibilities for creation of reports, models and shared data sources are exposed through the Report Designer. The possibilities within the tool are vast and considerable time will have to be spent exploring all these capabilities. However, wizards are available for the most important functions, which will enable even a beginner to start designing and deploying reports and models right away.

The functionality is broken down by the objects that can be created and manipulated in the Report Designer. A solution can contain either a Report Server project, which is exactly what it sounds like, a project for creating and maintaining the objects that are going to live on a Report Server, or a Report Model project, which is a project for creating the models that allow users to avoid having to learn T-SQL to access data for reporting.

Inside a Report Server Project the three basic objects that can be created are Shared Data Sources, Shared Datasets and Reports. The shared data sources were described earlier. They’re simply the means of connecting to the database server or file, or whichever of the various possible sources of data that can be reached by reports. Shared data sources are dependent on the drivers installed on the system, but can use native connections or ODBC. Once a shared data source is created, any number of reports can use it. A shared dataset is similar to a snapshot. It’s a prepared data set that may or may not be on a schedule refresh cycle that can be used to create reports. A dataset, like a snapshot, helps speed access to the report because the information inside only has to be accessed, not retrieved from the original data source. Finally of course are the reports.

Reports can be built through the use of a wizard or by directly manipulating the various tools available in the Toolbox. Some of the things from the Toolbox that can be put on a report, apart from the basic grid of information most people expect, are matrixes, pictures, charts, maps, and gauges. Each of these can be manipulated within the context of the report allowing for a very wide range of behavior and formatting, making for a very rich environment.

With a Report Model project, the types of objects generated differ.  There are still Data Sources, which function more or less as they do for Report Server projects. There are also Data Source Views and Report Models that can be created. A Data Source View describes the tables and their relationships within one of the Data Sources. This is vital information to allow Reporting Services to build T-SQL statements for the users of Models. A report model consists of descriptions about the data that will be used to create reports, metadata. There can be a one to one relationship between the fields within a model and the columns of a table, but the relationships can also be quite complex. It really depends on what’s needed to present the data in the proper way to the users.

All of this information comes together to provide a mechanism for publishing reports and report models for use within the software which will display them.

Display Reports

Obviously, as discussed at length, reports can be displayed through the Reporting Services server. As was noted, it’s also possible to display reports through the SharePoint Report Server utility. But it is possible to completely avoid using the Report Server at all. Two tools are provided for displaying reports. One is an ASP.NET control which allows you to embed report files (stored with an .RDL extension) in web pages. The other is a full Windows Forms control that puts the power of reports into standard Windows applications.

With all these different means of displaying reports, one or more can be integrated into almost any tool working within a business. As you expand your use of reports within tools already designed to display them, you may find that you’ll want to expand the use of reports into other applications. These mechanisms supply you with that ability.

Summary

All the tools supplied by Microsoft, the Report Server, Report Builder and Report Designer, as well as the display tools, come together to work with your applications to provide you with everything you need to create new reports or modify existing ones. By using Report Builder, it becomes extremely easy to implement Reporting Services reports for your business users. Report Designer makes growing a group of strong report designers who can supply almost anything for your business needs possible.

Conclusion

The goal of this paper was to get across two points, that SQL Server Reporting Services was probably already in use within your enterprise, and expanding use of SSRS is easy to do because of the tools supplied by Microsoft. When you consider the wide range of products that Microsoft has embedded Reporting Services reports within, most businesses are probably running some of the software that was listed above. It just makes sense to take advantage of the built-in, ready to run, functionality offered by Reporting Services reports. If you have SharePoint providing collaboration for your workers, giving them access to powerful dashboards and interactive displays of data, without programming, can improve that collaboration.

When you make the decision to expand the use of Reporting Services, you can be sure that the execution of the plan will be possible because of the tools provided by Microsoft. The Report Server will manage the reports. Business users will be able to easily access reports by using Report Models within the Report Builder software. Your developers and report writers will be able to create complex reports and get them in the hands of your users with a high degree of functionality and flexibility thanks to the Report Designer.

Are there difficulties and problems expanding the use of Reporting Services? Of course there are. Nothing comes for free. But the ease of implementation is very high. Since SQL Server Reporting Services comes embedded in so many Microsoft applications, the monetary cost is effectively free. All of this, taken together, makes Reporting Services a strong service that should get expanded use in most enterprises.

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).