SSRS Reporting Services and Architecture: Part II

Amit Dhania
15y
58.9k
0
0

Article

SSRS Report available to End User

After publishing report on Server, report is available to user. There are two ways to expose report to user:-

Report Manager
SSRS Report in ASP.NET Application

Report Manager

Report Manager is web interface that allow to access to reports published on Report Server. Report Manager can access in browser by entering Report Server path i.e. HTTP://NSARORA/ReportServer$NSARORA?SSRS_Demo_Project

After accessing report manager now we can navigate to AdmittedPatientList report shown in list in above image.

We need to provide parameters FROM_DATE & TO_DATE to access list of patients admitted in hospital.

The other way we can access report manager by MS SQL Server Management Studio.

Connecting to Reporting services is shown in below image

After connecting to reporting server we can have folder where we published our reports on report server.

We published our reports in 'SSRS_Demo_Project' folder containing AdmittedPatientList report as shown below-

To open report in Report Manager right click on report & select 'View Report'.

To access report in Report Manager first of all it needs authentication:-

After successfully authenticated we are able to view report shown below:-

SSRS Report in ASP.NET Application:

The first way to provide SSRS Report to end user is Report Manager just studied above. But Report Manager is usually used by System Administrator. So we need to create a custom application in ASP.Net that will be available to user. Here we are creating ASP.NET Application & SSRS Report to be integrated on aspx web page.

ReportViewer Control

First of all create ASP.NET Application in VS 2005. Add ReportViewer control to aspx page. We included to textboxes passing FROM_DATE/TO_DATE parameters in report. Page design has been shown in below image.

Design code has been shown below:-

ASPX[Design page]

Report Parameter in SSRS

On button submit, we pass Server [i.e. NSARORA], Report Server [i.e. Reportserver$nsarora] & name of the report located in publishing folder[/SSRS_Demo_Project/AdmittedPatientList].

Here SSRS_Demo_Project is folder name where we publish our reports on report server. We create object of ReportParameter Class to pass parameters to ReportViewer control.

ASPX.VB[ Code behind Page]

Partial Class _Default
   Inherits System.Web.UI.Page
   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
   End Sub
   Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
   Try
   ReportViewer1.ShowDocumentMapButton = False
   ReportViewer1.DocumentMapCollapsed = True
   ReportViewer1.ShowParameterPrompts = False
   ReportViewer1.ShowBackButton = True
   Dim s As System.UriBuilder = Nothing
   s = New System.UriBuilder
   s.Host = "NSARORA"
   s.Path = "Reportserver$nsarora"
   ReportViewer1.ServerReport.ReportServerUrl = s.Uri
   ReportViewer1.ServerReport.ReportPath = "/SSRS_Demo_Project/AdmittedPatientList"
   Dim PARAM1 As New Microsoft.Reporting.WebForms.ReportParameter("IS_DISCHARGE", "-1")
   Dim PARAM2 As New Microsoft.Reporting.WebForms.ReportParameter("FROM_DATE", txtFromDate.Text.Trim())
   Dim PARAM3 As New Microsoft.Reporting.WebForms.ReportParameter("TO_DATE", txtToDate.Text.Trim())
   Dim P As Microsoft.Reporting.WebForms.ReportParameter() = {PARAM1, PARAM2, PARAM3}
   ReportViewer1.ServerReport.SetParameters(P)
   Catch ex As Exception
   End Try
   End Sub
End Class

SSRS Report on ASP.NET Application

Report Builder

Up to now we studied how to develop a SSRS Report, publish it, and make it available to end user via ReportManager or a custom application that we just created in previous section.

Now Microsoft has given facility to end user to create their own reports of their choices. Here role of Report Builder involved making available environment to end users so they can create report there.

Question also arises that why we need to give option to create own reports to end user. The reason behind it that many times User needs to analyze data according to their requirements.

