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]
<form id="form1" runat="server">
<div>
<table width="100 %" class="lab1" align="center">
<tr>
<td align="right"
>From</td>
<td align="left">
<asp:TextBox ID="txtFromDate" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td align="right">To</td>
<td align="left">
<asp:TextBox ID="txtToDate" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td></td>
<td align="left">
<asp:Button ID="btnSubmit" runat="server" Text="Submit" CssClass="Button" />
<asp:Button ID="btnReset" runat="server" Text="Reset" CssClass="Button" /></td>
</tr>
<tr>
<td
colspan="3"
bordercolor="green"> <rsweb:ReportViewer ID="ReportViewer1" BorderWidth="10" BorderColor="AliceBlue" ProcessingMode="Remote" Visible="true"
runat="server"
Width="688px">
</rsweb:ReportViewer>
</td>
</tr>
</table>
</div>
</form>
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.