Mar 13, 2012

SSRS Reporting Services and Architecture: Part II


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:-
  1. Report Manager
  2. 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
27.gif
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.
28.gif
The other way we can access report manager by MS SQL Server Management Studio.
29.gif
Connecting to Reporting services is shown in below image
30.gif
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-
31.gif
To open report in Report Manager right click on report & select 'View Report'.
32.gif
To access report in Report Manager first of all it needs authentication:-
33.gif
After successfully authenticated we are able to view report shown below:-
34.gif
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.
35.gif
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">
                        &nbsp;<asp:TextBox ID="txtFromDate" runat="server"></asp:TextBox></td>
                </tr>
                <tr>
                    <td align="right">To</td>
                    <td align="left">
                        &nbsp;<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">&nbsp;<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 ObjectByVal e As System.EventArgs) Handles Me.Load
    End Sub
    Protected Sub btnSubmit_Click(ByVal sender As ObjectByVal 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

36.gif
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.
37.gif
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.
  1. Data Source
  2. Data Source View
  3. Report Model
38.gif
First of all we need to give details of data source from where we have to fetch data
39.gif
Database server & database selected & connection tested here.
40.gif
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.
41.gif
Data Source [dsDataSource] that we created previously chosen here
42.gif
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
43.gif
After adding Data Source View, we need to create Report Model by right clicking on Report Models folder.
44.gif
In Report model we chose Data Source View.
45.gif
Once we complete Report Model, all tables & their corresponding columns shown in application.
46.gif
Now our Report Model application completed. So we need to deploy it on server. Make changes in property of application as shown below.
47.gif
Here nsarora is our server & ReportServer$nsarora is report server.
Now go to solution explorer right click on project name & choose Deploy:-
48.gif
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.
49.gif
After successfully connected to report server, Report Builder will be opened. Report Builder will contain Report module we created in last section.
50.gif
Different sections of Report Builder have been shown in below image:-
51.gif
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.
52.gif
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.
53.gif
We can also put sorting criteria on report by using sorting window.
54.gif
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.
55.gif
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.
56.gif
Enter authentication details to connect to Report Database.
57.gif
After connected, we have Data Sources, Folders containing deployed reports, Models folders.
Below given image shows details of these folders of Report Server database.
58.gif
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.
59.gif
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:-
  1. Authentication Service: - To access reports, or any tools with reporting server, authentication service involved.
  2. Deployment Service: - To deploy or publish report on server, deployment services involved.
  3. Data Processing: -Data need to be shown in report processed.
  4. 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.

No comments:

Post a Comment

Hi,

Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.