Excel Web Access Drill-through to SSRS Report

I’ve been having fun lately with a dashboard page which has an Excel Web Access (EWA) web part connected to a PivotTable report. The PivotTable report is nothing to brag about. It displays a few KPI-related measures coming from an SSAS cube. PivotTable was converted to formulas to achieve a free-form report layout. So far, pretty straightforward stuff. That is, until the customer wanted the ability to drill through the chart to see a more detailed view from the cube.

Since to my understanding cross-EWA drillthrough is not possible because Excel Services is not URL-addressable (shame [:@]), I implemented this scenario by allowing the user to drill through from the Excel spreadsheet to an SSRS report. To implement this, I converted the Excel cell to a hyperlink which has the URL address of the SharePoint report using the Excel HYPERLINK function:

=HYPERLINK(“http://” & CUBEVALUE(“<connection name>”,”[Measures].[ServerName]”) & “:8080/ReportServer?http://” & CUBEVALUE(“<connection name>”,”[Measures].[ServerName]”) & “/Reports/ReportsLibrary/Top 50 Customers.rdl&DateCalendar=[Date].[Calendar].[Calendar Year].%26[” & PreviousPeriod & “]&rs:Command=Render”, “Growth in Customer Base”)

There are a few interesting things going on in this nasty expression that deserve more explanation. First, instead of hardcoding the server name, I added a ServerName calculated member to the cube that is mapped to the following SSAS stored procedure:

public static string ServerName() {

     return Context.CurrentServerID;

}

Second, note the URL address of the report. Since SSRS is configured for SharePoint integration, the report path points to the WSS library where the report is deployed. Finally, I had to solve the nasty issue with ampersands in the member names b/c the drillthrough SSRS report takes a member as a parameter. If I just use & the Report Server will choke b/c it will think that it is a parameter placeholder. The hack is to escape & to %26 .

Here is what the link looks when rendered in the browser.