Forum Discussion
Helper IINeed help with parameter filter in Power BI Report Builder
Hi all,
I’m working in Power BI Report Builder connected to an Oracle database. I have three fields:
Product_ID (text input slicer)
Batch_ID (text input slicer)
Product_Name (dropdown slicer)
Relationships
One Product can have multiple Batches.
One Batch can also belong to multiple Products.
Product Name is tied to Product ID and Batch ID
What I want to achieve
- If user enters Product_ID = 123 and Batch_ID = %, it should return all Batch_IDs for Product 123.
- If user enters Batch_ID = ABC and Product_ID = %, it should return all Product_IDs for that Batch.
- After these two filters, the Product Name dropdown slicer should only display the Product Names relevant to the chosen Product/Batch combination.
Current Oracle SQL
SELECT Product_ID, Batch_ID, Product_Name, Quantity, Status FROM Sales_Table WHERE (Product_ID LIKE :paraProductID OR :paraProductID = '%') AND (Batch_ID LIKE :paraBatchID OR :paraBatchID = '%');
Parameter details:
Product_ID (text input slicer) → no default value, no available value list.
Batch_ID (text input slicer) → no default value, no available value list.
Product_Name (dropdown slicer) → has both default and available values from this query:
SELECT DISTINCT Product_Name
FROM Sales_Table WHERE (Product_ID LIKE :paraProductID OR :paraProductID = '%') AND (Batch_ID LIKE :paraBatchID OR :paraBatchID = '%');
My issue:
This setup works fine in Oracle SQL Developer, but in Power BI Report Builder I’m not sure how to:
Pass % as “ALL” in parameters.
Cascade the Product_Name dropdown so it correctly depends on the first two text slicers (Product_ID and Batch_ID).
Has anyone implemented this kind of cascading parameter logic in Report Builder with Oracle?Should I handle it fully in the SQL queries, or is there a cleaner approach using Report Builder parameter settings?
Thanks in advance
Hello prabavathym ,
You can try this:- Passing % as “ALL” in Parameters : In Power BI Report Builder, you can simulate the “ALL” behavior using default values and conditional logic in SQL:
Text Parameters (Product_ID and Batch_ID):
Set default value to % (or leave blank and handle it in SQL).
Your current SQL already handles this well:
WHERE (Product_ID LIKE :paraProductID OR :paraProductID = '%') AND (Batch_ID LIKE :paraBatchID OR :paraBatchID = '%')
This means if the user enters %, it returns all values.
- Cascading Product_Name Dropdown
To make Product_Name depend on Product_ID and Batch_ID, you need to:
Create Parameters:
paraProductID → Text
paraBatchID → Text
paraProductName → Dropdown (Menu)
Set Available Values for paraProductName, use a dataset like:
SELECT DISTINCT Product_NameFROM Sales_TableWHERE (Product_ID LIKE :paraProductID OR :paraProductID = '%') AND (Batch_ID LIKE :paraBatchID OR :paraBatchID = '%')
This ensures the dropdown only shows relevant names based on the other two inputs.
Enable Cascading Behavior:
- In Report Builder, go to the Product_Name parameter properties.- Under Available Values, link it to the dataset above.
- Under Dependencies, make sure it refreshes when paraProductID or paraBatchID changes.
Please feel free to give me a kudo and accept my answer as the solution if it suits you.
HAve a nice day,
Vivien
- Passing % as “ALL” in Parameters : In Power BI Report Builder, you can simulate the “ALL” behavior using default values and conditional logic in SQL:
1 Reply
- vivien57
Super User
Hello prabavathym ,
You can try this:- Passing % as “ALL” in Parameters : In Power BI Report Builder, you can simulate the “ALL” behavior using default values and conditional logic in SQL:
Text Parameters (Product_ID and Batch_ID):
Set default value to % (or leave blank and handle it in SQL).
Your current SQL already handles this well:
WHERE (Product_ID LIKE :paraProductID OR :paraProductID = '%') AND (Batch_ID LIKE :paraBatchID OR :paraBatchID = '%')
This means if the user enters %, it returns all values.
- Cascading Product_Name Dropdown
To make Product_Name depend on Product_ID and Batch_ID, you need to:
Create Parameters:
paraProductID → Text
paraBatchID → Text
paraProductName → Dropdown (Menu)
Set Available Values for paraProductName, use a dataset like:
SELECT DISTINCT Product_NameFROM Sales_TableWHERE (Product_ID LIKE :paraProductID OR :paraProductID = '%') AND (Batch_ID LIKE :paraBatchID OR :paraBatchID = '%')
This ensures the dropdown only shows relevant names based on the other two inputs.
Enable Cascading Behavior:
- In Report Builder, go to the Product_Name parameter properties.- Under Available Values, link it to the dataset above.
- Under Dependencies, make sure it refreshes when paraProductID or paraBatchID changes.
Please feel free to give me a kudo and accept my answer as the solution if it suits you.
HAve a nice day,
Vivien
- Passing % as “ALL” in Parameters : In Power BI Report Builder, you can simulate the “ALL” behavior using default values and conditional logic in SQL: