April 19, 2011

SSRS: Introduction to SQL SERVER Reporting Services

Report is a better presentation of data. From report user/Management can easily analyze the information. Report is easy to explain such information like Sales graph, profitability etc..

SQL SERVER provides rich kind of report that can be easily generated from SQL SERVER and send it to user/management in different formats like Excel, word, HTML etc..SSRS (SQL SERVER Reporting Services) also supports CHARTS which is very useful when we need to develop reports for Management.

To start with report, We can see how to create a report with SSRS. We will learn this by following steps:

1. Create Report.
2. Deploy Report on server
3. Use Report from application (Asp. Net)

1. Create a Report

  • Open Visual Studio and select "Report Server Project"

Introduction to SQL SERVER REPORTING Services SQLYoga#1

  • Check Solution Explorer (Right Side) and click on "Add New Report"

    Introduction to SQL SERVER REPORTING Services SQLYoga#2

  • Select Data Source. Enter information to connect with database and click on "Next"

    Introduction to SQL SERVER REPORTING Services SQLYoga#3

  • Build query which returns data to be displayed on the report.

    Introduction to SQL SERVER REPORTING Services SQLYoga#4

  • Select Report Format. For simple report we select "Tabular" format

    Introduction to SQL SERVER REPORTING Services SQLYoga#5

  • Select which are the columns that needs to be displayed on the report. User can also select columns if report needs to make GROUP BY

    Introduction to SQL SERVER REPORTING Services SQLYoga#6

    For Sample report, I have selected only some of columns that should be displayed on Report.

  • Select Table style

    Introduction to SQL SERVER REPORTING Services SQLYoga

  • Enter Report name, "Sample Report"

    Introduction to SQL SERVER REPORTING Services SQLYoga

    It displays summary info we have selected so far.

    And that's it.

    We are ready with the report now.

    Introduction to SQL SERVER REPORTING Services SQLYoga

    Preview Tab, we can preview report

    Introduction to SQL SERVER REPORTING Services SQLYoga

    So, report is done. Now we need to deploy this report on server, so user can use it from server.

2. Deploy Report to Report Server:

    To deploy report on Report server, we need to have Report server information like Report Server URL, Credentials to connect to Report Server. Today we will just deploy report to Local Report server, I will explain in another post to How to deploy report to the server which requires credentials.

  • Click on Report Project and open Properties:

    Introduction to SQL SERVER REPORTING Services SQLYoga

    Introduction to SQL SERVER REPORTING Services SQLYoga

    Here we need to enter Report server information where we need to deploy this report and from where application/user can get this report.

    • Make OverwriteDataSources : True
    • TargetReportFolder : "My Reports" (given by hosting provider/Remote computer)
    • Target Server URL: <Report URL>(Report Server URL)

    Now we are done with server information and we need to deploy report.

  • Once properties are set, we need to deploy report on server, right click on report file and click on "Deploy"

    Introduction to SQL SERVER REPORTING Services SQLYoga

    Now report is deployed on the server. We can check that report is on server or not by:

    1. Go to your browser (IE, FF, Chrome etc.)

    2. Enter URL: Go to Report Server URL (http://<IP/domain>/ReportServer

    3. It will display page like:

    Introduction to SQL SERVER REPORTING Services SQLYoga

    4. Click on "My Reports"

    Introduction to SQL SERVER REPORTING Services SQLYoga

    Hurray, we can find our reports here on the server.

    Now, report is deployed on the server, now we need to build application from where we need to use this report


3. Use Report from application (Asp.Net)

  • To use this report from Web application, we need to create "New Website" OR you can use existing website if you have.

    Introduction to SQL SERVER REPORTING Services SQLYoga

    Now, in the application, we need to create one ASPX page where we need to use ReportViewer control as follows:

    Introduction to SQL SERVER REPORTING Services SQLYoga

    Microsoft provides this control to load reports in web/desktop application. Now we need to write a code in this page, so Report Viewer control can load report on this page.

    Introduction to SQL SERVER REPORTING Services SQLYoga

     

This is simple code, which tells Report Viewer control to load report. Let me explain each line.

1. ReportServerUrl and ReportDataSourceName are set up with server information.

2. Then we need to setup "ReportName", it tells report viewer control to load that report from Report server.

     .ReportPath = ReportDataSourceName + "ReportName"

    NOTE: "ReportName", will be actual report name

3. And we also need to setup Report Credentials, so "Report server" can allow our application to access this report.

That's it.

Now run application and you can find that you can access "Reports" in application.

0 comments:

Post a Comment