Need Help in Passing Dynamic parameters in main dataset

Here are two common, working patterns.

1) Single value

SELECT *
FROM your_view
WHERE region = region

2) Multi-value parameter

Redshift won’t accept IN (@Param) unless the driver expands it correctly, so the safest approach is to pass a single string and split it in SQL.

In Report Builder:

• Create parameter pRegion (Allow multiple values)

• Create a second hidden parameter pRegionCsv with default:
  =Join(Parameters!pRegion.Value, ',')

SELECT *
FROM your_view
WHERE (
  @pRegionCsv = 'ALL'
  OR region IN (
    SELECT TRIM(value)
    FROM SPLIT_TO_TABLE(@pRegionCsv, ',')
  )
)

This pushes filtering to Redshift before the report renders, which is what you want for performance.

If your parameters are numeric IDs, remove quotes and cast value to INT.

Hi Koritala 

Please check this and confirm

Create Parameters in Dataset
sql
In Dataset Properties → Parameters tab
Add parameters like:
@StartDate (Date)
@EndDate (Date)
@Department (Text)
@Region (Text)

Use in SQL Query
SELECT *
FROM your_redshift_view
WHERE
-- Date parameters
(@StartDate IS NULL OR date_column >= @StartDate)
AND (@EndDate IS NULL OR date_column <= @EndDate)
-- Text parameters
AND (@Department IS NULL OR department = @Department)
AND (@Region IS NULL OR region = @Region)
-- Multi-select parameters (comma-separated)
AND (@ProductList IS NULL OR product_id IN (
SELECT value FROM STRING_SPLIT(@ProductList, ',')
))

Koritala 

Since you mentioned your data source is Amazon Redshift, I am assuming you are using "Get Data" experience in Paginated reports. 

If yes then I would suggest you to first 

1. Connect to Redshift with the Power Query editor using the "Get Data" experience in paginated reports.

2. Create a table by pulling the data from redshift view

3. Create a m parameter (to apply filter on your view) with some default value

4. Filter the table you created in step 2 using the parameter you created in step 3

Then load the data into Paginated report builder.

Then in Report builder,

1. create a parameter

2. Bind the parameter to the table, steps are here: https://learn.microsoft.com/en-us/power-bi/paginated-reports/report-builder/connect-snowflake-databricks-power-query-online#how-to-bind-parameters

This way, you will be able to achive the desired output.

Also, there is a great video from Guy in a Cube channel, where they explained all the steps 

https://www.youtube.com/watch?v=OQKgnJkjJDI

I followed these steps in my reports and it is working fine.

Hope this helps

Connect on LinkedIn

Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

Hi Koritala,

I would also take a moment to thank krishnakanth240, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.