Microsoft SQL Server Reporting Services (“SRS” or “SSRS”) have been around for the better part of two decades, first hitting the streets in early 2004. Over the past 16+ years, many articles and blog posts have been written about passing multi-value parameters between reports using a combination of the JOIN and SPLIT functions. These work great, but I ran into a specific scenario where no matter what I tried, the selected values in the top-level report did not default as selected values in the sub-report. No combination of JOIN or SPLIT would make this happen. So, I had to get creative.
Report Issue SSRS Multi-Value Parameters
We developed a report with dynamic cascading parameters, which means the selection of one or more parameters were not specified and therefore drives the availability of other parameters. Some of these parameters are multi-value parameters, and they populate correctly and then are passed to sub-reports without any issues. In fact, we do this often, as do others, so that is not unique nor is it innovative.
What is different in this scenario, though, is that the selected values in the multi-valued parameters in the top-level report are not selected by default in the sub-report, even when the two reports have the exact same default values query. It just was not happening, not matter what we tried: the available values in the multi-value parameter list were being properly passed, but the selected values in the top-level report were not being defaulted as selected values in the sub-report.
The Report Fix (Pass Multi-Variable Parameters to Sub Report)
I was able to solve this by leveraging the little-used drill-down URL action in the click-through text box with a custom expression, as shown in Figure 1.
The key here is to create a URL string with all of the default values for a multi-valued parameters in the sub-report using the selected vales of the multi-valued parameter in the top-level report. This seems daunting at first given the many possible selections for a multi-valued parameter, but since a URL is simply a string value, this can be built using custom code. Hence the reason for an expression rather than a hard-coded URL string.
Figure 2 displays the expression behind the URL action defined in the click-through text box.
=Code.makeURL (join (Parameters!asPeriods.Value, ","), Parameters!sDatabaseName.Value, Fields!CUSTNMBR.Value, Fields!CUSTNMBR.Value, Parameters!sPeriodPattern.Value, join (Parameters!asPeriods.Value, ","), Parameters!iDistSales.Value)
Figure 2 – Text Box URL Action Expression
Each report requirement is slightly unique, and my report is no different. Essentially, to explain this code requires knowledge of any proprietary needs, but in summary I passed:
- The concatenated selected periods
- A SQL database name
- A selected customer number
- A free-text period pattern
- A Boolean value, indicating whether or not to display certain sales types
For purposes of our discussion, the multi-selected period list is what need to be defaulted in the sub-report. The available period values are parsed from an entered period pattern indicating a year/month range.
As you can see, this expression invokes custom VB code to create the URL string from the provided values, and this window is shown in Figure 3. (Note: I have redacted the server name after the URL HTTP protocol identifier.)
You can get to this window by right-clicking in the SRS report body and selecting Properties.
The actual free-text code is below listed. We are harnessing the power of SRS via a URL query string and the defined SRS query parameter list to launch a new subreport based upon the selected values from a multi-valued parameters in a top-level SRS report.
public function makeURL (ByVal sForceSelections as string, ByVal sDatabaseName as string, ByVal sCustomerPattern as string,
ByVal sCustomerNumber as string, ByVal sPeriodPattern as string, ByVal sSelectedPeriods as string,
ByVal iDistSales as integer) as string
dim sRetVal as string = string.Empty
dim sBaseURL as string =
"http://SERVER_NAME/ReportServer/Pages/ReportViewer.aspx?/Sales+Reporting/Sales+Margin+by+Customer+and+Product"
sBaseURL = sBaseURL &
"&asForceSelections={0}&sDatabaseName={1}&sCustomerPattern={2}&asCustomerNumber={3}&sPeriodPattern={4}"
sBaseURL = sBaseURL & "{5}&iDistSales={6}"
dim asPeriods as string() = split (sSelectedPeriods, ",")
dim sPeriods as string = string.Empty
dim sPeriod as string = string.Empty
for each sPeriod in asPeriods
sPeriods = sPeriods & string.Format ("&asPeriods={0}", sPeriod)
next
sRetVal = string.Format (sBaseURL, sForceSelections, sDatabaseName, sCustomerPattern, sCustomerNumber,
sPeriodPattern, sPeriods, iDistSales)
return sRetVal
end function
Figure 4 – Custom SRS VB Code
This code is likely not directly usable as-is for your situation, but there is a pattern to what this code is building in terms of the URL that will be passed back to the main report for invocation. The main URL string is based on
http://SERVER_NAME/ReportServer/Pages/ReportViewer.aspx?/REPORT_FOLDER/REPORT_NAME
and with each parameter to be passed using the &PARAM_NAME=PARAM_VALUE pattern. The selected values in the multi-valued parameters are passed in the same manner, but each value has its own entry. As an example,
&MVP_PARM=MVP_VALUE01&MVP_PARM=MVP_VALUE02&MVP_PARM=MVP_VALUE03
and so on. In the provided code, the multi-value parameter is asPeriods, and each selected value is built from the split array passed into the code function via the SRS JOIN operator within the for each loop.
This was a fun project to solve. If you have reporting needs, contact our team of experts at Crestwood for assistance. Email us at sales@crestwood.com.
