PEPconnect

SQL Server Reporting Service (SSRS) Intermediate Report Management

This web-based training will review SQL Server Reporting Services (SSRS). It is a server-based report generation software system from Microsoft. It is part of the suite of Microsoft SQL Server services. SSRS is a set of readymade tools, that helps to create, deploy and manage reports.

By the end of this training you will be able to:
  • Describe the folder structure from within Report Manager
  • Run a report
  • Export report output
  • Manage existing reports
  • Create a subscription to a report
  • Identify the main areas of the report builder window
  • Modify an existing report

Welcome to this web based training on the SQL Server Reporting Service (SSRS) Intermediate Report Management. You should now be able to: Describe the folder structure from within Report Manager Run a report Export report output Manage existing reports Create a subscription to a report Identify the main areas of the report builder window Modify an existing report By the end of this training you will be able to: Describe the folder structure from within Report Manager Run a report Export report output Manage existing reports Create a subscription to a report Identify the main areas of the report builder window Modify an existing report SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It is part of  the suite of Microsoft SQL Server services. SSRS is a set of readymade tools, that helps to create, deploy and manage reports. Report Manager Web Based Tool Used to run reports, manage reports, folders, and subscriptions Access via URL supplied by system administrator Used by RIS administrator, management Report Builder Used to create and/or modify reports Access from within Report manager Used by RIS administrator, SQL administrator Important terms Report manager folder structure How to run a report How to export report output How to manage existing reports Commonly used terms in SSRS that will be used in this training: Data Source: The collection of data that is queried to get the information you want in a report.  (syngo Workflow SLR Database) Dataset: Specifies the data you want to get from a data source. (SQL query statement) Report Layout: The data that the report has extracted into a dataset needs to presented to the user. The report layout specifies which fields go in which locations on the screen or on paper. Report Parts:  Report items that have been published separately to a report server and that can be reused in other reports. Embedded dataset: Defined in and used by only the report it is embedded in. Shared dataset: Defined on the report server and can be used in multiple reports Parameters: Variables that can be used to filter your data. (WHERE clause) Expressions: Used to further manipulate query results such as a calculation or formatting change. How to idenfity the different types of folder items How to navigate folders The model folder structure How to build a folder structure for your site Reports Shared Datasets Report parts Folders Data Source Left click to open folder Large text is current folder name Blue text is full folder path Production (PRD)    Datasets    ReportParts    Reports    PRD (DataSource) Training (TRN)   Datasets   ReportParts   Reports   TRN (DataSource) Test (TST)   Datasets   ReportParts   Reports   TST (DataSource) Warning: DO NOT MODIFY the Model Structure   Within the PRD folder are three subfolders and the PRD Data Source Within the Datasets folder are all the model datasets. Within the ReportParts folder are all the model report parts. Within the Reports folder are all the model reports. Details View Last Run Modified Date Modified By To create a new folder: Navigate to folder location Click New Folder Specify Folder Name and Description Click OK To modify folder security: Click dropdown arrow to right of folder Select Security For more information on Folder Security refer to the "Managing SSRS Users" section of the syngo Workflow SLR System Administration Manual To run a report: Left click on report Enter Parameters Click View Report Click the link below to view a video example of this process. *To gain maximum benefit from this WBT, it is recommended that you practice these step by step instructions directly on your system. How to Run a Report How to Run a Report   Browse output pages Change zoom level Search the report Export the output Refresh the data Print the output.  Hover over the image below to see details on each of these functions. Report Files (.rdl) are the definition of the entire report, they can be: Moved Renamed Deleted Downloaded Uploaded To move a report file: Click the dropdown arrow to the right of the report name Select move Choose new location Click ok.   To delete a report file: Click the dropdown arrow to the right of the report name Select delete Verify by clicking OK To rename or manage a report: Click the dropdown arrow to the right of the report name Select manage Enter new report name and description if desired Click apply Note: The window remains open, you must navigate back to your report location manually. Creates a copy in a location off of the SQL server To download a report file: Click the dropdown arrow to the right of the report nam Select "Download..." A banner will popup at the bottom of your window Click the dropdown arrow to the right of the Save button and select Save As Choose your save location Change file name if desired Click Save To upload a report file: Ensure that you are in the folder location that you want the file to reside Click Upload File Rename if desired Select File to Upload Select Save Once the report is uploaded you need to: Resolve "data Source" property Verify Shared Datasets property Review parameter default values and display text Click the links below to view step by step procedures for verifying report information. *To gain maximum benefit from this WBT, it is recommended that you practice these step by step instructions directly on your system. How to resolve Data Source property How to resolve Data Source property Checklist TitleChecklist TypeChecklist ContentStep 1HTMLClick the dropdown arrow to the right of the report name, select manage. Step 2HTMLClick Data Sources tab on the left side of the screen. Step 3HTMLClick Browse. Step 4HTMLFind your data source. Step 5HTMLClick OK. Step 6HTMLClick Apply.  Note: Changes will not take effect until Apply is clicked.   How to verify Shared Datasets property How to verify Shared Datasets property Checklist TitleChecklist TypeChecklist ContentStep 1HTMLClick the dropdown arrow to the right of the report name, select manage. Step 2HTMLClick Shared Datasets tab on the left side of the screen. Step 3HTMLVerify that the datasets locations are correct. Step 4HTMLIf locations are not correct, click browse. Step 5HTMLSelect correct dataset locations. Step 6HTMLClick OK. Step 7HTMLClick Apply.  Note: Changes will not take effect until Apply is clicked. Step 8HTMLTest the report by running it and verifying output.  Adjust the report title if necessary. A subscription is a standing request to deliver a report at a specific time and to have that report presented in a way that you define. To create a new subscription: Click the drop down arrow to the right of your report and click "Subscribe..." Choose method of delivery Select the time the report should run Set parameter values Click OK To view a detailed step by step instruction of this process and a video demonstration, click the links below. *To gain maximum benefit from this WBT, it is recommended that you practice these step by step instructions directly on your system.   How to Create a Subscription - Step by Step How to Create a Subscription - Step by Step Checklist TitleChecklist TypeChecklist ContentStep 1HTMLClick the drop down arrow to the right of your report and click "Subscribe..." Step 2HTMLChoose method of delivery.  By default, windows file share is enabled.  The SQL server can also be configured to send the report to an email (see http://msdn.microsoft.com/en-us/library/ms159155.aspx for instructions). Step 3HTMLType in the path of your file share folder (using UNC standard format - \\server\share\file_path) Step 4HTMLSelect render format of report output. Step 5HTMLDefine overwrite options. Step 6HTMLClick select schedule. Step 7HTMLChoose schedule options.  In this example, we have set the daily schedule to: Every weekday at 6:00 am.  Step 8HTMLClick OK to return to the subscription window. Step 9HTMLSet parameter values. Step 10HTMLEnter username and password if required. (Note: if you enter password before you made all other selections, the password field will be cleared out by the system.) Step 11HTMLClick OK (Note: You will get directed back to the folder path where the report resides) Step 12HTMLSelect My Subscriptions to view any subscriptions you have personally created. How to Create a Subscription - Video How to Create a Subscription - Video   In this section, we will provide a basic overview of the report builder application including the GUI components and how to modify an existing report.   Home, Insert, and View ribbons The report data pane The report design surface The report properties pane The row and column group properties panes The server status bar Home Ribbon - Formatting controls, run button Insert Ribbon - Insert various report components View Ribbon - Show and hide GUI components The home ribbon contains formatting controls and the Run button. The insert menu provides buttons used to insert various report components. The View menu allows you show and hide the GUI components. Hover over the image below to see additional information that is displayed in the GUI when a report is open. This section details the ways in which you can modify the appearance of an existing report. To copy a model report: Open the model report folder Open the report Click the Office Start Button Select save as Choose new location, Enter filename Click save.  Warning: Never modify a model report unless you have created a copy of it. Click the link below to view a step by step of this procedure. How to Copy a Model Report How to Copy a Model Report Checklist TitleChecklist TypeChecklist ContentStep 1HTMLOpen Model Report Folder. Step 2HTMLOpen the Report. Step 3HTMLStep 4HTMLSelect Save As. Step 5HTMLChoose new location.  Step 6HTMLEnter Filename.  Note:  Filename cannot contain space. Step 7HTMLClick Save.  Step 8HTMLYour new filename is shown on the top of the window. Using the formatting tools on the home ribbon, you can modify the appearance of your display text. You can also adjust titles and headings, but be sure not to change any database field names [indicated in brackets]. In order to add text to your report, you must insert a text box.  Click the tabs below to see the steps required to insert a text box. Click the Text Box button (single left mouse click).  Note that your cursor changes to an "ab" text box and crosshair. Images can be inserted onto your report.  Oftentimes this is used to insert your site's logo in the report header. Click the tabs below to see the steps required to insert an image. Click the Image button (single left mouse click).  Note that your cursor changes to an image box and crosshair.   Drag a box the size you would like the image to be displayed.  When you release your mouse, the image properties window displays. Click the import button to browse for your image. SSRS includes several built-in fields that are commonly used in reports such as execution time, page number, etc.  Click the tabs below to view the steps required to insert a built-in field onto your report. Position field on report and resize if necessary. To delete a column: Select the entire column by clicking the box above the column description Right click Select delete column. Note: Deleting a column does not alter the query, it simply doesn't display that information in the report. Click the tabs below to view the steps required for inserting a new column. Select the entire column by clicking the box above the column description. (Note: To display the frame above the column description, select any cell.) Right mouse click and select insert column. Hover over the cell to display the "notes" icon.  Click the notes icon and select the database field that you want displayed in this column. Note: Only database fields that are defined in the dataset are available to be selected. The column title will be automatically inserted to match database field, but column title can be modified if desired. Don't forget to save your work!