How can the data of a Widget column be switched from multiple columns in Bold BI?

Published:

This article outlines the steps to swap columns in a dashboard widget based on dashboard interaction. The process involves creating a data source, configuring a dashboard parameter, and writing a custom query to dynamically adjust the displayed data. For example, please refer to below sample data below which we used to verify the dynamic swapping based on filter selection.

id	name	email	sales	sales_by_individual
1	John Doe	john.doe@example.com	5000	2000
2	Jane Smith	jane.smith@example.com	7000	3000
3	Alice Brown	alice.brown@example.com	4500	1500
4	Bob Johnson	bob.johnson@example.com	6200	2200
5	Carol White	carol.white@example.com	8000	4000
6	Dave Black	dave.black@example.com	3300	1300
7	Eva Green	eva.green@example.com	7600	3400
8	Frank Stone	frank.stone@example.com	4900	2100
9	Grace Blue	grace.blue@example.com	5400	2700
10	Henry Gold	henry.gold@example.com	6800	2800

In this article, we will set up the name column in the master widget. We will then define the case to switch the columns in the listener widget - if any values are chosen in that filter widget, the widget will display data from the sales_by_individual column and if all names are selected in the filter widget, the widget will show data from the sales column.

Steps to Swap Columns

Step 1: Create a Data Source and Dashboard Parameter

Create a data source and establish a list mode dashboard parameter for the column to be used in the filter. For this example, the filter will be based on the “name” field.

Step 2: Create a Custom Query

Create the same data source again to avoid cyclic dependency errors. Switch to code view mode and write a custom query to swap the column based on user selection. The dashboard parameter will be configured in the filter widget to filter values based on changes to the dashboard parameter.

WITH Sales_table AS (
    SELECT	
        DISTINCT unnest(string_to_array('@{{:postgresql-dynamiccolumn.Parameter1.IGNOREQUOTES}}', ',')) AS selectedvalues,
        "sales_data"."id" AS "id",
        "sales_data"."name" AS "name",
        "sales_data"."email" AS "email",
        "sales_data"."sales" AS "sales",
        "sales_data"."sales_by_individual" AS "sales_by_individual"
    FROM "public"."sales_data" AS "sales_data" 
)
SELECT 
    CASE 
        WHEN count(distinct Sales_table.selectedvalues) = (SELECT count(distinct Sales_table."name") FROM Sales_table) 
        THEN Sales_table."sales" 
        ELSE Sales_table."sales_by_individual" 
    END AS salesdata,
    Sales_table."id",
    Sales_table."name",
    Sales_table."email",
    Sales_table."sales",
    Sales_table."sales_by_individual" 
FROM Sales_table
GROUP BY Sales_table."sales", Sales_table."sales_by_individual", Sales_table."name", Sales_table."id", Sales_table."email"

The aforementioned query is compatible with PostgreSQL. You should modify the query to ensure compatibility with the database you are using.

In the above query:
- Replace @{{:postgresql-dynamiccolumn.Parameter1.IGNOREQUOTES}} with your dashboard parameter name and data source name.
- The below logic checks the count of selected values against the total count. If all values are selected, the sales column data is used; otherwise, the sales_by_individual column data is used.

CASE WHEN count(distinct Sales_table.selectedvalues) = (select count(distinct Sales_table."name") from Sales_table) Then  Sales_table."sales" ELSE  Sales_table."sales_by_individual" END As salesdata,

Step 3: Bind Dashboard Parameter and Configure Widgets

Bind the dashboard parameter value in the combo box and assign other values in the Grid widget or any other widget as needed.
Enable “Multi select” and set the “Act As Master Widget” property for the combo box.
Configure the filter for cross data source filtering:
- Change the radio button to custom.
- Choose the parameter in the first dropdown, select the target data source in the second dropdown, and select the target column name in the third dropdown.
Save the filter configuration and publish the dashboard.

Step 4: Verify Filtering Functionality

Check if the filtering works as expected:
- Selecting all values should render the data under “salesdata” column in widget based on the sales column.
- Selecting any specific values should render the data under “salesdata” column in widget based on the sales_by_individual column.