Reporting services in SQL server 2005
Demo names:
Server: CONFIGWS1
Database: technoencyclopedia
Username: Demo User
Password: 123456789
Connection: My DataSource
Introduction:
In today’s competitive world, business is run through logic, not by rules. And to produce a logical plan, you need to have data or information in useful form. Every business requires reports daily, monthly, or yearly to manage their business cycle.
SQL server 2005 also provides a competitive and efficient solution for reporting called SQL server reporting services.
SQL server reporting services, also known as SSRS, runs on internet information services (ISS) on Windows platform
In this article, we will discuss how to configure the reporting server and generate reports from SQL server 2005.
Reporting services:
SQL Server Reporting Service provides full support to the reporting cycle which includes:
- Authoring. You can create and send reports to the report server through a design tool that works by using Report definition language. This is an XML-based language which is a standard for this purpose.
- Managing. Reports are managed in files and folders and are formed as web services. You can define specific schedules to manage the reports and can define different tasks with specified time in order to maintain consistency.
- Delivering report. The reporting service in SQL server 2005 supports the report delivery on-demand as well as at the specified time. It also supports automated email generation of reports, as defined by the user.
- Security. SQL server 2005 reporting services are highly secure and provide an excellent security platform for confidentiality of the reports.
As there are several security checks and options the sensitive data is highly secured. This function also provides an option of restricting any user from viewing some reports.
Report security uses both SQL server and Windows for data and report sources. In short you can create an excellent reporting environment using SSRS with a very reliable and secure environment.
Basically, SSRS is divided in to two components and these two components are: Report Server and the Business Intelligence Development Studio (BIDS).Report server comes built-in with SQL server 2005, as a service. The basic functionality of this service is to make sure that SQL can run reports using SSRS. Whereas the Business Intelligence Development Studio enables you to design you report in different formats. It comes with SQL server 2005 additional tool kit.
Configuring the reporting server:
Now let’s see how we can configure a report server first. Go to Start menu, select SQL server 2005 -> Configuration tool and then select Reporting service configuration.
Now the following window will be opened:
Here you can see a pop-up window. Provide the name of the machine that you want to act as your report server, select the name of the instance and click Connect.
The configuration will take few minutes and then the following window will appear:
Now here you can see the vertical menu, which you need to configure, as per you requirement. Let’s go through them one by one.
After successful server status, we have report server virtual directory. As I told you this acts as a web service and runs in IIS; it needs to have a virtual directory. You can apply the default setting or click New to create your own.
Clicking the New button will enable a pop-up window in which you can provide the directory name and the website name that you want to keep. Click Ok and move to the reporting manager virtual directory tab.
Now here again you need to create a virtual directory for report manager. Define the website name and virtual directory name, and click Ok.
In the above image, you can see that the manager virtual directory ID was successfully created.
Now, move on to the Web service identity. SSRS requires ASP.et platform to run over IIS; for this, you need to have a .net platform on your report server. Give the desired path and select Apply.
You can see in the above window that the web service identity has been created successfully.
Now let’s move to database setup:
Select the server name on where database resides and click the Connect button to connect to it. The following pop-up window will appear:
In the above window, you need to specify the SQL server instance on that server and select the account type to connect and press Ok button.
Now that you are connected with the server, the database in that particular SQL instance will appear in the drop-down of the database name. Select your desired database.
Now select the account type, if Windows or SQL provides the credentials, and then press the Apply button. Your report server has been configured successfully.
Configuring report through Report Wizard
Now let’s see how we can create an SSRS report using the report wizard. For this purpose you need to have Visual Studio 2005 installed on your machine. Open a new business intelligent project in Visual Studio 2005. Select Project > Add New Item. And Select the Report Wizard .write the file name “1st Report.rdl”. Click Add. And then Click Next on the Welcome screen. Now you can select the data source from the screen below:
From there, select the new data source radio button, and type the name of your data source
Now click Edit to view the connection properties:
Enter the server name. Use the SQL Server Authentication if you want to; enter the username and the password. Now select the database. Test the connection and click Ok.
Now then, it’s time to design our query using the Query builder. Enter select * from (type database name) in the Query builder text box and press Next. Now select the report type and press Next.
The following window will appear:
Now there is a column of available fields. These are the fields available in the Database.
Select any field in the available fields, then click Group. This will bring your field in the Group column. This way, you can select the fields that you want to appear in the report. When done, click Next.
The following window will appear:
This window displays the attributes of you report. Press Finish to end.
Now then you will be able to view your report in Visual Studio as follows:
You can see in the above window that there are three tabs: Data, Layout, and Preview. Now click on the Preview tab to view your report.
Eureka!!!! You can see the data from you Database in your report.
Conclusion:
In this article we discussed a very important external service of SQL server 2005, the reporting service. This article describes the creation of Report server and the generation of reports using Business Intelligence Development Studio (BIDS).
