How to Filter Joined Data Using Custom Attributes in Bold BI Code View

This article explains how to use custom attributes in Code View (custom SQL) data sources in Bold BI to filter the results of SQL JOIN queries. It demonstrates common JOIN patterns, shows how to apply attribute-based filtering to joined datasets, and provides guidance on avoiding common issues related to aliases, column references, and custom attribute syntax. This approach is useful for dashboard designers and developers who need to dynamically restrict data returned from joined tables based on values configured through custom attributes.

Step-by-Step Instructions

Step 1: Create (or confirm) the Custom Attribute in Bold BI

• Create a Custom Attribute (example: customer_filter) that maps to a field (example: CustomerID).
  
  
  
  
  
  
  
  

Step 2: Open the SQL data source in Code View

• In the Data Sources panel, locate the SQL data source you want to modify.
• Click the kebab menu (⋮) next to the data source and select Edit Data Source.
  
  
• In the data source editor, switch to Code View.
• Verify that the SQL query editor is displayed.
  
  
  
  
  

Step 3: Use the correct syntax for Custom Attributes in Code View

• In your SQL query, reference the attribute using this syntax:

  ${{:attributename}}
  

• If the attribute returns a string value, enclose it in single quotes.
• Place the attribute where SQL expects a value, typically within a WHERE clause.
• Run the query to verify that the attribute value is resolved correctly before proceeding with additional query modifications.
  
  

Step 4: Perform the SQL JOIN and apply the Custom Attribute filter

• Write your JOIN using fully qualified column references to avoid ambiguous column errors.
• Apply the attribute filter in the WHERE clause (recommended for clarity and performance).

Step 5: Validate the query output

• Preview the data source results.
• Confirm that changing the attribute value changes the returned rows.
  
  
• If you see errors, check the troubleshooting section below.

Code Samples

The custom attribute name used in the SQL query must match the attribute name configured in UMS. When the attribute returns a string value, enclose the expression in single quotes. After adding the custom attribute expression, run the query to verify that the value is resolved correctly and the expected records are returned.

Example 1: INNER JOIN + Custom Attribute filter in WHERE

• Use this when your attribute value represents a direct filter (for example, filter to a specific customer).

SELECT
   [Customers].[CustomerID] AS [CustomerID],
   [Customers].[CompanyName] AS [CompanyName],
   [Orders].[OrderID] AS [OrderID],
   [Orders].[OrderDate] AS [OrderDate],
   [Orders].[Freight] AS [Freight]
FROM [dbo].[Customers] AS [Customers]
INNER JOIN [dbo].[Orders] AS [Orders]
   ON [Customers].[CustomerID] = [Orders].[CustomerID]
WHERE [Customers].[CustomerID] = '${{:attributename}}' 

Example 2: LEFT JOIN + Custom Attribute filter (keep unmatched rows)

• Use this when you want all rows from the left table, and filter by attribute only for the left table.

SELECT
   [Orders].[OrderID] AS [OrderID],
   [Orders].[OrderDate] AS [OrderDate],
   [Orders].[CustomerID] AS [CustomerID],
   [Customers].[CompanyName] AS [CompanyName]
FROM [dbo].[Orders] AS [Orders]
LEFT JOIN [dbo].[Customers] AS [Customers]
   ON [Orders].[CustomerID] = [Customers].[CustomerID]
WHERE [Orders].[CustomerID] = '${{attributename}}' 

Example 3: Using an alias safely (avoid alias-in-WHERE limitations in Extract mode)

• In many SQL engines, a SELECT alias cannot be referenced directly in WHERE. In Bold BI Extract mode, this is more noticeable because column names may include unique identifiers. Use a subquery to filter on an alias consistently.

SELECT *
FROM (
   SELECT
       [Orders].[CustomerID] AS [CustomerID],
       [Orders].[OrderID] AS [OrderID],
       [Orders].[OrderDate] AS [OrderDate]
   FROM [dbo].[Orders] AS [Orders]
) AS FilteredOrders
WHERE FilteredOrders.[CustomerID] = '${{:attributename}}' 

Notes and Troubleshooting

Extract mode vs Live mode behavior

• Live mode: GUID-style column identifiers are not generated, so using column names or aliases is typically straightforward.
• Extract mode: columns may include unique identifiers; using a stable alias via a subquery can make the query easier to reuse across data sources.
• Avoid ambiguous column references
• If both tables contain the same column name (for example ID or Date), always prefix with table alias:

SELECT a.ID, b.ID
FROM TableA a
JOIN TableB b ON a.ID = b.AID; 

• Keep JOIN conditions simple
• Complex CASE WHEN logic inside JOINs can hurt performance. Prefer boolean logic (AND/OR) when possible.

Related Articles

• Configuring Custom Attributes
• Steps to Add Custom Attributes in Query With and Without Alias Name
• How to Use the Code View Mode Effectively
• Code View Mode in Bold BI | Understanding it’s Impact on Performance