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.

HYPERLINK function in Excel

The HYPERLINK function in Excel has two parameters: URL and display text. In this example, the URL will be the Reporting Services URL and the display text will be the invoice number.

1. Open “SalesReport” with the OfficeWriter Designer in Excel
2. Create a hidden column that will contain the invoice IDs, which will be referenced in the hyperlink. Put the data marker for the invoice ID in this column, for now, let the cell that data marker resides in be A5.
3. To create a hyperlink for each linked report, the invoice ID in the Reporting Services URL has to be hardcoded for each separate report URL. CONCATENATE will be used to combine the first part of the Reporting Services URL, which is the same for all the reports, with the specific invoice IDs, which are in the hidden column. This concatenated URL will be used to create the hyperlink.
4. In the column that will actually display the links, in the same row as the invoice ID data marker, use the HYPERLINK formula.

Example of formula that would appear in B5:

 =HYPERLINK(CONCATENTATE("http://myservername/reportserver?/ InvoiceReport&rs:Command=Render&rs:Format=XLTemplate&InvoiceID=", A5), A5) 

This will create a hyperlink that, when clicked, will make a call to the Report Server to render “InvoiceReport” with the invoice ID value from A5. The report will be streamed back to the client in a new instance of Excel.

Follow

Related posts: