Problem

Reporting Services provides drill-down report behavior, where clicking on a populated data field generates another report based on the value of the data field clicked. This post addresses how to acheive similar behavior within Excel with OfficeWriter using Reporting Sevices URL access and Excel’s HYPERLINK function.

Solution

Overview

When the OfficeWriter renderer populates the Excel report with data, it inserts new rows for each row of data. This means that all Excel formulas are updated, including the HYPERLINK function formula. This approach uses this formula update functionality and imported data values to dynamically create hyperlinks that point to the Reporting Services URLs.

For the sake of example, “SalesReport” is the original report that contains a list of invoice IDs. “InvoiceReport” is the linked report that takes an InvoiceID as a parameter.

Reporting Services URL Access

Typically, Reporting Service reports are rendered through an application, such as the Report Manager. Reporting Services also allows for rendering reports using just URLs. The basic syntax to render a report wtith a parameter is:

 http://[servername]/reportserver?/[File path to report on Report Server] &rs:Command=Render&rs:Format=[Rendering Format]&[ParamID]=[Param Value] 

The URL for rendering the “InvoiceReport” in the OfficeWriter for Excel format, with Invoice ID 12345, is as follows:

 http://myservername/reportserver?/ InvoiceReport&rs:Command=Render&rs:Format=XLTemplate&InvoiceID=12345 

Notes:

• The rendering extension for OfficeWriter for Excel is “XLTemplate.”
• For more information about SSRS URL access, read this MSDN article. Continue reading How to create a drill-down report in Excel with hyperlinks →