Admin

Urvi Makhiyaviya

Posts by this author

Quick Guide: Introduction to SSRS
SSRS
Quick Guide: Introduction to SSRS
Urvi Makhiyaviya
Jan 20, 2021

SSRS or SQL Server Reporting Services is one of the tools available in Microsoft SQL Server Data Tools. It is a server-based reporting platform that you can use to create and manage tabular, matrix, graphical, and free-form reports that contain data from relational and multidimensional data sources.    SSRS is a set of readymade tools, that helps you to create, deploy and manage reports. SSRS allows are reports to be exported in various formats (Excel, PDF, word ,CSV ,XML etc) SSRS allows reports to be delivered via emails or dropped to a share location.   Advantages of using SSRS Supports a variety of file formats Facility to drill down to different levels of data Helpful and perceptive reporting Access to enterprise-level features Simplistic implementation owing to a centralized server   Why SSRS? Here, are prime reasons for using SSRS tool: SSRS is an enhanced tool compared to Crystal Reports Faster processing of reports on both relational and multidimensional data Allows better and more accurate Decision-making mechanism for the users Allows users to interact with information without involving IT professionals It provides a World Wide Web-based connection for deploying reports. Hence, reports can be accessed over the internet SSRS allows reports to be exported in different formats. You can deliver SSRS reports using emails SSRS provides a host of security features, which helps you to control, who can access which report   Working and Architecture The main components of SSRS are the following:  Report Builder -This component is basically used as a drag and drop utility which can be used to pick any functionality or tables and drag it as per usage. It runs on the client computer. Report Designer - This component is used to develop reports. Complex reports can be developed with ease using this component. It is a publishing tool which is hosted in SSDT (SQL Server Data Tools) or visual studio. Report Manager -To access any web-based reports, we can make use of Report Manager. Report Server - This component is used to store SQL server Engine metadata. Server Database Report - This component is used to store security settings, report definitions, metadata, delivery data, etc. Data Sources - The reporting service components retrieve data from data sources like multidimensional, relational or traditional data sources.   Reporting Life Cycle Report Authoring: In this phase, the report author defines the layout and syntax of the data. The tools used in this process are the SQL Server Development Studio and SSRS tool.  Management: This phase involves managing a published report which is mostly part of the websites. In this stage, you need to consider access control over report execution.  Delivery: In this phase, you need to understand when the reports need to be delivered to the customer base. Delivery can be on-demand or pre-defined schedule. You can also add an automation feature of subscription which creates reports and sends to the customer automatically.    What is RDL? Report Definition Language (RDL) is an XML representation of a SQL Server Reporting Services report definition.  A report definition contains data retrieval and layout information for a report.   Create RDL Report You can create a RDL reports using any of the following reporting tools,  Syncfusion Web Report Designer: Provides intuitive user interface to create or edit report online.  Microsoft Report Builder: You can create a RDL report using the Microsoft stand-alone Report Builder.  Visual Studio Report Server project template: To create a RDL report in Visual Studio, a Report Server project is required where you can save your report definition (.rdl) file.    How To create a report server project From the File menu, select New > Project. In the left-most column under Installed, select Reporting Services Select the Report Server Project icon   Creating a report definition file (RDL) In the Solution Explorer pane, right-click on the Reports folder. If you don't see the Solution Explorer pane, select View menu > Solution Explorer.  Select Add > New Item.    In the Add New Item window, select the Report icon.  Type "PatientDetail.rdl" into the Name text box.  Select the Add button on the lower right side of the Add New Item dialog box to complete the process. Report Designer opens and displays the Patient Detail report file in Design view.    Setup Connection In the Report Data pane, select New > Data Source. If the Report Data pane isn't visible, then select View menu > Report Data OR (ctrl + Alt + D).   The Data Source Properties dialog box opens with the General section displayed.   In the Name text box, type “PatientDetail". Select the Embedded connection radio button. In the Type dropdown selection box, select "Microsoft SQL Server". In the Connection string text box, type the following string: Data source=Magnusminds; initial catalog=Patient Select the Credentials tab, and under the section Change the credentials used to connect to the data source, select the Use Windows Authentication (integrated security) radio button. Select OK to complete the process.   Define a Dataset for the Table Report In the Report Data pane, select New > Dataset.... The Dataset Properties dialog box opens with the Query section displayed.  In the Name text box, type "GetPatientDetails".  Below that, select the Use a dataset embedded in my report radio button.  From the Data source dropdown box, select PatientDetail.  For the Query type, select the Text radio button and Type Query into the Query text box. Select OK to exit the Dataset Properties dialog box.   The Report Data pane displays the AdventureWorksDataset dataset and fields.    Add a Table to the Report Select the Toolbox tab in the left pane of the Report Designer. With your mouse, select the Table object and drag it to the report design surface. Report Designer draws a table data region with three columns in the center of the design surface. If you don't see the Toolbox tab, select View menu >Toolbox.    In the Report Data pane, expand the AdventureWorksDataset to display the fields.  Drag the field from the Report Data pane to the first column in the table.    Preview Your Report Select the Preview tab. Report Designer runs the report and displays it in the Preview view.    The following diagram shows part of the report in Preview view.    Deployment of An RDL Report File in SQL Report Server By Uploading RDL file in Report Server.  Open SSRS Server from webportal URL. There, you will see the upload button. Click the upload option and browse the rdl file of the report from the location. It uploads your report to the report server. Click on the uploaded file it runs the report in the browser, hence, you can view it in the browser. 

Read More
magnusminds website loader