Invoking Report Builder Reports With Passed Parameters

EnvisionConnect, as you probably know, leverages SQL Server's Reporting Services and Report Builder for its ad hoc reporting. This is a fantastic collaboration since it frees up Decade's teams to focus on our domain expertise (software environmental regulation and public safety) AND it provides a superior interface for casual reporting.

This post is for EnvisionConnect Admins (technical folks) who want to do a little bit more to integrate EnvisionConnect and Report Builder.

I must also acknowledge MSSQLTips and Weld County Environmental Health Services Division for inspiring me to write this post.

Let's say you've created a great Report Builder report that summarizes all the activities (e.g., inspections and other services) related to a Complaint. The report prompts for the Complaint ID, but you want that to come over automatically. You also want to invoke this report from within EnvisionConnect for the complaint you're reviewing.

Step 1 - Isolate the Report URL

As you probably know, Report Builder reports can be accessed through a web browser using Report Manager. This is wonderful since you probably have stakeholders across the enterprise that don't really need to be in EnvisionConnect, but DO need to quickly check a record. My simple report looks like this when displayed in a browser:

The intranet URL for the "Standard" view of the report is as follows:

http://envisionconnect/Reports/Pages/Report.aspx?ItemPath=%2fSummary+of+Complaint+Activities (not a real URL)

Server name: envisionconnect (your server name may be different)

Report name: Summary+of+Complaint+Activities (your report name will be different)

This URL can be shared with anybody who has permissions to the report.

The report can also be "served" up at this alternate URL:

http://envisionconnect/ReportServer?%2fSummary+of+Complaint+Activities&rs:Command=Render (not a real URL)

Server name: envisionconnect (your server name may be different)

Report name: Summary+of+Complaint+Activities (your report name will be different)

It's with this second URL that we can start to do some fancy stuff.

Step 2 - Tweak the URL to Pass Parameters and Commands

According to MSSQLTips we can augment this URL to do our bidding by passing one or more report parameters, hiding the parameter panel, or even rendering the report in a certain format.

To Specify a Report Parameter Add to the URL:

&ParameterName=ParameterValue

So, to pass a particular Complaint ID to the report, the URL would look like this:

http://envisionconnect/ReportServer?%2fSummary+of+Complaint+Activities&rs:Command=Render&ComplaintLogRECORDID=CO0034751

So, how do you get the report's Parameter Name? You can guess it... In my experience it's the Entity Name smashed up against the Field Name. But to be sure, you have to look into the report file.

To look into a report file, use these steps:

- Open the report in Report Builder.

- Choose File > Save as File and save the RDL file to your desktop.

- Open the RDL file in Notepad and search for a section titled <ReportParameters>.

You'll see the name of your parameter in this section. For my example, it looked like this:

<ReportParameters>
  <ReportParameter Name="ComplaintLogRECORDID">
    <DataType>String</DataType>
    <Nullable>true</Nullable>
    <DefaultValue>
      <Values>
        <Value>="CO0034751"</Value>
      </Values>
    </DefaultValue>
    <Prompt>Complaint Log RECORD ID</Prompt>
  </ReportParameter>
</ReportParameters>

To Hide the Parameter Panel Add to the URL:

&rc:Parameters=false

To Open in a Specific Format Add to the URL:

&rs:Format=PDF or &rs:Format=Excel

Step 3 - Configure an EnvisionConnect System Tool to Launch Your Report

From within EnvisionConnect, users and administrators can configure "System Tools." This feature adds a link to external resources such as web sites, scripts, and executables. In addition, the System Tools can reference parameters from the EnvisionConnect screen. In our example, we'll scrape the Complaint Record ID off the Enter Complaint Details page to customize the URL and get the report we want.

To configure a System Tool that invokes our custom report:

- Launch Manage System Tools from the System Administration Center.

- Click Add a System Tool to see the Enter System Tools Details page. Using the URL from the example above, my system tool configuration looks like this:

- Don't forget to replace the actual Complaint Record ID ("CO0034751") with the linking placeholder {RecordId} so the system will do the replacement for you on the fly.

After the configuration, your users should be able to see Summary of Complaint Activity under the tools.

In Conclusion

I know this was a rather technical post... but I'm pretty excited about the potential for tying Report Builder Reports and EnvisionConnect System Tools in ways that make your life easier. Enjoy!