Customizing Selection Type values like Today, Yesterday and Last 7th Day using Dashboard Parameter

Customizing Selection Type Values Using Dashboard Parameter in Bold BI®

The concept of relative date filter allows you to filter and view data for specific time periods. By configuring the relative date in the Date Picker widget, you can apply the filter to all associated widgets, enabling a consistent view of the desired period’s data. Additionally, the Custom filtering option lets you explore data from previous periods with ease. This article provides a step-by-step guide on how to customize relative date type options in the combo box widget using the Dashboard Parameter feature in Bold BI®.

Step 1: Create a New Data Source

Refer to this help document to successfully create a new data source by connecting to your database.

Step 2: Access the Dashboard Parameter

Once the data source is created, navigate to the data source page and locate the dashboard parameter icon.

Step 3: Configure the Dashboard Parameter

Click on the dashboard parameter icon to open the configuration pop-up. Set the Mode to List and Type to String. Add the following values: Today, Yesterday, and Last 7th day.

Step 4: Use SQL Code to Filter Dates

Utilize the following SQL code to filter for Today, Yesterday, and Last 7th day in the code view mode. After run the code, ensure to save the data source.

SELECT
   [Product].[ProductID] AS [ProductID],
   [Product].[ProductName] AS [ProductName],
   [Product].[CreationDate] AS [CreationDate],
   [Product].[Price] AS [Price]
FROM [dbo].[Product] AS [Product]
WHERE 
[Product].[CreationDate] IN (
       SELECT DateValue
       FROM (VALUES 
           (CAST(GETDATE() AS DATE), 'Today'),  -- Today
           (CAST(DATEADD(DAY, -1, GETDATE()) AS DATE), 'Yesterday'),  -- Yesterday
           (CAST(DATEADD(DAY, -7, GETDATE()) AS DATE), 'Last 7th day')  -- 7 days ago
       ) AS DateList(DateValue, DateLabel)
       WHERE DateLabel IN (@{{:Date}}) 
   ) 

Replace @{{:Date}} with your actual parameter name. This query filters products by their creation date based on specific labels (e.g., ‘Today,’ ‘Yesterday,’ or ‘Last 7th day’). It uses a subquery with a VALUES table to dynamically generate a list of dates and labels, and matches the chosen label (@{{:Date}}) to return only the corresponding date.

Step 5: Bind the Column to Widgets

Bind the column to any of the available widgets and verify that the values are displayed correctly on the dashboard. For example, you can bind the dashboard parameter to a combo box and display the details in a grid widget.

Step 6: Apply Selected Values

Select the desired values from the widget. The results will be displayed accordingly based on the selected values.

Additional References

• Working with Dashboard Parameters in Bold BI
• How to create Data Source in Bold BI