Skip to main content

Documentation Index

Fetch the complete documentation index at: https://honeydew.ai/docs/llms.txt

Use this file to discover all available pages before exploring further.

​
When would you use this?

  • Finance reviews need gross profit; weekly ops meetings need revenue - the same dashboard should serve both without anyone switching reports.
  • A CFO wants one governed performance metric that switches between revenue, food cost, and margin depending on the meeting.
  • A BI developer is tired of maintaining three nearly identical dashboards for different financial views - they want one dashboard where a filter controls which calculation runs.
  • Your semantic layer needs to replicate the behavior of a BI tool parameter: the user picks a value from a list, and a metric’s calculation changes accordingly.

​
What this recipe builds

In traditional BI tools, a parameter is a user-controlled input that can change a calculation. Users pick a value from a list, and selecting it changes which formula runs behind the scenes. Honeydew implements this through two components working together:
  1. A disconnected parameter entity (dim_financial_view) that holds the values a BI user can filter on.
  2. The GET_FIELD_SELECTION() function inside a metric, which reads whatever value the BI user has filtered on and routes to the correct calculation.
This recipe uses the conditional filtering concept. See the documentation for the full technical reference.

​
Architecture

User filtersGET_FIELD_SELECTION returnsMetric used
financial_view = 'Revenue''Revenue'Top-line revenue
financial_view = 'Food Cost''Food Cost'Ingredient cost
financial_view = 'Gross Profit''Gross Profit'Revenue minus order cost
No filter'Gross Profit' (default)Revenue minus order cost

​
Steps

1

Create the parameter entity

The dim_financial_view entity is disconnected - it has no relations to any other entity. It exists purely to carry the parameter values that a BI user can filter on. Its dataset is an inline SQL VALUES statement - no warehouse table is required.
  1. Click Add new entity.
  2. Choose Custom SQL.
  3. Paste the following SQL: 
    select column1::varchar as FINANCIAL_VIEW
from VALUES('Revenue'),('Food Cost'),('Gross Profit')
  4. Click Load columns.
  5. Rename the entity to dim_financial_view.
  6. Do not set any relations - continue and save.
2

Create the dynamic routing metric (revenue_dynamic)

Create a new metric called revenue_dynamic. GET_FIELD_SELECTION(entity.attribute, default) inspects the current query context and returns the value the BI user has filtered on for dim_financial_view.financial_view. If no filter is applied, it returns the default ('Gross Profit'). The CASE expression uses that value to route to the correct existing metric.
CASE GET_FIELD_SELECTION(dim_financial_view.financial_view, 'Gross Profit')
  WHEN 'Revenue'      THEN order_detail.revenue
  WHEN 'Food Cost'    THEN order_detail.order_cost
  ELSE                     order_detail.profit
END

​
How the generated SQL changes

Honeydew compiles GET_FIELD_SELECTION at query time by substituting the detected filter value directly into the CASE statement: 
-- No filter: resolves to Gross Profit (ELSE branch)
CASE 'Gross Profit'
  WHEN 'Revenue'      THEN sum(price)
  WHEN 'Food Cost'    THEN sum(quantity * cost_of_goods_usd)
  ELSE                     revenue - order_cost
END

-- Filter: Revenue
CASE 'Revenue'
  WHEN 'Revenue'      THEN sum(price)
  WHEN 'Food Cost'    THEN sum(quantity * cost_of_goods_usd)
  ELSE                     revenue - order_cost
END

​
Sample output

Same revenue_dynamic metric, 2022, top 5 cities - three different filter selections show three different numbers:
CityGross Profit (default)RevenueFood Cost
Tokyo$141M$268M$127M
Seoul$140M$268M$128M
Delhi$139M$267M$128M
London$135M$258M$122M
New York City$133M$255M$121M

​
Routing reference

Filter valueRoutes to metricUnderlying SQLMeaning
RevenuerevenueSUM(price)Top-line revenue
Food Costorder_costSUM(quantity × cost_of_goods_usd)Ingredient cost
Gross Profitprofitrevenue - order_costRevenue minus order cost
(no filter)profitrevenue - order_costDefault view

​
Using in a BI tool

  1. Drag dim_financial_view.financial_view as a filter.
  2. Select a single value: Revenue, Food Cost, or Gross Profit.
  3. Drag order_detail.revenue_dynamic to the view. The metric uses the correct formula automatically. Switch the filter value to see the calculation change.

​
Key design notes

ConcernGuidance
Disconnected entitydim_financial_view must have no relations to any other entity.
GET_FIELD_SELECTION defaultThe second argument is the fallback when no filter is applied. Useful when the BI tool does not support parameter defaults, or to protect against a report builder forgetting to set a value - unfiltered dashboards will still show meaningful data.
Extending to more variantsAdd WHEN branches and corresponding metrics. The dim_financial_view dataset SQL just needs one more VALUES row.
  • Metrics - reference for metrics that change behavior based on user filters
  • Conditional filtering - deeper reference on GET_FIELD_SELECTION patterns
  • Entities - reference for disconnected entities