Dashboard Filters (Showing Description) but selecting the id value for use query parameters

[Deenar]

Hi

Is there a way to have a Single or multiple filter in a Dashboard that shows titles/names but on selection passes in the identifier as query parameter filters. The cost management dashboard shipped by Databricks seems to do this for the workspace selector, but I cant find anything in the documentation .

The workspace selector uses a single value with the id prefixed by : and uses one of the two formats

1) WorkspaceName (id:xxxx)

2) id:xxxx - where no workspace name is found

This seems to work magically, but I cant find any reference in documentation

[Deenar]

Hi

For those interested in the same. After digging into the Databricks cost dashboards code, I found out that this feature isn't supported yet. The Databricks dashboards use a hack to get around this by having a lookup table in each dataset definition to translate the text back to an identifier.

[200052]

I wanted to share my solution to this, hope this helps. 

Create a SQL function that accepts an ARRAY<STRING>, extracts a numeric ID enclosed in parentheses from the end of each string, and returns an ARRAY<BIGINT>.

Example Usage

Input: ["Item (1)", "Item (99)", "Complex Item (105) - Review"]

Output: [1, 99, 105]

CREATE OR REPLACE FUNCTION extract_numeric_ids(arr ARRAY<STRING>)
RETURNS ARRAY<BIGINT>
RETURN transform(
arr,
x -> cast(regexp_extract(x, '\\((\\d+)\\)[^\\(]*$', 1) as bigint)
);​

Now replace each of your legacy params which would have return the id to use this subquery.

SELECT
  id
FROM
  rangeme.certification_groups
WHERE
  array_contains(
    extract_numeric_ids(
      :`CERTIFICATION_GROUP_ID`
    ),
    id
  );