Configuring Web API Data Source with Dynamic Date Parameters for Specific Day Retrieval

When integrating a Web API data source with your Bold BI dashboard, you may encounter scenarios where you need to pass dynamic date values as part of the API request. A common requirement is to retrieve data for a specific day of the current month, such as the second day. This article provides a step-by-step guide on how to configure your Web API data source to dynamically refresh with the date of the second of the current month.

Step-by-Step Configuration

Before proceeding, ensure you have access to the Bold BI dashboard and the Web API you wish to configure. Familiarity with SQL and the structure of your API request is also beneficial.

1. Understand Dynamic Date Parameters: Begin by reviewing the existing knowledge base article on configuring Web API data sources with dynamic date parameters. This article can be found at Configuring Web API Data Source with Dynamic Date Parameters.

2. Create a Dashboard Parameter: Instead of following steps 2 to 5 in the existing article, you will create a List type data source mode dashboard parameter with a custom SQLquery. This parameter will calculate the date you need to pass to the Web API.

3. Custom SQL Query: Use the following SQL query to create a dashboard parameter that will always return the second day of the current month:

   SELECT 
       CASE 
           WHEN EXTRACT(DAY FROM CURRENT_DATE) >= 2 
               THEN date_trunc('month', CURRENT_DATE) + INTERVAL '1 day'
           ELSE date_trunc('month', CURRENT_DATE)
       END AS second_of_current_month
   

   Note: Please use the Custom SQL query provided above if you are using a PostgreSQL data store. If you are using a different data store, please refer to the syntax specific to that server type, such as MSSQL or MySQL.

4. Configure Data Type and Format: Ensure that you select the appropriate data type and date format for your dashboard parameter to match the requirements of your Web API.
   

   

5. Utilize the Dashboard Parameter: Incorporate the dashboard parameter into your Web API request URL. For Example,

https://sample.com/Northwind/Northwind.svc/Orders?OrderDate=@{{:dynamic_date_param}}

This will ensure that each time the dashboard refreshes, the Web API call includes the correct date parameter. The above example retrieves the second day of the current month for every API call. You can customize the date as per your requirement.

Conclusion

By following the steps outlined above, you can successfully configure your Web API data source to dynamically retrieve data for the second day of the current month. This method leverages the flexibility of Bold BI’s dashboard parameters and custom SQL queries to meet specific data retrieval needs.

Additional References

• Configuring Web API Data Source with Dynamic Date Parameters