Creating Reports and Report Templates

about creating reports and report templates docid\ nbwtfjmfn9gmaqcwuo6smstep 1 creating a report template docid\ nbwtfjmfn9gmaqcwuo6smstep 2 uploading a report template docid\ nbwtfjmfn9gmaqcwuo6smstep 3 creating a report docid\ nbwtfjmfn9gmaqcwuo6smcreating a time based report template docid\ nbwtfjmfn9gmaqcwuo6smmodifying a report template docid\ nbwtfjmfn9gmaqcwuo6smdownloading and uploading a report template docid\ nbwtfjmfn9gmaqcwuo6smabout data security for report templates docid\ nbwtfjmfn9gmaqcwuo6sm about creating reports and report templates only report managers and administrators can create report templates you create reports in from report templates; however, the system does not support drill down reports you can create report templates using the report builder in microsoft sql server reporting service (ssrs) microsoft ssrs is integrated with to facilitate building templates in addition to using the report builder in microsoft ssrs to create reports, you can also use microsoft business intelligence studio (bids) to create reports see creating a report using microsoft business intelligence development studio (bids) docid\ oei ts0kclk97 wq9sxv for more information about using microsoft bids this documentation applies to microsoft ssrs 2008 and earlier versions after you create a report template, upload it to you can then use the report template that you created to create new reports in addition, has predefined report templates that you can also use to create reports when using the microsoft ssrs report builder, note the following if you are inactive for 180 minutes or longer, the report builder times out and displays the following error object moved reportserver logon aspx you only need to enter your credentials once per session the main steps in creating a custom report are step 1 creating a report template docid\ nbwtfjmfn9gmaqcwuo6smstep 2 uploading a report template docid\ nbwtfjmfn9gmaqcwuo6smstep 3 creating a report docid\ nbwtfjmfn9gmaqcwuo6sm step 1 creating a report template before you can create a custom report, you must have a report template the report template contains the search parameters, filters, and layout of a report when you run a report, the data is organized according to the definition of the report template report managers create report templates using the microsoft ssrs report builder, which is integrated with you can go directly to the database and build your own datasets in microsoft ssrs or you can use the microsoft ssrs report builder and build a dataset in microsoft ssrs for more information on installing the report builder, view the microsoft website at http //www microsoft com/ http //www microsoft com/en us/download/details aspx?id=29072 log in to the application as a report manager open the report templates workspace the system displays a list of report templates click new report template the system opens the microsoft ssrs report builder if this is the first time that you are using the report builder, enter your credentials by doing the following a in the application run dialog box, click run the report builder starts to download this might take a few minutes b in the connect to report server dialog box, enter the following field value username enter userid # tenant id password enter the application password c click ok the system displays the microsoft ssrs report builder from the left report data pane, from data sources , right click saasappmodel , then choose add dataset the system displays the dataset properties dialog box dataset properties dialog box click query designer the system displays the enter data source credentials dialog box enter your microsoft ssrs user name and password, then click ok the system displays the query designer dialog box right click the parameters folder and choose add parameter enter the report template parameters report parameter values do not support angle brackets (<>) microsoft has implemented this feature in microsoft ssrs for security reasons see about data security for report templates docid\ nbwtfjmfn9gmaqcwuo6sm click help on the window for more information on how to create a parameter select the fields for the report template and then design the appearance of the template a click to expand the tables folder, select the fields that are to appear on the report template, then click ok b click ok to close the dataset properties dialog box c design the template by dragging and dropping the fields you selected to the right pane d click the field that you dropped so that its edges are highlighted, right click and select insert , then insert any of the design items e click run to preview the template when you have finished designing the template, click save as and save the template locally do not click save ; click save as step 2 uploading a report template after creating a report template, upload it to the report templates workspace log in to the application as a report manager open the report templates workspace the system displays a list of report templates click upload report the system displays the upload report template dialog box for the report template name field, enter the report name for the report template parameter, select the report template file click save in the message dialog box, click ok the system displays the newly uploaded report template in the template name column step 3 creating a report after creating a custom report template, you are ready to create a report log in to the application as a report manager open the reports workspace the system displays a list of reports click new report the system displays a blank report form enter information into the fields field description report template the name of the report template to base this report on choose from the drop down list the template that you imported should automatically appear in the list display name a descriptive name that appears on the reports list description a brief description of the report category the category choose from the drop down list publish report to the roles that can view and use this report report options allow run now enables users to run the report immediately can run from run report quick action enables the report to be run from the run report quick action see run report action docid\ onqtiwdmfrqpvijlyk0ca quick action parameters when checked, the report appears in the quick action report list the report must be published to the role using the quick action can run from export data quick action enables the report to be run from the export data quick action see export data action docid\ qjqzn1pfuz azjooz3sfh parameters the report must be published to the role using the quick action parameter information automatically populated from the report template not all reports have parameter options, and they vary for each report under the hidden column, specify if the parameter is hidden from users if you select no , the parameter field is visible to the user to select when they run the report if you select yes , the parameter field is hidden to the user, although the report will run according to the parameter you set up for example, to limit a user to viewing only their incidents, create a report template that includes a parameter that limits the results to incidents where the incident owner is the logged in user or owner = $(currentloginid()) if you select yes for this parameter, a user cannot change this filter when running the report the value of the currentbrowserculture parameter must be the same as the value of this same parameter on the report server see about using last 1 year vs last 4 quarters docid\ nbwtfjmfn9gmaqcwuo6sm for more information about the parameters schedule information no schedule does not run the report on a schedule run once on see scheduling reports docid\ wh08aszz 1ncrdijfu5kp for information on using the scheduler time zone see scheduling reports docid\ wh08aszz 1ncrdijfu5kp for information on using the scheduler daily at see scheduling reports docid\ wh08aszz 1ncrdijfu5kp for information on using the scheduler weekly on see scheduling reports docid\ wh08aszz 1ncrdijfu5kp for information on using the scheduler monthly (by week) on see scheduling reports docid\ wh08aszz 1ncrdijfu5kp for information on using the scheduler monthly (by day) on see scheduling reports docid\ wh08aszz 1ncrdijfu5kp for information on using the scheduler start see scheduling reports docid\ wh08aszz 1ncrdijfu5kp for information on using the scheduler end see scheduling reports docid\ wh08aszz 1ncrdijfu5kp for information on using the scheduler time zone see scheduling reports docid\ wh08aszz 1ncrdijfu5kp for information on using the scheduler distribution information report format the format of the report choose from the drop down list available formats are acrobat (pdf file) csv (comma delimited) xml file with report data embedded html microsoft excel tiff file microsoft word no distribution does not automatically distribute this report download makes this report available for download from the report link in the workspace of the user email sends the report by email enter the email addresses, subject line, and descriptive text click the preview icon to preview the report click the save icon the system displays the report on the report list about using last 1 year vs last 4 quarters if you are using a report template, such as the executive scorecard report template, in the parameter information section, there are fields where you can specify the time report template with time parameters the system returns different results based on the values that you enter for example, entering 1 in the last field and selecting year from the second field is not the same as entering 4 in the last field and selecting quarters in the second field the system calculates quarters and years differently whenever you choose last year , last quarter , last month , last week , or last day , the system ignores the current year, quarter, month, week, or day example 1 if you run the report on january 8, 2014, then entering 4 in the last field and selecting quarters in the second field displays information from 4/1/2013 to 1/8/2014 last 4 quarters = \[current quarter (1/1/2014 1/8/2014 (today) \[1st quarter]] \+ \[quarter 10/1/2013 12/31/2013 (2nd quarter)] \+ \[quarter 7/1/2013 9/30/2013 (3rd quarter)] \+ \[quarter 4/1/2013 6/30/2013 (4th quarter)] example 2 if you run the report on january 8, 2014, then entering 1 in the last field and selecting year in the second field displays information from 1/1/2014 to 1/8/2014 last 1 year = current year 1/1/2014 1/8/2014 (today) creating a time based report template log in to the application as a report manager open the report templates workspace the system displays a list of report templates click new report template the system opens the report builder make the parameters that govern the time ranges visible by doing the following a from the report data pane, click the last parameter this is the parameter that governs report intervals the system displays the report parameter properties dialog box b for the select parameter visibility field, select visible when running the report, this parameter detects the report runtime defined in the query c click ok d repeat steps a c for the datarangeoptions and browsertimezoneoffset parameters create the interval report query, including the date ranges (start and end date) for example, create the following query select incidentnumber,createddatetime from incident where createddatetime>(@startdate) and createddatetime <(@enddate) from the dataset properties parameters pane, set the expressions for the values value expression @startdate =code getstartdatetime( parameters!datarangeoptions value, parameters!browsertimezoneoffset value, parameters!last value ) @enddate =code getenddatetime( parameters!datarangeoptions value, parameters!browsertimezoneoffset value ) complete the template design, and click ok when you run the report, the startdate and enddate query parameters return the dates in the format defined by the user (year, month, quarter, etc ) modifying a report template after creating a report template, you might want to edit it or create a duplicate report template log in to the application as a report manager open the report templates workspace the system displays a list of report templates click the edit icon next to the report template to edit the system launches the report builder log in to the report builder and select the report template to modify make changes as needed click save as to save your report template locally or click save to overwrite the existing report template the edit option is disabled in cloud environment, you can only edit in the on premise environment downloading and uploading a report template you can save a report template from one instance of to use in another instance for example, if you create report templates in your staging environment, you can easily save the templates to your local machine and upload them to your production environment without logging into microsoft ssrs log in to the application as a report manager open the report templates workspace the system displays a list of report templates click the download icon next to the report template to save the system downloads the report template to your downloads folder log into a new instance of and open the report templates workspace again click upload report the system displays the upload report template dialog box for the report template name field, enter the report name for the report template parameter, select the report template file click save in the message dialog box, click ok the system saves the report template and it is ready to use about data security for report templates when creating a report template, you can add parameters to the template to make the report data secure or to give users options in selecting the data that they can view when you use the report template to create a report, you can make these parameter fields hidden or visible for reports with restricted data, you can hide the parameter fields for example, the owner =$(currentloginid()) parameter restricts the report data to the logged in user, so the user can only view a report of records where the logged in user is the owner if you hide this parameter, the user cannot change it in the same way, you can restrict reports to generate data limited to the team or organization unit of the logged in user for reports where users can make selections to the report data, make the parameter fields visible for example, make the start date and end date parameters visible to allow users to select a date range for the report data parameters that specify context (for example, logged in user) do not work for scheduled reports