Modifying an existing SSRS report

You may in certain situations want to modify an existing SSRS report. To add or remove information from the report, you must download its RDL file and make adjustments to the SQL that the report uses.

Report changes are made in the using one of SQL's reporting tools. iMIS SSRS reports are compatible with all versions of SQL's reporting tools that iMIS also supports.

  1. Find the report you wish to modify under Continuum. Find the template (RDL file) used by this report.

    2. Note: To determine which template is used, select the report and click Edit to open the report definition. Here you can click on the Sources tab to see which template and IQA query the report uses.

  1. Select the report template from the Templates folder, and click Download. The .RDL file will download to your local workstation.
  2. Find the IQA query that the report uses (as noted in Step 1). 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. You can select the query and click Edit, which will open the query definition. From there you can click Save As to create a copy of the query and save it with a different name.
  3. Modify your new copy of the query to add any additional sources, display fields, filters, parameters, or sorting as required for your new report.
  4. Save this query’s Report Definition to your workstation. Click Report, then click Save Report Definition to Workstation.

  1. Launch your version of SQL BIDS, and create a new project. Select the Report Server Project type and name your project.
  2. Under the Solution Explorer, right-click on the Reports folder and select Add > Existing Item.

  1. Select the RDL file that you saved in Step 2 above (for the original report), and click Add. Repeat this process to also add the RDL file that you saved in Step 5 above (for your revised query). Both of your report templates (RDL files) should now display in the Solution Explorer under the Reports folder.

  1. 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.
  2. Find the line that says <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 with <Field> tags before each and </Field> tags after each. 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, you would copy the section of code beginning with <Field Name=”Email”> and ending with </Field>. See highlighted text in the example below. 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.

  1. Right-click on 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.
  2. Find the line that says <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 </Field> tag that follows the last field, and before the closing </Fields></DataSet></DataSets> tags. After pasting in the code, it should look just like the same section in the XML for your new query.
  3. Right-click on the RDL for the original report and select View Designer. This will open another tab displaying the layout of the report.
  4. 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.

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

  1. Make any other layout or design changes you wish to make to the report.
  2. 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. It displays the full path to your revised RDL file.

  1. On your iMIS site, browse to the Templates folder where you wish to save the RDL file. The simplest way to do this is to just go to the Reporting directory you wish to use and open the Templates folder there.

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

  1. From either location, select New > Reporting Services Template.
  1. Browse to select your revised RDL file, enter a description, and click the Upload button. Your new RDL file should now appear in the list of templates.
  2. Move up a level to the directory that contains the reports (for example, $/Accounting/DefaultSystem/Reports). From here, select New > Reporting Services Report. Enter a name and description for your new report on the Summary tab.
  3. Go to the Sources tab and browse to find the new Reporting Services Template you just created (example: $/Accounting/DefaultSystem/Reports/Templates/YourReportTemplate.rdl). Then browse to find the new query you created in Step 3. Now your Sources tab should have both fields populated.
  1. If you wish to prompt for parameters at runtime, go to the Parameters tab and enable the Show parameters at runtime option.
  2. Click Save and browse to the appropriate directory for your new report (for example, $/Accounting/DefaultSystem/Reports), and click OK.
  3. Test your report by clicking Run at the left. You can also test by refreshing your report directory so that your new report appears, and run it from there.
  • If you want to add a new parameter to your report, just edit your IQA query. You should 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 itself for the data set and parameters needed. So if you want to add a report to your site using that Content Item, you can create it in the same way outlined above.
  • The “Report Display” Content Item looks at the RDL itself to figure out the data source, data sets and parameters needed for the report to render. So if you want to add a report to your site using that Content Item, you would need to also modify the Select statement in the code in BIDS (right above the list of fields – you would need to add any new fields and joins here).