Fixing Errors When Passing Multiple Values to the STRING_SPLIT Function in Bold Reports

This guide explains how to resolve common errors that occur when passing multiple parameter values to a dataset in Bold Reports, including:

• Error: “Argument data type varchar is invalid for argument 3 of the STRING_SPLIT function.”
  

  

• Error: “Procedure or function STRING_SPLIT has too many arguments specified.”
  

  

These issues occur when multi-value parameters are not formatted as a comma-separated string before being passed to the STRING_SPLIT function in SQL Server. This article provides a step-by-step solution for developers and report designers working in Bold Reports.

Example Scenario

Consider the following dataset query:

SELECT * FROM STRING_SPLIT(@Parameter1, ',')

In multi-value parameter scenarios, Bold Reports does not automatically join the values into a single string, causing the errors. You must format the parameter using an expression.

Solution

To fix these errors, convert the multi-value parameter into a comma-separated string before passing it to STRING_SPLIT. Follow these steps in Bold Reports:

1. Locate the Parameter:
   In the Bold Reports Query Designer, identify the parameter causing the error (e.g., @Parameter1).

   

2. Modify the Parameter Expression:

   • Select the parameter in the Query Designer.
   • Open the Expression dialog.
   • Enter the following expression to join the values:

   =Join(Parameters!YourParameter.Value, ",")
   

   

   Note: Replace YourParameter with the actual parameter name for example, Parameter1 for @Parameter1).

3. Save and Test:

   • Save your changes in the Query Designer.
   • Run the report to confirm that the dataset returns the correct results.
   

Additional Tips

• Parameter Name Matching: Ensure the parameter name used in the expression matches the name defined in the query.
• SQL Server Version: STRING_SPLIT is available in SQL Server 2016 and later. For earlier versions, use a table-valued function or XML parsing.
• Sample Report: Download a sample report for reference.
• Alternative Approach: For complex scenarios, consider using a stored procedure to process multi-value parameters.

By following these steps, you can successfully pass multiple parameter values to a dataset using STRING_SPLIT, eliminating errors and ensuring accurate, efficient report generation in Bold Reports.