Dynamic Filtering Based on Parameter Using ODBC Connections in Report Builder

Cascading or dependent multi-value parameter allows us to pass either one or more than one input values to the report based on a particular parameter selection and one parameter value will be filtered depending on the other parameters. Also, it offers a “Select All” option that helps us select all the parameter values. But it is not directly supported by ODBC connections.

If you’re tired of dealing with problems in creating and filtering reports for ODBC connections, here’s a solution.

Step 1: Open Power BI Report Builder. In Report Data panel, right click on the “Data Source” and select “Add Data Source”.

Step 2: Choose the connection type as “ODBC” and in the connection string. Provide the data source name which is already configured in the ODBC driver.

Step 3: Give the data source specifications and build the connection by entering the “Username” and “Password” of the data source in the login information.

Step 4: Once the test connection is succeeded, click on the “Datasets” in the side panel to add a new data set from the data source.

Step 5: In the Dataset Properties, name the dataset and select the data source. Provide the query in the “Query” section to select values for the parameter.

SELECT DISTINCT(GENDER) FROM EMPLOYEE_DETAILS;

Step 6: After creating the datasets, select the “Report Parameter” under the parameters folder and specify the “Name” and the “Prompt name” that is to be displayed before the parameter in the report section. Choose the datatype of the parameter field and select “Allow multiple Values”.

Step 7: Next choose the “Available Values” section and select the dataset name, value field and label field which will point the fields in the respective dataset.

Step 8: Create a dataset for the dependent parameter (Name) and in the query section, provide the corresponding query.

SELECT Name FROM EMPLOYEE_DETAILS
WHERE Gender IN 
(SELECT VALUE FROM table(split_to_table(?,',')) AS table1);

Step 9: In the “Parameters” section, add a parameter and give the following expression in the expression field.

=JOIN(Parameters!Gender.Value,”,”)

Once the expression is given, repeat the step 6 and 7 for specifying the report parameter properties.

Step 10: Create another dataset and provide the corresponding query in the query section to display the data in the report view according to the parameter selection.

SELECT * FROM EMPLOYEE_DETAILS 
 WHERE Gender IN (SELECT VALUE 
 FROM table(split_to_table(?,',')) AS table1) 
 and Name in (SELECT VALUE FROM table(split_to_table(?,',')) AS table2);

In the “Fields” section, specify the field names and the field sources from the dataset.

Step 11: In the expression section, create the expressions for two parameters (Name and Gender).

=JOIN(Parameters!Name.Value,”,”)

=JOIN(Parameters!Gender.Value,”,”)

Step 12: Create a table in the report area with the required fields from the latest dataset.

Step 13: Now run the report and select the parameter value.

Step 14: The “Name” field will be filtered according to the selection of “Gender” parameter.

Step 15: Thus, the report gets filtered and displays the selected parameter values.

CONCLUSION:

This feature makes reports user-friendly and efficient. Dynamic filters simplify data selection, providing a personalized and intuitive experience, maximizing the effectiveness of the reporting system.

About Us:

Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe, and we’re quickly building a brand that is well-known for superior delivery.

Website: https://bi3technologies.com

Follow us on,
LinkedIn: https://www.linkedin.com/company/bi3technologies
Instagram: https://www.instagram.com/bi3technologies/
Twitter: https://twitter.com/Bi3Technologies