Deploying the Report on the Web Server

Deploying the report on the web server

Before you can serve your report from the web server, you need to have an understanding of the web sites and folders created during installation and how they are related to the SQL 2005 Report Services Server. When the MS Server SQL 2005 is installed two IIS sites are also created. These sites are: Reports$Mysorian and ReportServer$Mysorian

1. Create your views

There are ways to aggregate your data in the report itself, but it’s easier and cleaner to use SQL views to provide the data for the report and use Reporting Services only for the report. So go to SSMS and create your view.

2. Create a new BIDS (Business Intelligence Development Studio) solution based on the “Report Server Project” Template. Add a new report by clicking on the Report node in the Solution Explorer. Next we need to connect to a data source. Give it a name, click edit and connect to your database.

3. Click next and the Query Designer appears. Here you enter the T-SQL statement for your data. Ideally it’s just a “SELECT * FROM yourView” (or select each column distinctly). You can test it using ‘Run Query’. You can also choose to use data from a stored procedure if you like, just select the appropriate option.

4. Design your report. You can add elements to the report surface, by right clicking on the center surface and select them from the insert menu. The most important are Header, Footer and Table. In the Header and Footer you can add Textboxes with text or by right-clicking on them and choosing expressoin you can choose some variable input. For example page number, report name etc. In the table you can drag columns from the “Report Data” (on the left showing the data source you just added). When you are finished, you might have something like this. Click preview to get a glimpse at what your boss might get.

5. Now let’s deploy it to the report server. First we must set the report server url. Right-click on the solution in the Solution Explorer and click on Properties. You will see the following dialog. Change the TargetReportFolder value to something you wish and provide the TargetServerURL as (not just /report!). Press save. Now right-click on the solution again and select Deploy.

6. When it finishes open the url (not /reportserver!). You will see something like this. Go ahead and click on Test.Reports (or whatever you specified as TargetReportFolder) and then click on your test report. You will be greeted with the web version of your report.

7. Now your Boss will not want to go the web page to get that report, so let’s look at sending it to him per email automatically. First we need to change the security settings for connecting to the data source. In the report page, click on Properties > Data Sources. Change the “Connect using” to “Credentials stored securely in the report server” and specify an account that has access to the data. Also check “Use as Windows credentials…”.