How To Use SQL Server Reporting Services

Business development often requires analysis and data mining. Determining trends with which Customers and other stake holders deal with a business are of sole importance to the business. In fact, helping a businessman to grow his business has contributed a lot in rapid growth of IT industry.

Microsoft SQL Server Reporting services provide a solution to those tasked in finding business trends. Briefly speaking:

1. Reporting services lets you create rich reports and format them based on your requirements.

2. You can create interactive reports using parameters. Also, RS comes with an API to generate complex reports.

3. You can assign permissions to specific users for viewing reports.

4. The phenomenon goes like:

   • a) As a developer, you will first need to create .RDL (Report Definition files) using SQL Server Business Intelligence studio. This file has xml syntax to define your report and in this file you can specify the data source of your report and how the report will be displayed.

   • b) Once you are done with creating RDL File, you will need to deploy it on Report Server which manages all reports and information kept in reports on backend. Some of its tasks involve keeping metadata of reports, Data source information for reports and cached reports. All of this information is kept in SQL Server databases which you will configure as part of configuring Reporting services.

   • c) Once you have developed and deployed reports on report server, users can view reports using report manager which uses report server on backend.

This article will explain installation and Configuration of Reporting services. Besides, we will generate a report to learn basics.

Installing SQL Server Reporting Services

Before you install reporting services, Make sure that SQL Server 2005 is installed. Not all the components those are required comes with default installation package of SQL Server. A separate toolkit for installing additional components is available on http://msdn/microsoft.com/vstudio/express/sql/download/default.aspx.

I have attached a few important screenshots for installing SQL Server Reporting services and its IDE, SQL Server Business intelligence development studio.

After the installation, you will need to configure Reporting services. That is, you will need to create a report server, report manager and specify the database which reporting services will be used. As we said, Report Server uses a database for internal operations. Please see the figure below:

In the above screen, Red shows items which you need to configure.

Report Server Virtual directory: Create a virtual directory for report server here.

Report Manager Virtual directory: Configure report manager by creating its virtual directory.

Web Service identity: Reporting services API can be used later on if you configure web service here.

Database setup: This is very important step. As we said before, reporting services uses an internal database which can be configured here.

Other Components that you will need to run SQL Server reports:

1) To create reports, you will need SQL Server Business Intelligence studio. This is Visual Studio IDE specially designed for generating reports.

Note. To install this IDE, You will need to run additional toolkit that you have downloaded from Microsoft's website before.

2) To integrate with your website, you will have to use Report Viewer component in your IDE. In case of Visual Studio, Report Viewer comes by default.

So, I am hopeful after following the above steps, your reporting services are installed and properly configured.

A few common errors that I saw while working with SQL Server reporting services:

1) When I installed everything on my machine and I accessed reports server from IE, it said:

The report server has encountered a configuration error. See the report server log files for more information (rsServerConfigurationError).

Solution: Later on, I found that proper permissions are not set on the folder where Reporting Services was installed. In my case, it was C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services.

It seemed ASPNET user does not have rights to read the configuration file rsreportserver.config but to save my time, I gave full permissions to ASPNET user and it worked (Just try to give read access rights first if that works). So the solution: Give access rights to ASPNET user on the folder where Reporting Services are installed.

2) In another scenario while working at my developer machine, when I tried to access my Report Server from IE, it said:

"The permissions granted to user KALIEM-PC\Kaliem are insufficient for performing this operation. (rsAccessDenied)"

Solution: Now, that is due to improper settings of Virtual Directory. I browsed to Report Server virtual Directory and disabled Ananymous access in it. It should be disabled by default I suppose, but as part of my experimenting things, I had enabled it for unknown reasons ;). Anyway, disabling it works and when I browsed to reports server, it prompted windows username and password for first time. When I supplied these credentials, everything worked fine.

Creating a report

So, we are ready to create a report now. The steps are:

1. Open SQL Server Business Intelligence studio and create a new report server wizard project.

You can also create Report Server project instead but at this stage, it is recommended you start with a wizard project.

2. Once you have created the project, you can specify the data source from where to bring data for our report.

3. In the next step, you can specify the query to fetch data which would be used in your report.

4. Next comes different options for formatting your report and that can be done using your preference as to how you want to display your data.

5. Once you have report defined via wizard, you will be able to edit it using Business Intelligence studio.

6. As you can see in figure 8, you can define the layout of report using the toolbox at the left. And before you build and deploy your report, you can preview it here as well. Note that a report to be accessible in another project, say in ASP.Net website on developer machine, you would need to deploy the report on report server. Also, any change in report would require you to re-deploy the report in order to reflect the latest change.

7. This completes our process of creating a report and deploying it on report server.

SQL Server Reporting Services - Summary

SQL Server Reporting Services enhances business intelligence skills by providing developers the ability to generate rich, nicely formatted reports.

The architecture consists of Report server maintaining information about reports at the backend and report manager to provide a web user interface to display reports.

Generating a report requires you to install SQL Server, Reporting services and Business Intelligence studio.

Generating reports involve specifying the data source from which data is to be fetched for our report and defining the desired layout.

Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |