Writing Custom Query in Dashboard Parameter for Extract Mode

Using a customized query in Dashboard Parameters allows for writing SQL queries directly on the data source table, providing greater flexibility in obtaining desired results that may not be possible through the user interface. Kindly locate the steps for writing Custom Query in extract mode data sources, with a sample query provided below for your reference.

Example:

The sample data provided below can be used to calculate the total number of employees from the table based on customer ID by writing a custom query.

SELECT COUNT(EmployeeID) FROM (Orders) WHERE CustomerID = 'some value' 

In SQL Live mode data sources, queries can be written using the actual table and column names. However, in extract mode data sources, fields in Bold BI are identified with a unique identifier, so they cannot be directly referenced in the Custom Query. Instead, you will need to refer to the corresponding GUID for the tables and columns. Below is the equivalent custom query for extract mode.

Custom Query:

SELECT
COUNT("Orders_dbo"."ff2009ff-22e4-49f5-bdb7-db2dd3633f39") AS "CountOfEmployee"
FROM "Datasource_12588f9f-8098-4bf6-b720-0bf811b4e071"."Orders_dbo" AS "Orders_dbo"
WHERE "Orders_dbo"."1ef7195e-0a86-47ac-9b1e-9c0c32b317e9" = 'VINET'

This SQL query calculates the count of values in the column “ff2009ff-22e4-49f5-bdb7-db2dd3633f39”* from the “Orders_dbo”* table. It specifically filters rows where the value in the column “1ef7195e-0a86-47ac-9b1e-9c0c32b317e9” equals ‘VINET’. The result is presented with the alias “CountOfEmployee”.

Kindly adhere to the steps outlined below to prepare this query.

Steps to prepare Custom Query for Extract mode:

1. Edit the data source from which you want to create the dashboard parameter and enable the Code option.
   
   
2. After enabling it, you will be able to find the unique identifiers for the table and fields as shown above.
3. Copy the required fields with or without the Alias name and prepare the query needed for custom SQL.