SSRS Reporting Services & Architecture

SQL Server Reporting Services [SSRS] is Reporting system based on SQL Server. It provides a set of tools & services enabling us to create, manage, and deliver reports for entire organization. It is a Microsoft Product released in year 2000. SSRS Architecture intentionally not given in the starting of article. Before looking on each component of architecture, I preferred to make practical approach to it. In the end of article Architecture has been explained. Create SSRS Report First of all go to MS SQL Server 2005=>SQL Server Business Intelligence Development Studio. Move to File=>New=>Project=>Report Server Project After creating new report project, we get two folders in Solution:- Share Data Sources=>here we set database credentials. Reports=>here we add report files SSRS Data Source Right click on Shared Data Sources folder & add data source. Following window panel will be opened where we need to provide data server details & database name. To confirm that defined database is successfully connected click on ‘Test Connection’. After successfully adding data source, we can add a report in Report folder. Right click on Report folder & Add-New-Report. Window panel will be opened. Select Report item & provide report name ‘Header_Report’. SSRS Report Design After adding new report, we see report has 3 sections in different Tabs: Data-Here we put SQL Query or Procedure to fetch data from database that we have to show on report. Layout-This is the designing section where we format report by dragging tables, rectangle, lines etc from Toolbox. And Data field on report from Dataset panel. Preview-This panel shows how the report will display to end user. All these 3 section circled in below image. SSRS Toolbar Before moving to design report just we can briefly go through Report Items available in Toolbox. Textbox: To add any custom text on report we use textbox. Line: Drawing line on report. Table: Creating a table having rows & columns, header & footer. We can format table according to our requirement. Image: Adding image to report. Chart: facilitate to add different type of charts to report. Subreport: We can add a report in another one report. Like having Header & Footer report on a report. Toolbox items shown in below image:- Now I dragged 3 textboxes on report & put text ‘Dhania Hospital’, & ‘Health is Wealth’, & ‘Bhiwani Haryana 127021’ respectively. After previewing report in Preview tab, report will appear as shown below. After finishing Header_Report, now we are creating new report AdmittedPatientList. After adding new report, move to Data section of report. Select <New Data Set>. A new window Dataset will be opened. Here choose Command type [store proc or text query] we need to use to fetch data from database. Here in this report stored procedure USP_GET_INPATIENT_REPORT. After adding dataset, click on Run button to execute command to get data. Define Query Parameters window opened where we need to pass values to procedure parameters[@FROM_DATE,@TO_DATE etc.] After execution of command, data shown in below panel & Report Datasets occupied with data fields we have in USP_GET_INPATIENT_REPORT procedure. After adding dataset to report, now we move to design report. Move to Layout section of report & drag a table on it. As we drag a table we get 3 sections in it:- Header: here we put data that need to be shown header of report. We can have more than one row in header just by clicking on Header row & add new row. Details: this is the part of table where we drag data fields from dataset panel. Footer: here we add items we need to show in footer of report. SSRS Subreport In this report I am adding subreport item to add Header in report. Sub report Header_Report that we created previously chosen in Subreport property. Now we are adding 2 more rows in header section of report by just right clicking on left most of header column. Report designer provide Expression Window to help developer to use different formula, functions, operations. We can directly drag any data field like patient name, address etc from Dataset window or just right click on any row cell & select EXPRESSION option. EXPRESSION Window has been shown in below image By default table have 3 columns only so we can add more columns as requirements by right click to header of a column & option to add column in left or right of selected column. We can merge no of columns to accommodate more space required for a field. For example in our current report we have to merge all columns in header section to put text ‘List of in patient from X Date to Y Date’. New row added in header section to put name of column name like S.No, Patient Name etc. To format text of cell just right click on cell & select PROPERTY. Here in property window we can set font size, font type etc. Row Formatting: To format a row we need to open property window of a row by just selecting row & right clicking it & go to Property option. In property window we can set border font, type, back color, text alignment, padding etc. Now we dragging data fields like patient name, address in detail section of table just below their corresponding headers like patient name address. It is shown in below image. Now report is ready to use. Move to Preview panel of report & pass required parameters [FROM_DATE, TO_DATE etc] of report. Publish SSRS Report on Report Server After creation of report we need to publish it on report server so it could be available to end user. To publish report on sever we need to set credential of report server. Go to property of Solution explorer as shown below image. In Property window set ‘TargetServerURL’ field with Report-Server [i.e. NSARORA] and report virtual folder [i.e. ReportServer$NSARORA]. Field ‘TargetReportFolder’ contain folder name in report server where published reports saved. In detail it is shown in below image. After making setting for report server credential now we can deploy reports on server. As deployment of report starts output window shows the deployment. 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”> &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 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.