Create a report in SSRS (SQL Server Reporting Services 2008)

SSRS is a comprehensive server based solution for reporting and it is widely used for creating, managing and delivering web based reports. Let's see a simple way of creating a useful report for a particular application.

(01). First open the SQL Server Business Intelligence Management Studio (Start menu > SQL Server 2008 > SQL Server Business Intelligence Development Studio)

(02). Then add a report server project under Business Intelligence projects.

(03). After adding the project right click on the reports folder in the solution explorer and click add new item. There you can select Report wizard and Report; we start with the report (.rdl).

(04). Now you have to design the report, you can use the toolbox items to add a table and  columns. You can also add a matrix instead of a table.

(05). When the table is added to the report it shows a popup to set the data source for the report.You have to set  a  particular  database  in  your  local  machine  or  a  particular  server  as  the  datasource. 

Make sure that when you set the connection to datasource keep the CheckBox (save my password) checked, otherwise you have to give datasource credentials when the report is previewed.  Now you have successfully created the datasource for your report. Then you have to set a dataset for the report. The dataset  can be created using Query Designer or a stored procedure in the datasource (database). We do it using stored procedure.

(06). Use a stored procedure to set it as the dataset of the project.

Now you can view the report data in a window by clicking the view in menu bar and clicking the report data on it. In report data there are folders named Built-in fields, Parameters, Images and Datasource. Inside the datasource folder you can see your dataset and its fields.

Then you can easily set the dataset fields to report columns as in the following image.

Now your report is ok. If the dataset stored procedure contains parameters they automatically added to the parameters folder in the report data. When you preview the report you need to provide the report parameters. Otherwise the dataset stored procedure doesn't contain parameters just click on the preview tab and report will be generated.

Adding filters, grouping and adding subtotals can be added this report and also add colors to make the report attractive. I'll discuss them in my next post.