SQL Server Reporting Services – Service Secrets

Almost all my recommendations addressed to developers of SQL Server, Visual Studio and VB.NET are based on readers’ suggestions. Your ideas have made Reporting Services (SSRS) more effective.

At the heart of almost all my recommendations addressed to SQL Server, Visual Studio and VB developers. NET, are based on the readers’ suggestions. Your ideas have made Reporting Services (SSRS) more effective. Microsoft has retained almost all of the commendable tools and added a few new ones. At the same time, we would like to see some more features added. Let’s take a look at some of the improved tools that should make it easier for an administrator to do their job, as well as some of the features that are not well thought out that make it difficult for them to do so.

Virtual Directory

SSRS can now save virtual directories without having to use Microsoft IIS. Without IIS, reporting services are faster than before. To access reports over SSL, the administrator must use a certificate as before. Running a production report without SSL is like sending your phone number over a local radio station. When using SSL, all data exchanged between the SSRS and the browser is transmitted over the network in the form of encrypted packets. And no one can read the reports or the confidential data contained in them using a packet analyzer.

SSRS developers also changed the procedure for naming virtual directories, but these changes are only valid if SQL Server 2008 is installed as a named instance. In the ReportServer or Reports URLs of previous versions, the name of the SQL Server instance was joined with a $ symbol, such as:

http://<your server>/Report$SS2K5

In SSRS 2008, Microsoft replaced the $ symbol with an underscore character (_). To deploy the Microsoft Visual Studio (VS) 2008 BI project, you must change the TargetServer URL to look like this:

http://<your server>/ReportServer_SS2K8

where SS2K8 is the name of a particular SQL Server instance.

Reporting Services Configuration Manager

The tool has undergone little refinement and its capabilities have been improved. Reporting Services Configuration Manager (RSCM) is still run from the SQL Server Configuration Tools menu. Keep in mind that the manager makes configuration changes to a particular SSRS instance, which is often installed on a server in the same room.

If you install reporting services later than SQL Server 2000, this program allows you to make sure that everything is connected correctly, and helps you configure SSL, set up e-mail links, and most importantly, install and reserve encryption keys. It is quite difficult to access reports without these keys. Screen 1 shows a link to the report manager in RSCM. Here, the SSL port communicates with the certificate that you created.

Exporting Reports

SSRS services have not yet implemented a simple way to export reports. A spokesperson for the developers informed me that they plan to deal with the issue of exporting reports (although not in the near future) and that this feature may appear in a future version. For now, you can use a free utility that provides a solution to this problem (see the “Free Report Exporter SSRS” box).

Enabling My Reports

If you want to make report manager available to users, you can activate My Reports so that they can maintain a report directory based on their domain names used for registration in the system. For example, after I enabled My Reports, Reporting Services has created a new directory tree for my personal reports that I can use to search for reports of interest to me. The question is how do I enable this feature. The documentation on this is fragmentary, but enabling it is not that difficult if you know how the secret key exchange communication procedure works.

Run SQL Server Management Studio, but do not connect to the Database Engine, and in the original dialog box Connect to Server select Reporting Services (you can do this if the service is already running). To activate My Reports functionality, right-click on the base connection and select Properties. The dialog box shown on screen 1 will appear.

You can now specify a role that applies to each of the My Reports directories created on that server. When Windows users open the Report Manager URL, they will see the new path created in the Reporting Services directory for their domain name.

Securing your reports is a vital task for the user, no matter who is authorized to view the reports. One way to create Reporting Services roles to which an administrator can assign specific rights is to use Reporting Services Object Explorer. This prevents users from accessing reports that they are not allowed to view.

Working with the new Report Designer

Many report designers are at the disposal of report developers. In the “Microsoft Report Designer Versions” box, I tried to systematize the product versions and their features.

The new SSRS 2008 Report Definition Language (RDL) display tool solves many of the current problems that have manifested themselves in first generation RDL preparation modules (yes, there are several first generation modules). The SSRS 2008 visualization engine provides many functions to process data in an extended text format, as well as a new Tablix control (which, in fact, is not a report control). The Tablix control element combines the properties of matrix and table controls (hence its name), as well as features such as limited support for Rich Text format and built-in capabilities for summary tables.

In addition, the visualization mechanism SSRS 2008 became more universal. The problem is that the visualization mechanism and report designers who created RDLs to enter data into it formed new and incompatible RDL files that are not perceived by the old SSRS 2005 mechanism still used in ReportViewer VS 2005 and VS 2008 controls. This is bad, at least when you need to use BI tools with efficient report generation capabilities to create these new reports and ReportViewer management tools in Windows and Active Server Pages (ASP) applications to deploy them. The fact is that the internal mechanism of the ReportViewer VS 2008 SP1 control allows it to visualize only older first generation RDL-style reports. However, Visual Studio and the ReportViewer control are now being updated and the next generation RDL display tools will be implemented during this update. Unfortunately, this will not happen before the release of VS 2010.

Free SSRS report exporter

If I had the task of restoring a single SSRS report and the only way to solve it was to restore the entire SSRS database, I would, frankly, believe that sheepskin is not worth dressing out. Fortunately, Jasper Smith has mastered (apparently) the SOAP protocol as well as the SSRS programming interfaces and created a free utility to extract RDLs, data sources and all other directory elements from the database. I tested this product and although it didn’t seem to support SQL Server 2008 (2.0.0.0.11) at first, I managed to get the utility to connect to my SQL Server 2008 instance and received reports and also created a Visual Studio BI project for each directory. As a result, I didn’t have to spend any effort to perform the test on another system.

Microsoft Report Builder Version

There are at least four people on Microsoft Developer Campus 35 who understand how and why the report designer has evolved. With the release of each version of this product, the compatibility of these versions was questioned. I have already had to describe the features of all editions of the report constructor, but this does not allow us to understand why we cannot do what we were asked to do when creating SSRS reports.

The report constructor in Visual Studio (VS) 2003 combines with the Reporting SelVices add-on module using a binary “patch”. This constructor can only work with SSRS 2000 RDL reports. This version of VS does not support the ReportViewer control.

Report Builder in VS 2005

Report Builder in VS 2005. This constructor cannot “see” and interact with SSRS 2008 services, but it can import SQL Server 2000 RDL reporting services and interact with SSRS 2005. The product supports the first generation ReportViewer control that can “cross-connect” with SSRS 2005 RDL reports.

Report Builder in VS 2008

Report Builder in VS 2008. This constructor also cannot “see” and interact with SSRS 2008 services. In this case, it is not even able to open SSRS 2005 projects. But the product still supports the first generation ReportViewer control and can “cross-connect” with SSRS 2005 RDL reports.

Report Builder in VS 2008 SP1

Report Builder in VS 2008 SP1. This report designer interacts with SSRS 2008 and can even open SSRS 2005 projects that he converts into VS 2008 format. It supports projects with the first generation ReportViewer control and can “cross-reference” SSRS 2005 RDL reports but not SSRS 2008 RDL reports.

Report Builder, implemented in the SSRS BI Tools suite

Report Builder, implemented in the SSRS BI Tools suite. This new VS BI Report Builder is specifically designed to work with SSRS 2008 as well as with SSRS 2005 projects that it converts to VS 2008 BI format. The product does not support development in Windows Forms or ASP environments, so the issue of using the ReportViewer control is not relevant here.

SQL Server Reporting Service Configuration