2

Passing Multiple-Select Parameters from BI Office to SQL Server Reporting Services

by Jason Picker , Sales Engineer Pyramid Analytics


There are two primary ways to pass multi-select parameters to SQL Server Reporting Services (SSRS): using standard BI Office parameters or using standard SSRS parameters.  This document is designed to explain your options for integrating BI Office and SSRS when using multi-select parameters.

 

Understanding Multi-Select Parameters in BI Office

BI Office offers a number of slicer controls that support multi-value selections.  You can use the Multi-Select List Box, Button, or Hierarchy Tree controls.

Figure 1. Various selection options.

 

When using these controls, BI Office creates a comma-delimited list for passing the parameters to components other than other BI Office views, such as Web Page Assets and Buttons (value1, value2, …).

Understanding Multi-Select Parameters in SSRS

SQL Server Reporting Services supports multi-select values using a combo-box with checkboxes.  When using this control, each value must be passed to SSRS using parameter/value pairs (i.e. &parm=value1&parm=value2 etc.)

Figure 2. Parameter selections.


Option 1 –Using Standard BI Office Parameters

In order to configure SSRS reports to use standard BI Office multi-select parameters, you will need to make sure the parameter is a Text data type and the “Allow multiple values” checkbox is unchecked.

Figure 3. Reporting Service parameter setting.

 

Assuming the source of the report was SQL Server, you can apply the parameter to the SQL query using the “IN” statement, as an example.

Figure 4. Choose your data source.

 

In the parameters setup page, you will need to use an expression for the parameter value.

Figure 5. Set up the parameters.

 

In the SQL expression, you can use the Split function to break the parameter into separate objects.

Figure 6. Break into separate objects.

 

In the BI Office Story Board, you can pass the multi-select parameter as normal to the Web Page Asset.

Figure 7. Setting the interaction to pass members to Reporting services.

 


Option 2 –Using Standard SSRS Parameters

In order to configure SSRS reports to use standard SSRS multi-select parameters, you will need to make sure the parameter is a Text data type and the “Allow multiple values” checkbox is checked.

Figure 8. Reporting Service multi-parameter setting.

 

Assuming the source of the report was SQL Server, you can apply the parameter to the SQL query using the “IN” statement, as an example.

Figure 9. Alternate Reporting Service multi-parameter setting.

 

In the parameters setup page, you will need to select the parameter in the Parameter Value drop-down.

Figure 10. Set up the parameters.

 

Back in the BI Office Story Board, you will need to create a JavaScript button that will take the parameter as input and generate the necessary parameter/value pairs.

Figure 11. Using JavaScript button option to pass parameters.

 

Here is another example of the Javascript that you can copy and paste for your own use:

var url = "http://Fred/ReportServer/Pages/ReportViewer.aspx?%2fDrillThruReports%2fSales+Extract&rs:Command=Render";

var country = encodeURIComponent(arg1);

var sDate = encodeURIComponent("[Date].[Month Dates].[Date].&[" + arg2 + "-01-01T00:00:00]");

var eDate = encodeURIComponent("[Date].[Month Dates].[Date].&[" + arg2 + "-01-31T00:00:00]");

window.open(url + "&Country=" + country + "&StartDate=" + sDate + "&EndDate=" + eDate );

 

Here is a screenshot of the Story Board with a multi-select parameter and a JavaScript Button:

Figure 12. Story Board in edit mode with members, JScript button in place.

 

And that is how you can pass parameters from BI Office to Reporting Services. A great way to integrate your existing relational reporting with the BI Office analytics platform.

https://pyramidanalytics.wistia.com/medias/i02idrlhvp

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
login to reply
Like2 Follow
  • 2 Liked by
  • 3 mths agoLast active
  • 2Replies
  • 1006Views
  • 2 Following