Background to Solution

Many of us these days are utilizing SQL Server Analysis Services (SSAS) as our data source for many of our SQL Server Reporting Services (SSRS) reports. We have a lot of great tools for this like the MDX query builder in SSRS, however, the following combination of requirements presented a bit of a problem for me initially. I was struggling with this a few days ago, and thought I would share how I ultimately fixed the issue. I am sure there are other solutions out there, but this one seemed to be the right fit.

Requirements:

1. Data Source for SSRS report is an Analysis Services Cube
2. First Report (or Parent Report) had report parameters built on SSAS Dimension(s)
   1. One of the report parameters needed to be configured as a multi-value (multi-select) list.
3. Second Report (Child Report) needed to launch in a new browser window
4. Second Report (Child Report) had a report parameter built on a SSAS Dimension, that was also a multi-value (multi-select) list.

Solution

Requirements 1 and 2 are relatively straightforward. I simply plugged into my SSAS data source, created a data set with the Query builder, and chose the option for the dimension filter to be a parameter. The Parameter highlighted in yellow below needed to be configured as a multi-select in both the parent and child report.

The Parameter highlighted in yellow below needed to be configured as a multi-select in both the parent and child report.

In both the parent and child report, the parameter highlighted in yellow needed be configure to accept multiple values from a multi select list:

Launching a “Drill Through” or “Sub-Report” in a new browser window

Requirement #3 initially had me scratching my head. When configuring an “Action” on a textbox, you have the option to “Go to Report”, and it is relatively straightforward to configure as shown below.

The issue is that this report will render in the same browser window as the parent report that has the textbox action configured. I needed to open the report in a new window, and luckily there is a well-documented solution on how to do this by configuring the action to “Go to URL” and then using a bit of script code to launch a new browser window. You can take a look at this solution here.

So, with requirement #3 out of the way, all that was left was to be able to pass multiple values to an SSRS URL call to render the child report in a new browser window.

Format SSRS URL to accept multiple values

There are a lot of solutions out there that talk about passing multi-values to “drill through” reports when the data source of the target report is SQL Server. We can write in clauses in our stored procedures. I know we could do something similar with customer MDX, but as many of us do, I was searching for a simpler solution.

The first thing to understand is the SSRS URL format for passing multiple values to a parameter built off an SSAS Dimension. You would think the ideal way to pass multiple values would be an array, but keep in mind we are doing this through a URL call. The format for this can be found here.

SHOW URL FORMAT For multiple parameters.

To do this, I used an expression on the action of the textbox (“Go To URL”) to create the URL string. Below are some common functions used in expressions to work with multi-value parameters:

The last issue I ran into was how to enumerate the multiple values to create the correct URL string format (NOTE: the “&ParamName=” needs to be repeated for every value that was selected in the multi-value parameter). The answer again was a small bit of JavaScript code that would accept the parameter name, loop through the values, and create the string format needed as show below (ReportàReportPropertiesàCode tab):

This was modified from an original example that can be found on BOL here (Scroll down to – Examples of Referencing Parameters from Custom Code).

The final expression for the action on my textbox looks as follows:

Example:

=”javascript:void window.open(‘” & code.GetReportInNewWindow(“MyDrillThrougReport”) & code.CreateMultiValueURLString(Parameters!ParamName)&”’,’_blank’)”

Calling the CreateMultiValueURLString function I created looped through multiple values and created the URL string with a parameter name for every instance of the parameter value.

Summary

Requirements Met:

1. Data Source for SSRS report is an Analysis Services Cube
   1. Standard Fare for Reporting Services
2. First Report (or Parent Report) had report parameters built on SSAS Dimension(s)
   1. One of the report parameters needed to be configured as a multi-value (multi-select) list.
   2. Standard Fare for Reporting Services
3. Second Report (Child Report) needed to launch in a new browser window
   1. Use JavaScript in Textbox Action Expression to call drill through report in a new browser window.
4. Second Report (Child Report) had a report parameter built on a SSAS Dimension, that was also a multi-value (multi-select) list.
   1. Use JavaScript function to formate the URL string correctly for multi-value parameters.