Reporting With SSRS Part 1:
In this serires of blogs, i will try to introduce you with some of the key features of the tool which will help you get on board to become a proficient report developer.If you are not aware of Sql Server Reporting Services,it is a tool used for reporting and comes bundled with Standard, Developer and Enterprise editions of Sql Server.
Creating a report in Sql Server Reporting Services (SSRS) is simple and easy,besides the tool is very user friendly.
Lets create a small report with one sub report to get a qucik start with the tool.
We will use Business Intelligence Development Studio (BIDS) to design the report which comes with Sql Server software suite.People aware of Visual Studio will have no difficulty getting conversant to the tool,it’s almost similar.
Let us quickly get into developing a Simple report having a sub report(child report) using Adventure works database(You can download a copy from here if its not installed.)
Step :1
Open Bids and Select File –>New–> Project –> Report Server Project from the top left menu bar.
Give a name to your project and Click ok
If you dont see Solution Explorer window on your left click view from the menu bar on the top and select soulution explorer.You will see two folders already created (1) Shared Data Source.(2) Reports.
Shared data source will have the connection to the DB which the report can pull data from.
You can have more than one data source in your project.Reports folder will have all the reports designed in your project.
Step :2
Right click the Shared data source folder and select Add New Data Source,name the datasource and edit the connection string.Enter the details of sql server instance you wish to connect( Adventure Works is installed here) and select the Adventure Works database in the database drop down.Test the connection and Click ok.Data Source is now created.
You can see the datasource in your Shared Data Source folder in solution explorer.
Select the tabular type of the report and click next. Click the details button until all the columns get into the details box and click next and next. Give a name “ParentReport” to the report and click Finish button.
Preview the report and it shows you all the related counts .
Create one more report with the same process and use the below query.
Right click the reports folder in solution explorer and select Add New Report option.Go thorugh the wizard and in the Design the Query window enter the below query.
/*Displays no of male,female Employees in Each Department*/
SELECT DEPARTMENTNAME, GENDER,
COUNT(1)COUNT FROM DBO.DIMEMPLOYEE GROUP BY DEPARTMENTNAME,GENDER
ORDER BY DEPARTMENTNAME,GENDER
SELECT EMPLOYEEKEY,FIRSTNAME,LASTNAME,MIDDLENAME,TITLE,HIREDATE,BIRTHDATE,EMAILADDRESS,PHONE,GENDER
FROM DBO.DIMEMPLOYEE WHERE DEPARTMENTNAME=@DEPARTMENTNAME AND GENDER=@GENDER
Name the report as “ChildReport” and click Finish.
Preview of Solution Explorer.
Preview of Child Report.
Now that you have two reports, go to parent report by double clicking the report in solution explorer and navigate to the design pane. Right click the Count column data cell and select actions .In the hyperlink option select “Go to report” button and select the child report in the drop down.
Add two parameters which will be passed to the child report to filter data. Click on Add button, select Departmentname in the name drop down and select [Departmentname] in the value drop down.Do the same for Gender and click Ok.
Now go to the preview tab of the ParentReport and hover over the count column of a depatment. You will see a link has been created which will navigate to the Child Report displaying more details of the employees of that particular department and gender.
You can create more than two parameters and more than one sub report in your report and can have many more enhancements to the report like adding total to the count etc.
Hope this gave you a quick start to learn more about the tools.
Regards
Syed Asrar Ali
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
