Modifying a sample SSRS report

Do the following to modify an existing standalone SSRS report:

1. Find the SSRS report you want to modify under Reports, or in RiSE > Document system. The document type of these files in iMIS is Reporting Services Template .
2. Select the report template and click Download. The RDL file is downloaded to your local workstation.
3. Launch your version of SQL Server Data Tools and create a new project. Select the Report Server Project type and name your project.
4. Under the Solution Explorer, right-click the Reports folder and select Add > Existing Item.
Click to view image



5. Select the RDL file downloaded to your local workstation and click Add.
6. Modify the report as needed in SQL Server Data Tools.
7. On your iMIS site, browse to the folder to which you want to save the RDL file. The simplest way to do this is to go to the Reports directory you want to use.

You can also browse directly to the folder from the document system by going to RiSE > Document system (for example, for an Accounting report, browse to $/Accounting/DefaultSystem/Reports).

8. From either location, select New > Reporting Services Template. It is best to create a new document in iMIS for your updated report instead of modifying the report provided by iMIS out-of-the-box, in order to avoid your changes being overwritten on upgrade.
9. Browse to select your revised RDL file:
   1. Enter a description.
   2. Click Upload.

Your new RDL file should now appear in the list of templates.

Do the following to modify an existing IQA-SSRS report:

1. Find the SSRS report you want to modify under Reports or in RiSE > Document system. The document type of these files in iMIS is Reporting Services Template . For out-of-the-box iMIS reports, this file might be in the Reports folder, for example, $/CertificationManagement/DefaultSystem/Reports.
   

Note: To determine which template is used, select the report and click Edit to open the report definition. You can select the Sources tab to see which template and IQA query is used by the report. You can only view which template and query are being used by the report with templates that have the icon.

2. Select the report template and click Download. The RDL file is downloaded to your local workstation.
3. Find the IQA query that the report uses. Typically, the query will have the same name as the report and can be found by opening the Queries folder that appears above the list of reports.
   1. Select the query.
   2. Click Editto open the query definition.
   3. Click Save As to create a copy of the query and save it with a different name.
4. Modify your new copy of the query to add any additional sources, display fields, filters, parameters, or sorting as required for your new report.
5. Save this query’s Report Definition to your workstation:
   1. Select the Report tab.
   2. Click Save Report Definition to Workstation.
Click to view image

6. Launch your version of SQL Server Data Tools, and create a new project. Select the Report Server Project type and name your project.
7. Under the Solution Explorer, right-click the Reports folder and select Add > Existing Item.
Click to view image



8. Select the RDL file downloaded to your local workstation and click Add.

   Repeat this process to also add the query’s Report Definition that was saved to your workstation. Both of your report templates (RDL files) should now be displayed in the Solution Explorer under the Reports folder.

   Click to view image

   

9. Right-click on the RDL for your new query and select View Code. This will open the XML for the report in the left-side pane.
10. Find the line that states <DataSet Name="IMIS"> (for reports using an IQA query as the source, the DataSet name will always be "iMIS"). Just below this, you will see a list of fields. Copy the <Field>code for each new field you added to the query (these should appear at the end of the list of fields).
    For example, if you added the Email field, copy the section of code beginning with <Field Name=”Email”> and ending with </Field>. If you added multiple fields, you could select the code for all of the fields at once. Use CTRL-C or Edit > Copy to copy this section of code to your clipboard.
Click to view image



11. Right-click the RDL for the original report and select View Code. This will open the XML for that report in the left-side pane in a new tab.
12. Find the line that states <DataSet Name="IMIS"> and scroll to the end of the list of fields below it. Paste the code you just copied at the end of the list after the final </Field> tag and before the closing </Fields></DataSet></DataSets> tags. After pasting in the code, it should appear identical to the same section in the XML for your new query.
13. Right-click the RDL for the original report and select View Designer. This opens another tab displaying the layout of the report.
14. Expand the Datasets folder in the Report Data panel on the left. Next, expand iMIS. Your newly added fields should show up at the end of the list. Add your new fields to your report in the designer.

Note: If the Report Data panel is not displayed, you can open the panel by going to View > Report Data.

Click to view image



15. Make any other layout or design changes you want to make to the report.
16. When you are finished with the report, click File > Save Selected Items. Take note of the location of your revised report. The easiest way to find this is by looking at the Properties area, underneath the Solution Explorer. The full path to your revised RDL file is displayed.
Click to view image



17. On your iMIS site, browse to the Templates folder where you want to save the RDL file. The simplest way to do this is to go to the Reporting directory you want to use.

You can also browse directly to the folder from the document system by going to RiSE > Document system (for example, for an Accounting report, browse to $/Accounting/DefaultSystem/Reports/Templates).

18. From either location, select New > Reporting Services Template.
19. Browse to select your revised RDL file:
    1. Enter a description.
    2. Click Upload.

    Your new RDL file should now appear in the list of templates.

20. Move up a level to the directory that contains the reports (for example, $/Accounting/DefaultSystem/Reports).
21. Select New > Reporting Services Report.
22. (Summary tab) Enter a name and description for your new report.
23. (Sources tab) Browse to find the new Reporting Services Template you just created (example: $/Accounting/DefaultSystem/Reports/Templates/YourReportTemplate.rdl).
24. Browse to find the saved copy of the query for your existing IQA-SSRS report. Now your Sources tab should have both fields populated.
25. If you want to prompt for parameters at runtime, go to the Parameters tab and enable the Show parameters at runtime option.
26. Click Save and browse to the appropriate directory for your new report (for example, $/Accounting/DefaultSystem/Reports), and click OK.
27. Test your report by clicking Run. You can also test by refreshing your report directory so that your new report appears. Run the report.

• If you want to add a new parameter to your report, edit your IQA query. You do not need to modify the report template.
• The SSRS Report content item looks at the RDL only for the layout and looks at the IQA query for the data set and parameters needed. If you want to add a report to your site using the SSRS Report content item, you can create it in the same way outlined above.
• The Report Display content item looks at the RDL to figure out the data source, data sets and parameters needed for the report to be rendered. If you want to add a report to your site using that content item, you must also modify the SELECT statement in the code in SQL Server Data Tools (right above the list of fields; add any new fields and joins here).