syngo Dynamics 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. 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 Dynamics 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. Reports Shared Datasets Report parts Folders Data Source To create a new folder: Navigate to folder location Click New Folder Specify Folder Name and Description Click OK 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 Click Here for Fullscreen Image Click Here for Fullscreen Image 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. 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 Click OK 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. Click Here for Fullscreen Image Click Here for Fullscreen Image 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. Welcome to this web based training on the syngoDynamics SQL Server Reporting Service (SSRS) Intermediate Report Management. Click Here for Fullscreen Image Click Here for Fullscreen Image Report Manager Web Based Tool Used to run reports, manage reports, folders, and subscriptions Access via URL supplied by system administrator Used by department administrators, analysts, and management Click Here for Fullscreen Image Click Here for Fullscreen Image Report Builder Used to create and/or modify reports Access from within Report manager Used by department administrators, analysts, and management Click Here for Fullscreen Image Click Here for Fullscreen Image Important terms Report manager folder structure How to run a report How to export report output How to manage existing reports Click Here for Fullscreen Image Click Here for Fullscreen Image Report Files (.rdl) are the definition of the entire report, they can be: Moved Renamed Deleted Downloaded Uploaded Click Here for Fullscreen Image Click Here for Fullscreen Image To move a report file: Click the dropdown arrow to the right of the report name Select move Choose new location Click ok.   Click Here for Fullscreen Image Click Here for Fullscreen Image To delete a report file: Click the dropdown arrow to the right of the report name Select delete Verify by clicking OK WARNING: Only delete reports you are sure you no longer need! Click Here for Fullscreen Image Click Here for Fullscreen Image 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. Click Here for Fullscreen Image Click Here for Fullscreen Image 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 NOTE: A good practice is to download a copy of a report before deleting it. Click Here for Fullscreen Image Click Here for Fullscreen Image 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 Click Here for Fullscreen Image Click Here for Fullscreen Image Left click to open folder Large text is current folder name Blue text is full folder path Click Here for Fullscreen Image Click Here for Fullscreen Image Details View Last Run Modified Date Modified By Click Here for Fullscreen Image Click Here for Fullscreen Image syngo Dynamics Analytics Report Models Siemens.Cvis.Shared.DataSource (DataSource) syngoDynamics Datasets ReportParts Reports syngoDynamics (DataSource) Warning: DO NOT MODIFY the Model Structure   Home Home syngoDynamics Folder syngoDynamics Folder Within the syngoDynamics folder are three subfolders and the syngoDynamics Data Source Datasets Datasets Within the Datasets folder are all the model datasets. ReportParts ReportParts Within the ReportParts folder are all the model report parts. Reports Reports Within the Reports folder are all the model reports. To modify folder security: Click dropdown arrow to right of folder Select Security Click Here for Fullscreen Image Click Here for Fullscreen Image 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]. Click Here for Fullscreen Image Click Here for Fullscreen Image 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. Open Insert Menu Ribbon Open Insert Menu Ribbon Click Text Box Button Click Text Box Button Drag Box Drag Box Enter Text Enter Text 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. Open Insert Menu Ribbon 1. Open Insert Menu Ribbon Click Insert Image Button 2. Click Insert Image Button Drag to Size Image 3. Drag to Size Image Select Import 4. Select Import Select Image and Click Open 5. Select Image and Click Open Click OK 6. Click OK Image is Displayed 7. Image is Displayed 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. Expand Built-in Fields Group Expand Built-in Fields Group Drag and Drop Field Drag and Drop Field Position Field Position Field 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 Here for Fullscreen Image Click Here for Fullscreen Image Click the tabs below to view the steps required for inserting a new column Select Column Select 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.) Select Insert Column Select Insert Column Right mouse click and select insert column. Choose Left or Right Choose Left or Right Select Database Field Select Database Field 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. Edit Column Title Edit Column Title The column title will be automatically inserted to match database field, but column title can be modified if desired. Save Save Don't forget to save your work!