Create a report with Reporting services

See the clip

What is Reporting Service?

Reporting service is a free application coming with sqlserver so if you have sql server in your organization then probably you have Reporting service as well. It is a repository for your reports you can create reports by some tools then publish your report to this repository then your applications including windows and web can send request to this repository and get the report in a browser. Since you can create complex report to show different charts and graphs, it is one of the handy tools to use specially in Intranet web applications. Also you can use reporting service for internet applications However, you will be charged for that.

What do you need to know about Reporting services?

1- How to create a report

2- How to publish a report

3- How to see the report in the server

4- How to access a report from other .net applications (Next post)

5- How to create a chart report (there is a sample in clip)

6- How to create a parameterized report (future posts)

7- How to pass parameters to a report from other applications (future posts)

How to create a report?

When you install sqlserver2005 you have a special template will be added to your visual studio which is "Sql server Business intelligence development studio". You can use this template to create a report then as I explained you have to publish your report then other applications can address that report. Open visual studio from file menu select New Project then you can select Business intelligence project. In this list you can find Report server project wizard or Report Server project. Using Wizard help you to speed up implementation of you report.

Let's select Report Server Project. As you see in solution explorer you have two folders one is shared data source and another one is Reports. You need a connection to Database in Reporting service we call that Data source. But if you have multiple reports over one data base you better create one Shared data source to share between them. Because if database changed then you need just change shared data source and everything should be all right. The reports folder is where you create your Reports. I recommend you to use wizard to create the basics of your report then change report to the way you like.

Create a share data source:

Right click on Shared Data sources folder and select Add new data source then you will see this window:

All you need to do provide an alias name for the shared data source and a correct connection string. The connection string implies to which data base shared data source can connect. You may click on Edit then you will see a familiar window to define a connection string. You can use windows or sql server authentication. Then click on ok. You are done. Now you have a shared data source that can be used in different reports in your project.

How to create a report?

All you need right click on Reports and select Add New Report then you will see the wizard window. As I said you better use wizard then customize your report the way you like later on. (I am going to take a report from a table that has suburbs, postcodes, states)

Click on next in next page select the shared data source as you see you can select a custom data source but I do not recommend this. Using shared data source is more maintainable.

In Next step you need to define your query in our case we are retrieving data from a table but you can retrieve data form view as well. If you take a look at the next picture you will see the query builder button when you go to that page in left top corner of window there is a button which is generic query designer then you can add the tables and views you want and define the query with query designer assistance.

In next step you need to define your report type in our case we want Tabular:

In next step you have the ability to define which fields you need to show in report also you can define group by to show details of one product (please see the clip to see a sample of this) right now we just choose the fields we want to have in our report.

Select a style for your Report and click on next you are done

You have three views: Data, Layout and Preview

In the Data view you can see and modify your query also you can add other queries you may use in the report (I will explain how to add another query for this report and why you may need to do that in future posts)

In layout you can change look and feel of your report or add other element please see your toolbox when you are in this view then you will see these elements (I will talk about some of these elements in next posts):

In the Preview tab you can see the result which is your report.

How to publish a report to reporting server

Next step is that you need to publish you report to Reporting Server to do this you need to right click on your project in solution explorer and select properties. Then you need define 4 properties at least:

TargetServerUrl: the reportserver address usually it is the same server as your sqlserver with report server virtual directory see the picture

TargetRootFolder: define in which folder the report should be deployed in your reporting server

TargetDatasourceFolder: define in which folder the data sources should be deployed in your reporting server

OverwriteDataSources: define whether you prefer to overwrite previous deployed data sources.

I highly recommend specify the same folder for TargetRootFolder and TargetDatasourceFolder because if you are getting Reporting services for an internet site from a shared reporting services in internet you will be offered just one folder so you need to have all of them in one folder.

Click on ok again right click on your project select deploy then after a while it will be deployed to the server.

How to check report in your server?

Go to your reporting service through a browser like this: http://yourserver/Reportingserver in my case I have to use http://falcon/Reporingserver then in that page find the folder in my case TestProjectReport then you will see all reports in that folder select your report in my case Report1

Then you will see the result.
The very power full point about Reporting service is that you can export data to different sources like pdf, Excel, Web services and etc.

Please see the clip to get some more information. Also you will learn how to create Chart reports.

See the clip