Need Help in Passing Dynamic parameters in main dataset

Hi Koritala 

Okay, Thank you for highlighting it. Can you try these options?

Problem is that Report Builder passes a multi-value selection as a single comma-separated string (e.g., 'North,South') which is incompatible with Redshift's syntax for the IN clause.

Using regexp_split_to_table (Dynamic Method)
This function directly splits the comma-separated string from the Report Builder parameter into individual rows in a derived table which we can then join to the main query. This method will work no matter how many values a user selects.

SQL Syntax for Dataset Query:
SELECT s.*
FROM sales_view s
WHERE 1=1
AND (@StartDate IS NULL OR s.sales_date >= @StartDate)
AND (@EndDate IS NULL OR s.sales_date <= @EndDate)
AND (@Department IS NULL OR s.department = @Department)
-- Multi-value parameter logic
AND (@Regions IS NULL OR s.region IN (
SELECT TRIM(value)
FROM regexp_split_to_table(@Regions, ',') AS t(value)
))

Key Points:
The regexp_split_to_table(@Regions, ',') part splits the string 'North,South' into a temporary table with two rows: 'North' and 'South'.
The TRIM(value) ensures any accidental spaces around the values are removed.
If no value is selected (@Regions is null), the @Regions IS NULL OR ... clause ensures the filter is ignored.

Dynamic SQL String Concatenation (Static Method)
If the dynamic method above fails, we can build the SQL string directly in the dataset's Query Expression (fx). This method is also common for other data sources including Redshift .

Instead of using a parameter directly in the SQL, we can write an expression that constructs the entire SQL string in Report Builder, inserting the parameter values as a formatted list. This gives us control over the final query syntax.

Report Builder Expression (fx):

vb
="SELECT * FROM sales_view WHERE 1=1"
& IIF(Parameters!StartDate.Value <> "", " AND sales_date >= '" & Format(Parameters!StartDate.Value, "yyyy-MM-dd") & "'", "")
& IIF(Parameters!EndDate.Value <> "", " AND sales_date <= '" & Format(Parameters!EndDate.Value, "yyyy-MM-dd") & "'", "")
& IIF(Parameters!Department.Value <> "", " AND department = '" & Parameters!Department.Value & "'", "")
// Multi-value parameter logic for region
& IIF(Parameters!Regions.Count > 0,
" AND region IN ('" & Join(Parameters!Regions.Value, "','") & "')",
"")

Key Points:
The Join(Parameters!Regions.Value, "','") function takes the selected values and turns them into a string like 'North','South'.