SQL Server Reporting Service (SSRS) for the Advanced User

Welcome to this web based training on the SQL Server Reporting Service (SSRS) for the Advanced User.  Before taking this training ensure that you have viewed the SQL Server Reporting Service (SSRS) Intermediate Report Building WBT. To take advantage of this course you should be familiar with the content and structure of the data source (that is, you need to know which database table columns to use and also in which tables or views each of those columns is located). This training includes exercises that allow you to practice each step on your own system.  If possible, please take this training while you have access to your own system You should now be able to: Create and format a report using Report Builder Create data sets using Query Designer Manipulate query results further using Expressions Create Parameters and Filters and define default values Insert Tables and group the data displayed By the end of this training you will be able to: Create and format a report using Report Builder Create data sets using Query Designer Manipulate query results further using Expressions Create Parameters and Filters and define default values Insert Tables and group the data displayed The five steps that are always followed when creating a new report are: Connect to Data Source that you will be querying Create Datasets to filter the information and find only the information we want to use Create Layout using Data Elements to display the results of your query (such as a table) Verify your report to ensure your data is correct and the layout is well-defined. Publish your report Please ensure that you have access to the following three documents as they will be helpful in understanding the advanced features of the SQL Server Reporting Service and the SLR database structure: The syngo® Workflow SLR SSRS Reports User’s Guide contains instructions for creating, editing, and viewing SQL Server Reporting Services (SSRS) reports for syngo Workflow SLR. The syngo® Workflow SLR Data Dictionary provides a detailed description of data entities and attributes contained within the physical data model of syngo Workflow SLR. Siemens uses the model as a foundation for relational database design. This document contains a list of database tables, views, columns and column descriptions. The syngo® Workflow SLR Entity Relationship Diagrams provides diagrams describing the relationship between the different data components (tables, key columns) of the SLR database. Click the tabs below to view an explanation of common terms used in SSRS. A Report Definition is an XML file that conforms to an XML grammar called Report Definition Language (RDL). In Reporting Services, you create a report definition in a tool such as Report Builder. It includes elements that define data source connections, datasets (queries) used to retrieve data, expressions, parameters, images, text boxes, tables, and any other layout element. A Data Source is defined by the connection and credentials information used to connect to the database. There are two types of data sources: An embedded data source is defined in a report and used only by that report. A shared data source is defined in an external file that is stored on the server and can be referenced by multiple reports. All of the SSRS model reports use a shared data source (that is, a connection to a syngo Workflow SLR database). A Dataset represents report data that is returned from running a query on the data source. The dataset contains a query command, a field collection, parameters, filters, and data options. Similar to data sources, there are two types of datasets: An embedded dataset is defined in the report and used only in that report. A shared dataset is defined in an external file that is stored on the server and can be used in more than one report. Report Filters and Parameters are a part of a report definition. You can add filters to a dataset query to limit the data that is retrieved from the database. Filter values can be specified within the query or can be used as parameters that prompt for filter values to be entered when the report is run. The data that the report has extracted from the datasource is not of much use to you unless you have some way of presenting it to the user. You need to specify which fields go in which locations on the screen or on paper. Layout elements (or data elements) like tablix, chart, gauge, and indicator can be used to organize and manage the data on the report. A report layout also includes titles, text boxes, header and footer, and page numbers.   A report item is an internal but basic concept in a report definition. Properties of a report item apply to data regions, maps, text boxes, images, tables and other design elements that you add to a report. Understanding the properties of a report item can help you to design customized report content and appearance. For example, all report items have a Hidden property to control visibility. A Report Part definition (.rsc) is a report server item that is a fragment of a report definition file. You create report parts by creating a report definition, and then selecting report items in the report to publish separately as report parts. Report parts include data regions, rectangles and their contained items, and images. You can save a report part with its dependent datasets and shared data source references so it can be reused in other reports. Expressions are widely used throughout a report to calculate, display, group, sort, filter, parameterize, and format data retrieved from the Data Source. Many report item properties can be set to an expression. Expressions help you control the content, design, and interactivity of your report. Note: Expressions are written in Microsoft Visual Basic (not SQL!). The Query Designer is a tool within the Report Builder that supports you in creating Datasets. You use the Query Designer for the following tasks: Specify fields to retrieve for the dataset Specify relationships between two objects such as tables Specify filters to restrict the data before it is retrieved as report data Indicate whether to create parameters   Using SQL Server’s view technology, Siemens has created SQL views that help make authoring reports easier. Native database table and column names have been changed to more user-friendly names with meaning. In addition, common joins of tables have been combined into single views to create “virtual” tables containing the columns needed for frequently used queries. This saves having to define the same joins repeatedly.   Report Builder is a report authoring tool that supports you in building the reports you need. Hover over the image below to view details of the Report Builder GUI. The Ribbon is designed to help you quickly find the commands that you need to complete a task. Commands are organized in logical groups, which are collected together under tabs. Each tab relates to a type of activity, such as inserting report items or formatting text. The Ribbon is divided into the three tabs: The Home tab is a collection of commonly used commands focused on the appearance of items within your report. The Insert tab is a collection of commands commonly used for adding report items to the report. The View tab is a collection of commands that control what is displayed within the Report Builder window. From the Report Data pane, you can define the report data and report resources that you need for a report before you design your report layout. For example, you can add data sources, datasets,  and report parameters to the Report Data pane. You can also drag built-in fields from the Report Data pane to the report design surface. When rendered, these fields provide information about the report, such as the report name, the total number of pages in the report, and the current page number. The Report Builder report design surface is the main work area for designing your reports. To place report items such as data regions, subreports, text boxes, images, rectangles, and lines in your report, you add them from the Ribbon or the Report Data pane to the design surface. There, you can add groups, expressions, parameters, filters, actions, visibility, and formatting to your report items. Note: The design surface size might be different from the page size print area you specify to print the report. Changing the size of the design surface will not change the print area of your report . Every item in a report, including the report body itself, data regions, images, and text boxes, has properties associated with it. For example, the BorderColor property for a text box shows the color value of the text box's border, and the PageSize property for the report shows the page size of the report. These properties are displayed in the Properties pane. The properties in the pane change depending on the report item that you have selected. The easiest way to create a report is to find an existing Report Part on the report server and just add it to your report design surface. You search for Report Parts in the Report Part Gallery and just drag&drop to your report. Siemens provides the following Report Parts (parameters): StartDate, EndDate, Entity, Department, DepartmentsActive, AssessmentIndex, and PatientClass. Note: When you use Report Parts you need to adjust your embedded dataset manually to support the new information added to your report definition. Groups are used to organize your report data into a visual hierarchy and to calculate totals. You can view the row and column groups within a data region on the design surface and also in the Grouping pane. The Grouping pane shows two areas: Row Groups and Column Groups. When you select a data region, the Grouping pane displays all the groups within that data region as a hierarchical list: Child groups appear indented under their parent groups. While you are designing the report (design mode) you are not working with the actual data but a representation of the data indicated by the field name or expression. When you want to see the actual data displayed in the context of the report that you designed, you can run the report to preview the data from the underlying database displayed in the report layout. Switching between designing and running your report allows you to adjust its design and see the results immediately. Before you start to create a report, it is important to identify: • What data you want to include • Where to get the different pieces of data and how they are related to one another • How you want to display the data In this section, we will be demonstrating the report creation steps using a sample report that provides an overview of all the procedures performed daily in each of your departments, organized by entity.  Click the tabs below to view the information that was gathered in order to plan our sample report: Data Fields to include: patient name, date of birth, medical record number (MRN), procedure number, accession number, department, procedure long description, and ordering doctor last name. Data Location: PatientInformation view, HistoricalProcedureInformation view, and ProcedureDetail view. Data Display: Table, ordered by entity and department that includes the total number of patients and procedures for each department, as well as the grand total number of patients and procedures over all departments. The main steps needed to create a report using Report Builder include: Connecting to the Data Source Creating the Dataset Configuring the Parameter properties Creating the Report Layout Running the report to verify layout and data presented Recommendation: Hit the save button as soon as you've changed something on your report. Using the save button during creation of the report is your first line of defense - the system will not allow you to save the report if there is incorrect data given. This could be a SQL syntax error in the dataset, or a missing comma in a parameter default setting. When you first launch Report Builder, a new report is opened by default.  If you are already viewing a report and want to create a new report, please follow the steps below: Select New from the start menu On the "New Report and Dataset" dialog select "New Report" Select "Blank Report"   Once you have opened a new report, the next step is to define where to get the data. That is, defining the Data Source. To add a Data Source to the report definition we will connect to the syngo Worklfow SLR database. Note: For all of the model SSRS reports, we retrieve data from the syngo Workflow SLR database. Siemens provides a shared Data Source for each environment (PRD, TST, TRN) at installation. Click the link below to see a step by step procedure for connecting a data source. How to add a Data Source How to add a Data Source Checklist TitleChecklist TypeChecklist ContentStep 1HTML In the Report Pane right click on Data Source and select 'Add Data Source…'. Step 2HTML Dialog 'Data Source Properties' displays with default name 'DataSource1'. Recommendation: Rename default name to actual name of your Data Source environment (PRD,TST,TRN). Step 3HTML Rename default name to TST. Recommendation: Create new reports connecting to your Test environment. Note: Make sure the radio button 'Use a shared connection or report model' (default) is selected. Step 4HTML Click on the Browse button to browse for your Data Source. Note: When you have browsed and selected a Data Source before, it will be displayed in the window below. Step 5HTMLBrowse to the TST model folder and select your Data Source TST. Step 6HTML Click 'Open' Step 7HTML The Data Source Properties dialog displays your selection. Step 8HTML If it is the first time connecting to this Data Source, test the connection by clicking on 'Test Connection'. Message dialog pops up. Note: Depending on your SQL Server setup you may be asked to enter user name and password to connect to the Data Source. Step 9HTML Dismiss the message dialog by clicking OK. Step 10HTML Click OK to close the Data Source Properties dialog. Step 11HTML Connected Data Source TST appears in the Report Pane.   Print out the document below and follow the steps on how to add a data source and save the report. Download:  Exercise #1 - Connecting a Data Source A dataset specifies the data that you want to use from a data source. The dataset includes a query that specifies a set of fields and also includes parameters, filters, and data options that specify character sensitivities. An embedded dataset is defined in and used by only the report it is embedded in. Use an embedded dataset when you want to get data from an external data source to be used only in one report. Embedded datasets are useful when you want to create a query that has no other dependencies and that you do not need to use for multiple reports. The Dataset Properties dialog is shown when you select "Add Dataset" on the Report pane. This dialog is your starting point of creating a dataset. Click the tabs below to see the different tabs and options this dialog provides. Query tab fields: 1) Name: name for the dataset; the name must be unique within the report 2a) Use a shared dataset. option to use a predefined dataset from the report server 2b) Use a dataset embedded in my report.: option to create a dataset for use only by this report 3) Data source: data source on which to base the dataset on 4) Query type: type of command or query to use for the dataset; text is the default and the only type used 5) Query: the actual SQL query statement 6) Time out (in seconds): number of seconds until the query times out; if the number is set to 0, the query does not time out Here you see the Query tab of a dataset properties dialog of an already created dataset.   The Fields tab on the Dataset Properties dialog box lists the field collection for the report dataset. The fields are automatically populated based on the fields used in your select statement. Select Options on the Dataset Properties dialog box to change data options, such as collation options and treating subtotals as detail data, for the query. The Filters tab on the Dataset Properties dialog box can be used to add, change, and delete filters for the dataset Select Parameters on the Dataset Properties dialog box to add, change, and delete query parameters.   Click the link below to view a step by step instruction on adding a new embedded dataset (first step). How to Add an Embedded Dataset - First Step How to Add an Embedded Dataset - First Step Checklist TitleChecklist TypeChecklist ContentStep 1HTML Right click on Dataset in the Report Pane and select 'Add Dataset…' Step 2HTML Dialog 'Dataset Properties' displays with default name 'DataSet1' and default selection of the radio button 'Use a shared dataset'. Step 3HTML Rename default name to DailyProcLog. Recommendation: Rename default name to a meaningful name so you can link the query information behind the dataset. Note: The system does not allow spaces in the name. Step 4HTML Select the radio button 'Use a dataset embedded in my report'. Note: Additional fields display as soon as you select the radio button. Step 5HTML In the Data source drop-down list select the data source  defined before - TST Step 6HTML As soon as a data source is selected the Query Designer button becomes active. Step 7HTML Click OK to close the Dataset Properties dialog. Step 8HTML The dataset 'DailyProcLog' appears in the Report Pane. The Report Builder provides a graphical and a text-based query designer to help you create a query that specifies the data to retrieve from your Data Source.  We will start by explaining the individual views and panes of the Query Designer GUI. The graphical query designer supports you in exploring the database views, columns, and tables.  It also interactively builds the SQL query statement based on your selections and definition.  The text-based query designer is mainly used to view the query and to test and modify the query.  The graphical query designer is divided into three areas: Explore Pane, Design Area and Results Pane. Hover over the image below for more information. The Explore Pane allows you to browse through the tables and views that are available on the database you have been connected with and check the fields you would like to include in your dataset. Expand the views and tables to see the individual columns/database fields. The views and tables are organized in an alphabetical order. Within a view the columns  are organized as such that the primary keys are listed on top and after that all other columns in alphabetical order. In the Design Area, the Selected fields window displays the database fields you have selected in the Explore Pane. The fields that are displayed in this pane become the field collection for the report dataset and build your SQL SELECT statement. Hover over the image below for more information. When you select database fields from different views and/or tables, you need to define the relationship between them. That is,  joining these views/tables using their primary key database field(s). Relationships specified in this pane are used to generate an SQL FROM clause. For information on how to setup relationships, please see the section in this training titled: "Create Relationships (FROM)". Hover over the image below for more information. The Applied Filters pane displays the criteria that are used to limit the number of rows of data that are retrieved at run time. Criteria specified in this pane are used to generate an SQL WHERE clause. When you select the parameter option, a report parameter is automatically created. Report parameters that are based on query parameters enable a user to specify values for the query to control the data in the report. Hover over the image below for more information. When you test the query within the Query Designer, the results will be displayed in the Query Results pane. Note: The Query Result pane is by default collapsed, click on the expand/collapse icon to display this pane. When you create a new dataset, the graphical query designer opens by default. To switch to the text-based query designer, click the Edit As Text toggle button on the toolbar. The text-based query designer presents two panes: the Query pane and the Result pane. Note: After you edit a query in the text-based query designer, you can no longer use the graphical query designer. The query will then always open in the text-based query designer. Hover over the image below for more information. We are going to create a daily log report using the following database fields: PatientName (PatientInformation) DateOfBirth(PatientInformation) MRN (PatientInformation) ProcedureNo (HistoricalProcedureInformation) acc_itn (HistoricalProcedureInformation) OrderingDocLastName (HistoricalProcedureInformation) ProcedureLongDescription (ProcedureDetail) Click the link below to view a step by step instruction of this procedure How to Select Database Fields How to Select Database Fields Checklist TitleChecklist TypeChecklist ContentStep 1HTML Right-mouse click on the DailyProcLog dataset in the Report pane and select 'Dataset Properties'. Step 2HTML The Dataset Properties dialog displays. Step 3HTML Click on the Query Designer button. Step 4HTML The graphical query designer opens in its default layout. Note: Depending on the set up you may be asked to enter user name and password first before the Query Designer dialog opens. Step 5HTML In the Explorer Pane expand the Views and go to the PatientInformation view Step 6HTML Expand the PatientInformation view and select  Patient Name, Date of Birth, and Medical Record Number (MRN) Note: The views are ordered alphabetically Step 7HTML Go to HistoricalProcedureInformation view and select Procedure Number, Accession Number, Department and Ordering Doctor Last name Step 8HTML Go to ProcedureDetail view and select Procedure Long Description. To join 3 views we need to create 2 joins: PatientInformation - HistoricalProcedureInformation HistoricalProcedureInformation - ProcedureDetail Click the link below to view a step by step instruction of this procedure. Create Relationships Multiple Views - Part 1 Create Relationships Multiple Views - Part 1 Checklist TitleChecklist TypeChecklist ContentStep 1HTML Click on the collapse/expand icon to expand the Relationship pane (per default it is collapsed). Step 2HTML The Relationship pane is expanded, showing information of views based on the 'Auto Detect' function of the tool. Step 3HTML Deselect 'Auto Detect' (per default it is selected).  Note: Auto Detect is part of the tool that specifies whether relationships are automatically detected from foreign keys in database tables. syngo Workflow SLR database does not contain such foreign keys needed, hence auto detection needs to be turnd off. Step 4HTML The Relationship pane is empty. Step 5HTML Click the 'add relationship' icon. Step 6HTML A blank row is added to the relationship pane. Note: As default Join Type 'Inner' is displayed, which is correct for the joins we are going to establish. Step 7HTML Click in the blank Left Table field - the views selected in the current query are shown and select the first view: PatientInformation. Step 8HTML Left Table column shows PatientInformation. Step 9HTML Click in the blank Right Table field and select HistoricalProcedureInformation Step 10HTML Right Table column shows HistoricalProcedureInformation. Step 11HTML Double-click the Join Fields field or click on Edit Fields to define the joining key fields for the two views. Step 12HTML The Edit Related Fields dialog displays. Step 13HTML Click the 'add field' icon Step 14HTML A blank row is added to the Edit Related Fields dialog box.  Note: As default Operator '=' is displayed, which is correct for the joins we are going to establish. Step 15HTML Click the Left Join Field and select 'pat_itn' from the left table/view that is PatientInformation.  Note: pat_itn is the primary key for joining the PatientInformation view to the HistoricalProcedureInformation view Create Relationships Multiple Views - Part 2 Create Relationships Multiple Views - Part 2 Checklist TitleChecklist TypeChecklist ContentStep 16HTML Click the Left Join Field and select 'pat_itn' from the right table/view that is HistoricalProcedureInformation. Step 17HTML Join conditions are context-dependent. Hence we need to establish a second relation between the two views:  You want to display procedures for a patient from a particular entity to return data only for that entity. Otherwise, if joining only on pat_itn, all procedures for a patient would be listed regardless of entity.  Click on the 'add fields' icon to enter a second row. Step 18HTML Select in the Left Join Field and the Right Join Field the 'Entity' column for both the views. Step 19HTML Click OK to close the Edit Related Fields dialog. Step 20HTML The defined field relations are displayed in the Relationship pane. Step 21HTML Click on the Add Relationship icon to add a second row to join in the third view.  Note: For this view relation two columns are identified as primary key to get unique data - ProcedureNo and Department. Step 22HTML Click in the blank Left Table field and select the HistoricalProcedureInformation view. Step 23HTML Click in the blank Right Table field and select ProcedureDetails view. Step 24HTML Open the Edit Related Fields dialog by double-clicking the Join Fields field or selecting Edit Fields button. Step 25HTML Click the 'add field' icon to add a blank row. Step 26HTML Click the Left Join Field and Right Join Field and select 'ProcedureNo' as key for both views. Step 27HTML Click the 'add field' icon to add a second row. Step 28HTML Click the Left Join Field and Right Join Field and select 'Department' as key for both views. Step 29HTML Click OK to close the Edit Related Fields dialog. Step 30HTML The relationships display in the Join Fields fields in the Relationship pane, and you are finished making the relationships. At this point you have created the SELECT and FROM clauses for the daily log embedded dataset. You can add filters to limit the data retrieved in a query. Filters are the WHERE clause in a query statement. Filter values can be specified within the query or they can be used as parameters that prompt for values to be entered when the report is run. Filter/ Parameters for daily log report: Start date End date Entity and Department Click the link below to view a step by step instruction on this procedure.   How to Define Filters and Parameters - Part 1 How to Define Filters and Parameters - Part 1 Checklist TitleChecklist TypeChecklist ContentStep 1HTML In the Applied Filters pane in visual Query Designer, click the Add Filter icon. Step 2HTML A row is added to the pane and, by default, the first field listed in the Selected fields pane (in this example, MRN) is displayed in the Field name field. Step 3HTML Click the Field name field (MRN). This list of current views is displayed.  Note: Because MRN is displayed the corresponding view is PatientInformation. Be aware that you have to change the view to continue. Step 4HTML Select the field Entity from the HistoricalProcedureInformation view. Step 5HTML Entity is displayed as Field name. Step 6HTML Click the Operator field and select is any of (which indicates that more than one entity can be selected). Step 7HTML The operator 'is any of' is displayed. Step 8HTML Keep the Value field as 'none' and check the Parameter option to specify that the filter value can be entered as a variable when the report is run. Step 9HTML Click on the 'add filter' icon to add a second row. Step 10HTML Click the Field name field and select Departments from the HistoricalProcedureInformation view. Step 11HTML Click the Operator field and select is any of. Step 12HTML Keep the Value field as 'none' and check the Parameter option. Step 13HTML Click on the 'add filter' icon to add a third row. Step 14HTML Click the Field name field and select ProcDateTime from the HistoricalProcedureInformation view. Step 15HTML Click the Operator field and select is more than or equal to (which will indicate the start date). Step 16HTML Keep the Value field as 'none' and check the Parameter option. Step 17HTML Click on the 'add filter' icon to add a forth row. How to Define Filters and Parameters - Part 2 How to Define Filters and Parameters - Part 2 Checklist TitleChecklist TypeChecklist ContentStep 18HTML Click the Field name field and select ProcDateTime from the HistoricalProcedureInformation view.select field name. Step 19HTML Click the Operator field and select is less than or equal to (which will indicate the end date). Step 20HTML Keep the Value field as 'none' and check the Parameter option. Step 21HTML Click OK to close the Query Designer dialog. Step 22HTML The Dataset Properties dialog displays with the SQL statement in the Query field. Step 23HTML Click OK to save the query and to close the Dataset Properties dialog. Step 24HTML The dataset and its containing fields are displayed in the Report Pane. Test your query by clicking on the '!' in the text-based query designer.   Click the link below to view a step by step instruction on how to test your query.   How to Test the Query How to Test the Query Checklist TitleChecklist TypeChecklist ContentStep 1HTML Open the graphical query designer for the dataset DailyProcLog. Step 2HTML Click the 'Edit as Text' button. Step 3HTML The text-based query desinger displays with the query highlighted by default. Step 4HTML Click the Run ('!') button to run query. Step 5HTML The Define Query Parameter dialog displays, prompting you to enter the parameter values to be used for this test run. Step 6HTML Enter parameter values that will enable you to run the query and view the results conent. Step 7HTML Click OK to execute the query. Step 8HTML The query results display in the lower pane. Note: When the column headers are displayed without rows of data is an indicator that the query syntax is correct, not necessarily the query itself. Step 9HTML Click on 'edit text' button again to get back to graphical Query Designer window. Step 10HTML The graphical query designer displays. Step 11HTML Click OK in the graphical query designer to save the query and to get back to Query Properties Dialog. Step 12HTML Click OK to save the query and to close the Dataset Properties dialog.  Note: Don't forget to save your progress.     Print out the document below and follow the steps on how to add a data source and save the report. Download:  Exercise #2 - Creating an Embedded Dataset Siemens provides the following shared datasets at installation: Entity - gets the valid entities in your enterprise. AllEntities - gets all the entities in your enterprise that are configured in the system. Departments - gets the departments for the selected entity or entities. DepartmentsActive - gets the departments for the selected entity or entities that are set to access level < 9 AssessmentIndex - gets all BIRADS 5 assessment categories; numbered 0 through 6 (BIRADS 5 = Breast Imaging Reporting and Data Systems, 5th edition) PatientClass - gets all patient classes configured in the system (for example, outpatient, inpatient, emergency).   How to Add a Shared Dataset How to Add a Shared Dataset Checklist TitleChecklist TypeChecklist ContentStep 1HTML In the Report Pane right click on Datasets and select Add Dataset. Step 2HTML The Dataset Properties dialog pops up. Default name is displayed and the radio button 'use a shared dataset' is selected. Step 3HTML Rename the default name to the actual name of dataset you are going to add - Entity.  As we want to add a shared dataset the default radio button selection is correct. Step 4HTML Click on the Browse button to browse for the location of the shared dataset. Step 5HTML The Select Dataset dialog box displays. Step 6HTML Browse for the shared dataset Entity in the TST model folder. Step 7HTML Select the DataSets folder. Step 8HTML Select the dataset 'Entity' and click Open or double-click the dataset. Step 9HTML You get back to the Dataset Properties dialog. Entity dataset is shown in the selection box. Step 10HTML If not already selected, select the Entity shared dataset and click OK. Step 11HTML The shared dataset is added to the report pane.  Note: The shared attribute is indicated by the arrow icon. Step 12HTML Repeat the steps to add the shared dataset DepartmentsActive. Print out the document below and follow the steps on how to add the shared datasets Entity and DepartmentsActive. Download:  Exercise #3 - Adding a Shared Dataset The next step is to verify the parameter settings and modify if desired. Adjusting parameter settings includes: Determine the order sequence of the parameters Define the available values Set default values In the Report Pane the current order of the parameters is entity, department, start date, end date; if you want the user to select first the date range before entity/department, you need to bring the date parameters on top - making sure that start date is listed before the end date, and entity is still listed before department. Select ProcDateTime paramter Click the up-arrow button till date is on top Select ProcDateTime2 parameter Click the up-arrow button till date is below start date   The Parameter Properties dialog includes four tabs: General, Available Values, Default Values and Advanced.  Click through the tabs below to get more information for each of the properties tab of this dialog. General allows you to change parameter value options such as the parameter name (Name), the text that appears at the prompt (Prompt), and the type of data represented by the parameter (Data type). In addition, you can allow parameters to return blank, null, or many values, and to display the parameter on the report toolbar (visibility). Note: The parameter data type must match the data type of the field in the dataset. Data type determines how a user enters parameter values on the report viewer toolbar, as described in the following list: Boolean. The user selects True or False from a radio button. DateTime. The user selects a date from a calendar control. Integer, Float, and Text. The user types values in a text box. Select Available Values on the Report Parameters dialog box to choose a set of values that are available for the report parameter and how users select them. None: Set as Default; the user need to enter values manually. Specify values: Define a value or a set of values for the report parameter; the user will be able to choose from this values. Get values from a query: Retrieve a value or set of values from a data source; the user will be able to select from a drop-down list of values available in your data source (e.g. list of departments). Note: Depening on which option you select the information displayed on the dialog will adjust.   Select Default Values on the Report Parameters dialog box to choose a default value for the selected parameter from a query or from a static list. Default values can be changed any time by the user. No default value: Set as default; no default value will be displayed. Specify value: Choose a value or a set of values for the default parameter; the parameter is prompted with this default values defined. Get values from a query: Retrieve a default value or set of values from the data source; the parameter is prompted with the default values defined Note: Depending on which option you select the information displayed on the dialog will adjust. Advanced allows you to set a value that indicates whether this parameter directly or indirectly affects the data in a report. On the report server, this value is used to determine cache options for reports and report data. Automatically determine when to refresh: Set as default; report processor automatically determines a setting for this value - the value is True if dataset query with a direct or indirect reference to this parameter, or if the report has subreports is detected Notify me when this report part is updated on the server: When the parmater is based on a report part that resides on the report server, every update to this report part will affect the reports this report part is used; marking this checkbox in the Report Builder application you will get notified if someone has made updates to the Report Part. Setting date default values will allow the end user to run the reports more quickly. Depending on the content of your report you can set the timeperiod the report ususally runs for accordingly.  Note: To define defaults for parameter you need to enter Visual Basic syntax in the Expression field (see also Expression chapter). Setting defaults is the simple form of expressions.  For further common default settings please refer to SSRS User Guide Best Practices chapter. Click the link below to view a step by step instruction on how to set defaults for date parameters. How to Set Defaults for Date Parameters - Part 1 How to Set Defaults for Date Parameters - Part 1 Checklist TitleChecklist TypeChecklist ContentStep 1HTML In the Report Pane right click on parameter ProcDateTime and select Parameter Properties Step 2HTML On the Parameter Properties dialog General Tab, The Prompt field shows the name the end user will see when running the report. Step 3HTML For easier understanding change the name from Proc Date Time to Start Date. Step 4HTML Select the tab 'Default Values'; per default 'No default value' is selected. Step 5HTML Select 'Specify values'; the 'add' window displays. Click the "Add" button. Step 6HTML An empty line appears in the list window with a value of 'Null'.  Click on the 'fx' button. Step 7HTML The Expression dialog displays. Step 8HTML In the category tree expand 'Common Functions'. Step 9HTML Under the 'Common Functions' select 'Date & Time'; the items for this category are displayed. Step 10HTML To set up a date default the item 'DateAdd' has to be used.  Note: Selecting an item displays the description and examples of the syntax. Step 11HTML Double-click on the category item 'DateAdd' and the syntax for DateAdd is displayed in the code window.  Note: Please note that the system does not automatically close the parenthesis. Step 12HTML Complete the format for the default of 1 day before today: =DateAdd("d",-1,Today()).  Note: Please be aware of the closing parenthesis. Step 13HTML Click OK to close the Expression dialog. Step 14HTML Click OK to close the Properties dialog. Step 15HTML Changes made are not visible in the Report Data pane. Step 16HTML To set the default for the end date, repeat the steps for the parameter ProcDateTime2 in the Report Pane. Step 17HTML Parameter Properties dialog displays with tab 'General'. How to Set Defaults for Date Parameters - Part 2 How to Set Defaults for Date Parameters - Part 2 Checklist TitleChecklist TypeChecklist ContentStep 18HTML The Prompt field shows the name the end user will see when running the report. Step 19HTML For easier understanding change the name from Proc Date Time2 to End Date. Step 20HTML Select the tab 'Default Values'; select 'Specify Values', add an empty row ('Add'); select the 'fx' button. Step 21HTML The Expression dialog displays. Step 22HTML In the category tree select 'Common Functions' - 'Date & Time' and double-click on the category item 'Today'. Step 23HTML The syntax for Today is displayed in the code window.  Note: Please note that the system does not automatically close the parenthesis. Step 24HTML Enter the closing parenthesis. ')' Step 25HTML Click OK to close the Expression dialog. Step 26HTML Click OK to close the Properties dialog. Step 27HTML Changes made are not visible in the Report Data pane. Step 28HTML Hit the 'Run' button to verify the changes. Step 29HTML The parameters are shown with the new prompt name and the date default settings. After you create a report parameter, you can specify a list of available values to display to the user. An available values list limits the choices a user can make to only valid values for the parameter.  Available values appear in a drop-down list next to the report parameter on the toolbar when the report runs. Report parameters can represent one value or multiple values. For multiple values, the top of list begins with a Select All feature so the user can select or clear all values with a single click.  You can provide a static list of values or a list from a report dataset. Click the link below to view a step by step instruction on how to provide a drop-down list of values from a shared dataset. How to Define Multi-Value Parameters How to Define Multi-Value Parameters Checklist TitleChecklist TypeChecklist ContentStep 1HTML In the Report Pane right click on parameter Entity. Step 2HTML Select Parameter Properties. Step 3HTML The Parameter Properties dialog displays, showing the content of the General tab. Step 4HTML In case 'Allow blank values' is checked, uncheck it. For entities a value has to be provided. Step 5HTML Select the tab 'Available Values'; per default 'None' is highlighted. Step 6HTML Select 'Get values from a query'; further setting options display. Step 7HTML From the 'Dataset' drop down list select the shared dataset 'Entity'. Step 8HTML From the 'Value field' drop down list select the field 'Entity'.  Note: The field 'Entity' is the value that is used in the embedded dataset; if you want to change it would you need to adjust the dataset as well. Step 9HTML From the 'Label field' drop down list select the field 'Entity'.  Note: If you want to use the long description of the Entity to be displayed as selectable item in the report parameter, please refer to the SSRS User Guide Best Practise chapter on how to change the dataset to support long description display. Step 10HTML Click OK to close the Properties dialog. Step 11HTML To set the available values for the department parameter repeat the steps - right click on parameter Department. Step 12HTML The Parameter Properties dialog displays, showing the content of the General tab.  Note: Verify that the checkbox for 'Allow mulitple values' is checked and 'Allow blank values' is unchecked. Step 13HTML Select the tab 'Available Values'; per default 'None' is highlighted. Step 14HTML Select 'Get values from a query'. Step 15HTML Select the following: Dataset: DepartmentsActive Value field: Department Label field: Department Step 16HTML Click OK to close the Properties dialog.   Print out the document below and follow the steps on how to adjust Parameter Properties. Download:  Exercise #4 - Adjusting Parameter Properties At this point when running the report, parameter fields are displayed and can be selected. That means, if you would click on the the 'Run' button, the query would be executed with the parameters entered, but nothing would be displayed.  Displaying the query results is the next part of creating a report - defining the report layout by inserting a table. A table is a data region that presents data row by row. Table columns are static: you determine the number of columns when you design your report.  Table rows are dynamic: they expand downwards to accommodate the data.  You can add groups to tables, which organize data by selected fields or expressions. Click the link below to view a step by step instruction on creating a table data element. How to Insert a Data Element - Table How to Insert a Data Element - Table Checklist TitleChecklist TypeChecklist ContentStep 1HTML Add a table to the design surface from the Insert tab on the ribbon. Step 2HTML Click on 'Table' and select 'Table Wizard…'. Step 3HTML The dialog 'New Table or Matrix' opens:  The first window of this dialog displays: Choose a dataset. It lists the datasets you have been working with for this report - embedded, as well shared datasets. Step 4HTML Select the embedded dataset previously created (DailyProcLog). Step 5HTML Click next. Step 6HTML The second window displays: Arrange Fields.  This window allows you to arrange the database fields defined in the dataset selected in rows, columns, or both. Here you define the structure of your table, as well the values you want to display.  Note: In our example we only define Row groups and Values, as the columns will be static. Step 7HTML Select the field you want the data to be grouped by and drag it to the 'Row groups' field. That is: Department. Step 8HTML Choose the data fields you want to display in  columns and drag them to the 'Value' field.  That is: MRN, PatientName, BirthDateUS, ProcedureNo, LeftRight, OrderingDocLastName, acc_itn, ProcedureLongDescription. Step 9HTML For database fields that are defined as integer fields the application automatically add the 'sum' aggregation to it (e.g. acc_itn). You can remove it by open drop down list and uncheck it. Step 10HTML Re-arrange the order of the value fields as desired. Note: Changing the order at this point requires less steps than later on when the table is inserted. Step 11HTML Click next. Step 12HTML The third window displays: Choose the layout.  In this step you define where the totals and subtotals shall appear. Step 13HTML Uncheck the check box 'Expand/collapse groups', otherwise the data will be grouped that way that you have to click on the expand button to see the details. Step 14HTML Click next. Step 15HTML The forth window displays: Choose a style.  Recommendation: Choose a color over 'generic'. By using a colored table it is easier to distinguish the groups, hence in which row the sub-totals will be counted, and which row has to be used to count the grand total. Step 16HTML Click Finish. Step 17HTML The table is inserted into the report design area. Print out the document below and follow the steps on how to insert a Table. Download:  Exercise #5 - Inserting a Table Using the formatting tools on the home ribbon, you can modify the appearance of your display text. You can adjust titles and headings and adjust formats, colors and allignements. Note: Do not to change the name of a database field  (indicated in [] brackets). Text within these brackets is a placeholder for the value of the database field to be displayed when the report is run.   You can insert text boxes to add free text, you can insert images. The system provides several built-in fields that are commonly used in reports such as execution time, page number, etc. By drag&drop them from the Report Pane to the Design Area you insert that information to your Report Layout.   Please refer to the WBT 2 for detailed steps how to format the layout. You can use expressions to vary and manipulate the content or appearance of a report.  Expressions are used throughout the report definition to specify or calculate values for parameters, queries, filters, report item properties, group and sort definitions, text box properties, and so on. You can set an expression for any property in a dialog box that displays the expression (fx) button or in a drop-down list that displays . Expressions are written in Microsoft Visual Basic (not SQL!). An expression begins with an equal sign (=) followed by a combination of references to built-in collections such as dataset fields and parameters, constants, functions, and operators. Note: Any incorrect syntax or definition of expressions will cause an error message as soon as you try to save the report. Unless the issue with the expression is resolved, the report cannot be saved. Thus the recommendation to hit the save button every time you made any changes to expressions. Previously we used an expression to define the default for Start and End Date of the Parameters itself. Now we are going to use expression to format the query result so the information is displayed as desired. During the layout definition, we had dragged the date parameter placeholders into the header of the report to indicate the timeframe the report was run for. As date / time fields in the database always include a timestamp as well, the idea is to format the outcome to  show the date only and to show it in the US format = month/day/year. This is done via an expression in the properties dialog of the placeholder on the report itself.   How to Format Date/Time Information How to Format Date/Time Information Checklist TitleChecklist TypeChecklist ContentStep 1HTML Double-click on Start date place holder (ProcDateTime) in the header of the report. Step 2HTML The Placeholder Properties dialog opens. Step 3HTML On the General tab click the 'fx' button besides the 'Value' field. Step 4HTML The Expression dialog displays.  Note: Each database field has it's own syntax which can be translated to a so called simple expression. That's why the code window already displays the syntax Parameters!ProcDateTime.Value. Step 5HTML In the code window put the cursor after the equal ('=') sign. Step 6HTML In the category tree select 'Common Funtions' - 'Text'. Step 7HTML Double-click on the category item 'Format'. Step 8HTML The code window displays now: =(FormatParameters!ProcDateTime.Value To finish the syntax you need to define what field you want to format (ProcDateTime), and how the format should look like (MM/dd/yyyy).  Note: MM = month in 2 digits / dd = days in 2 digits / yyyy = year in 4 digits.  In Visual Basic capital M refers to month, small m refers to minute. Step 9HTML Complete the syntax as follow: =Format(Parameters!ProcDateTime.Value, "MM/dd/yyyy")  Note: Don't forget to enter the closing paranthesis. Step 10HTML Click OK to close the Expression dialog. Step 11HTML The Value field shows now <>. Step 12HTML Click OK to close the Properties dialog. Step 13HTML In the report header the placeholder text box displayes now <>. Step 14HTML Repeat the steps for the End Date (ProcDateTime2). Print out the document below and follow the steps on how to use Expressions for Formatting Dates. Download:  Exercise #6 - Using Expressions for Formatting Dates Some expressions are a MUST-DO to get the correct information displayed. Selecting multiple values for a parameter is translated in a comma-delimited string, so the syngo Workflow SLR database can understand the request. To show the correct information on the report, this string needs to be translated back, so the SSRS component can display it correctly. During the layout definition, we had dragged the Entity and Department parameter placeholders into the header of the report to indicate the selection the report was run for. The formatting is done via an expression in the properties dialog of the placeholder on the report itself. How to Format Multi-Value Parameter Information How to Format Multi-Value Parameter Information Checklist TitleChecklist TypeChecklist ContentStep 1HTML Double-click on Entity place holder in the header of the report. Step 2HTML The Placeholder Properties dialog opens. Step 3HTML On the General tab click the 'fx' button besides the 'Value' field. Step 4HTML The Expression dialog displays with the simple expression Parameters!Entity.Value in the code window. Step 5HTML In the code window put the cursor after the equal ('=') sign. Step 6HTML In the category tree select 'Common Funtions' - 'Text'. Step 7HTML Double-click on the category item 'Join'. Step 8HTML The code window displays now: =Join(Parameters!Entity.Value To finish the syntax you need to define what field you want to format (Entity), and how the format should look like (","). Step 9HTML Complete the syntax as follow: =Join(Parameters!Entity.Value, ",") Note: Don't forget to enter the closing paranthesis. Step 10HTML Click OK to close the Expression dialog. Step 11HTML The Value field shows now <>. Step 12HTML Click OK to close the Properties dialog. Step 13HTML In the report header the placeholder text box displays now <>. Step 14HTML Repeat the steps for the Departments.   Print out the document below and follow the steps on how to use Expressions for Formatting Multi-Valued Data. Download:  Exercise #7 - Using Expressions for Formatting Multi-Valued Data Per definition an aggregate function in computer sience is a function that calculates a single result from a collection of input values. That means, by using an aggregate function we are going to calculate a value by using the result of the query. To include aggregated values in your report, the tool provides built-in aggregate functions in the Expressions dialog ('Common Functions' - 'Aggregate'). When you select a function in the Item pane, a description and example appear. Click the link to view a step by step instruction on calculating aggregations or totals.  In this example we will be calculating the total and grand total number of procedure and patients in the table where the total number is referring to the number per department, and the grand total is referring to the overall number for the timeframe selected. How to Calculate Aggregations - Part 1 How to Calculate Aggregations - Part 1 Checklist TitleChecklist TypeChecklist ContentStep 1HTML The system automatically creates one row for totals and one row for grand totals. As we are going to calculate two different numbers (patients and procedures) we need first to insert one row each: insert a row within the Department group (= totals per department), and one row outside the group (= grand totals).  Right mouse click on the department group row and select 'Insert Row' - 'Inside Group - Below'. Step 2HTML A second row for the department totals (group totals) is added. Step 3HTML Right mouse click the last row of the table and select 'Insert Row' - 'Below'. Step 4HTML A second row for the grand total is added. Step 5HTML Remove the Sum calculation for acc_itn, which was automatically added by the system, by selecting the cell and hit 'delete'. Step 6HTML Starting with calculating the number of Patients per Department, go to the first row of the department totals and hover over the cell you want to display the number. A list icon appears. Step 7HTML Click on the list icon to see all data fields available and Select the MRN field. Step 8HTML The placeholder for MRN displays. Double-click on the placeholder. Step 9HTML The Placeholder Properties dialog opens. On the General tab click the 'fx' button besides the 'Value' field Step 10HTML The Expression dialog displays with the simple expression =Fields!MRN.Value in the code window.  Note: The definition shows 'Fields' as it is a placeholder for a database field, not a parameter. Step 11HTML In the code window put the cursor after the equal ('=') sign. Step 12HTML In the category tree select 'Common Funtions' - 'Aggregate'. Step 13HTML Double-click on the category item 'CountDistinct'.  Note: CountDistinct returns a count of all unique values. As the goal is to count the number of patients, we need to make sure that patients are not counted twice. Step 14HTML The aggregation function is entered after the "=" sign and before the field name, that is the scope of the calculation. Complete the syntax by entering the closing parenthesis: =CountDistinct(Fields!MRN.Value) Step 15HTML Click OK to close the Expression dialog. Step 16HTML The Value field shows the defined aggregation. Step 17HTML Click OK to close the Properties dialog. The content of the table cells shows the aggregation function. How to Calculate Aggregations - Part 2 How to Calculate Aggregations - Part 2 Checklist TitleChecklist TypeChecklist ContentStep 18HTML Enter text that describes the value that will be displayed when running the report. Step 19HTML Continuing with calculating the number of Procedures per Department, go the second row of the department totals and hover over the cell you want to display the number. Click on list icon and select ProcedureNo. Step 20HTML The placeholder for ProcedureNo displays. Step 21HTML Double-click on placeholder to open the Placeholder Properties dialog.  On the General tab click the 'fx' button besides the 'Value' field to open the Expression dialog. Step 22HTML In the code window put the cursor after the equal ('=') sign and select the category 'Common Functions' - 'Aggregate'.  Double-click on the category item 'Count'.  Note: Count returns a count of all values. As the goal is to count the number of procedures performed we need to make sure that all procedures are counted. Step 23HTML The aggregation function is entered after the "=" sign and before the field name, that is the scope of the calculation.  Complete the syntax by entering the closing parenthesis: =Count(Fields!ProcedureNo.Value) Step 24HTML Click OK to close the Expression dialog. Step 25HTML Click OK to close the Properties dialog. Step 26HTML The content of the table cells shows the aggregation function. Step 27HTML Enter text that describes the value that will be displayed when running the report. Step 28HTML To calculate the grand total number of patients and procedures repeat the same steps by choosing cells outside the group. Step 29HTML Format the cells as desired. Print out the document below and follow the steps on how to Calculate Aggregations. Download:  Exercise #8 - How to Calculate Aggregations/Totals   The final step to a table is to make sure that the column headers are repeating on every page. How to Set Repeating Column Headers How to Set Repeating Column Headers Checklist TitleChecklist TypeChecklist ContentStep 1HTML Go to the View ribbon. Step 2HTML Mark the checkbox 'Grouping' - grouping pane displays on the bottom. Step 3HTML Mark the checkbox  'Properties' - properties pane displays on the left. Step 4HTML In the Grouping pane, click on little arrow to the right and select Advanced Mode Step 5HTML Detailed information to row and column groups displays. Step 6HTML Go to the table in the Design area and select the first column header (= Department).  In the Grouping pane - Row Groups the first entry 'Static' is highlighted in gray, in the Properties pane you see the information Data Only, Misc, Other, and Visibility. Note: If you do not see the correct information as shown in the image below, click on the Static entry in the Row Group one more time. Sometimes you have to click twice to get the right information displayed. Step 7HTML In the Properties pane under Other set the following properties: FixedData = True KeepWithGroup = After RepeatOnNewPage = True Note: When you click on the single items a short description is provided. Step 8HTML Save your report. In report design view, you are not working with the actual data but a representation of the data indicated by the field name or expression. When you want to see the actual data displayed in the context of the report that you designed, you can run the report to preview the data from the underlying database displayed in the report layout. Switching between designing and running your report allows you to adjust its design and see the results immediately.  You can run your report as often as you like while you continue to design it. Run the report to verify the data is displayed with the given parameters and verify that the layout is as desired.   When both are correct and as desired, your report is ready for distribution.   To preview your report, click Run in the Views group on the Home Ribbon. When you preview a report in run mode, it resembles a report produced in HTML, with the data from the connected database. In the run mode you can test and verify the data displayed: In general, are the data presented correct, based on the parameter settings? Are the calculations and formulas created by expressions correct? When you preview a report in run mode, it resembles a report produced in HTML. This gives you a good idea how the report looks like and you can check on typos, cut information, formatting of text and the like. To check how the report would look like on print out you can switch to print preview available in the run mode. Click the Print Preview button on the Run tab. The report will display as though it were on a physical page and you can check if the column headers are repeating, if you get all columns on one page, and that there are no empty pages generated. When you are satisfied with your report definition, the last step is to distribute the report, so it can be used by your colleagues. It is recommended to always keep a copy of the  original and working report on a safe place, not accessible by every user. Therefore, create a copy of your report. Download/Upload or perform a Save as of your report to your prodcution environment.  Note: If you have created the report referring to a datasource other than PRD, make sure you adjust the datasource using the Report Manager.   If you are interested in learning more about report builder including creating a report with more complicated SQL statements, handling more advanced expressions, and create subreports and linked reports, log on to Siemens Learning Center (siemenslearningcenter.com) and search for "syngo Workflow SLR SQL Server Reporting upgrade course" to register for this hands-on instructor led class.