Databricks Dashboard Pivot Table: default collapsed state?

[1pedroosilva]

I'm building a financial dashboard in Databricks using a Pivot visualization with two dimensions in the row hierarchy.

The drill-down works as expected, but I'm having a usability issue: every time the view is loaded, both dimensions are fully expanded.

For this type of financial report, I'd prefer the initial state to be collapsed to the first level, so the user can click + on the items they want to explore, instead of having to click, repeatedly to close the hierarchy.

I couldn't find a setting that controls the initial expand/collapse state of the Pivot visualization.

Is there a configuration for this, or is there another way to make the hierarchy start collapsed by default?

Thanks!

[Coffee77]

I'd say that is a limitation and no built-in configuration exists right now.

However, as a workaround, maybe you could filter hierarchy levels by adding dashboard parameters/filters in an additive way. With that filter in place, you could select set of levels to show (1, 2, ..., All or combinations) being "1" the default value. Not sure if this solves your issue or could arise other usability concerns as I don't have the full context. In any case, you should update your underlying query to include the hierarchy level, which is easy by using recursive CTE SQL queries:

WITH RECURSIVE hierarchy AS (
-- Root level
SELECT
id,
parent_id,
name,
1 AS hierarchy_level
FROM accounts
WHERE parent_id IS NULL

UNION ALL

-- Children
SELECT
a.id,
a.parent_id,
a.name,
h.hierarchy_level + 1
FROM accounts a
JOIN hierarchy h
ON a.parent_id = h.id
)

SELECT *
FROM hierarchy;

[balajij8]

@1pedroosilva 

You can use a regular Table visualization with pre aggregated levels and follow below

• Create dataset with only the top level aggregation - first dimension
• Add filters that let you drill down to see detail when needed

It gives you a collapsed view by default with manual filtering for exploration

[data_pulse · Accepted solution]

@1pedroosilva 

I tested this in Databricks as well. The Pivot hierarchy can be expanded and collapsed interactively, but after collapsing the rows, saving, and publishing the dashboard, the published version still opens fully expanded.

So the expand/collapse state is not persisted as part of the published visualization, and I couldn’t find a setting to define the initial hierarchy state or default expansion depth and is possibly limitation currently.

Possible workarounds:

• Use a separate summary visualization for the top level and a second detail visualization for lower-level data.
• Use a dashboard parameter to switch between summary and detailed views.
• Flatten the hierarchy into a single dimension if expand/collapse behaviour is not required.

These workarounds can give a cleaner initial view, but they don’t fully replicate the native experience of starting the Pivot collapsed and expanding individual rows. Would be best to raise as Databricks feature request.

[hayoni]

Hello, @1pedroosilva !

As others mentioned, there is no built-in setting for this yet. However, if you want a quick solution without changing anything in your dashboard or queries, here is a simple workaround.

Step1. Create a new bookmark in your browser.
Step2. Set the bookmark's URL to the following JavaScript snippet  (It's just a click event! 😅)

javascript: 
document.querySelectorAll('[data-component-id*=pivot-row-header-cell]').forEach(b=>b.click())

please make sure to replace ":" with a regular colon ":" in your bookmark URL

Once your dashboard loads, clicking this bookmark will instantly collapse the entire table. 

(Note: While this is a very unofficial workaround, I think it could be helpful until Databricks natively implements this feature...)

Hope this helps!

[1pedroosilva]

Thanks everyone for the input.

Marking @data_pulse's reply as the solution: it confirms through testing that the expand/collapse state isn't persisted in the published dashboard, so there's currently no setting for an initial collapsed state or a default expansion depth on the Pivot visualization. That answers my question — it's a platform limitation, not something I was missing in the configuration

Good workaround suggestions all around too:
- @Coffee77 for flagging the limitation first, and for the recursive CTE to expose the hierarchy level
- @balajij8 for the pre-aggregated table + filters variation.
- @hayoni  for the bookmarklet — genuinely clever, and the only option that gives a collapsed view in a single click.

I'll raise this as a feature request with Databricks. Thanks again!