Report Builder is not giving full access to SQL Server database to end user. It restricts to make available only those tables required by user & other tables not shown to user.

To make limited access to Database, Report Builder use Report Data Module.

As we create a Report Model Project, we get Data Source, Data Source Views, & Report Models folders. So next we are going to use each of these folders.

Data Source
Data Source View
Report Model

First of all we need to give details of data source from where we have to fetch data

Database server & database selected & connection tested here.

After configuration of Data source in Report Model application, we need to define Data Source View. In Data Source View we choose which table we want to expose to end user.

So right click on Data Source View folder & choose Add New Data Source View.

Data Source [dsDataSource] that we created previously chosen here

After choosing data source now we select tables to be available to end user.

In this application we just chosen one table [TBL_PATIENT_REGISTER] only

After adding Data Source View, we need to create Report Model by right clicking on Report Models folder.

In Report model we chose Data Source View.

Once we complete Report Model, all tables & their corresponding columns shown in application.

Now our Report Model application completed. So we need to deploy it on server. Make changes in property of application as shown below.

Here nsarora is our server & ReportServer$nsarora is report server.

Now go to solution explorer right click on project name & choose Deploy:-

So we successfully deployed our Report Model application.

Now its time to hand over facility to create own report to end user. So role of Report Builder involve now.

Report Builder is a tool provided to end user to create own reports.

To access Report Builder, we use following general link to access it:- http://servername/reportserver/ReportBuilder/ReportBuilder.application

Type this link in browser to access Report Builder.

Authentication details to be provided to access report server for Report Builder.

After successfully connected to report server, Report Builder will be opened. Report Builder will contain Report module we created in last section.

Different sections of Report Builder have been shown in below image:-

Now we step by step are creating report in Report Builder. Drag Patient_ID, Name, DOB, Address fields on drag and drop columns fields section.

After selecting fields on report we need to put filter criteria on behalf of which records to be shown. For making filter setting we need to use Filter Data window.

We can also put sorting criteria on report by using sorting window.

So we have created a report in Report Builder, now we need to run it. For it click on RUN REPORT button in menu items of Report Builder.

Report Server Database

Deployed Reports, data sources, Report Module etc all these SSRS Reporting objects stored in Report Server Database.

To connect to Report Data base, go to MS SQL Server Management Studio & connect to Reporting Services.

Enter authentication details to connect to Report Database.

After connected, we have Data Sources, Folders containing deployed reports, Models folders.

Below given image shows details of these folders of Report Server database.

Data Sources folder: - This folder contains dsDataSource that we created in Report module application.

Models Folder: - Having 'SSRS Demo Report Model' file of Report Module Application.

Report Deployed Folder: - This folder 'SSRS_Demo_Project' contains all reports we created here this article and deployed on server.

SSRS Architecture

SSRS Architecture includes different tools & services involved to creating, deployment of reports & making available to end user.

Block diagram of Architecture has been shown below.

Following are the components of SSRS Architecture:-

1. Reporting Services:-

It is the Execution Engine of SSRS that runs all services involved for reporting. These services includes:-

Authentication Service: - To access reports, or any tools with reporting server, authentication service involved.
Deployment Service: - To deploy or publish report on server, deployment services involved.
Data Processing: -Data need to be shown in report processed.
Rendering Service: - On request of a report, response in form of HTML stream made available.

2. Data Sources:-

SQL Server, Excel Sheet, or XML files may be the source of data for reporting.

3. Report Designer: -

This is the tool that a developer used to create reports. This tool we already used to design reports.

4. Report Server Database: -

Deployed Reports, data sources, Report Module etc all these SSRS Reporting objects stored in Report Server Database.

5. Report Builder: -

This is the tool provided to end user to develop reports themselves.

6. Report Manager: -

It is a web interface allow to access reports deployed on report server.

Above brief description has been given about SSRS Architecture. We already had gone thoroughly in each of the component of architecture.