Database Search Solution
(New Version) Search Control
Free ASP.NET Controls
ASP.NET Media Player Control
Flash Video Player Control
ASP.NET Telecommute Jobs
Free IP Location Lookup
Test .Net Regular Expressions
CSS/Table/DIV Page Layouts
Article Sites Master List
.NET Windows Forms
General .NET Framework
Accepting Credit Cards
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:
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.