AI/BI Dashboard pivot export: Excel

[anmolhhns]

Hey, currently we have an AI/BI Dashboard pivot table with a row hierarchy such as: outlet->date->day
In the dashboard, users can expand/collapse the hierarchy and may be viewing only the top-level outlet aggregation.
However, when downloading the pivot to Excel, the export includes the lower hierarchy levels (date, day) even when those levels are currently collapsed in the dashboard.
In Power BI, users can export “Data with current layout”, where the export reflects the hierarchy level currently visible in the visual.

Questions:
1. Is there any native Databricks AI/BI Dashboard option to export a pivot table at the currently expanded/collapsed level?
2. Is there an equivalent to Power BI’s “Data with current layout”?
3. If not, is parameter-driven aggregation at the dataset/query level the recommended workaround, or is there another supported approach that preserves a single pivot visual?

[balajij8]

Hi Anmolhhns,

AIBI generally exports the underlying dataset tied to that visual across all defined hierarchy fields ignoring whether row or column groups are currently collapsed in it. You can follow below

Parameter-Driven Aggregation

You can shift the aggregation logic upstream into your dataset definition using a single-value dashboard parameter to preserve a single pivot visual while controlling the export granularity.

• You attach a single-select dropdown parameter (controlling depth between Outlet, Date, or Day) to the dashboard and pass it directly into the dataset SQL. Inside the query, conditional grouping logic evaluates the selected parameter value and dynamically groups the data only by the dimensions required for that specific detail level.
• When a user selects a granularity level from the dashboard filter, the query re-aggregates and returns a dataset strictly at that depth. Because the pivot table's underlying result set now only contains data for the active level exporting to Excel outputs the exact aggregation displayed on the visual.
• Its recommended as it keeps the dashboard canvas clean with a single visual avoiding client-side rendering bottlenecks on big collapsed datasets and guarantees export consistency.

Multiple Dedicated Widgets

You can create distinct dataset queries pre-aggregated at each individual hierarchy level and surface them through separate pivot widgets on the canvas if requiring users to toggle a parameter dropdown prior to exporting isn't feasible for you.

• You display side-by-side pivot tables (Widget 1 for top-level Outlet, Widget 2 for Outlet + Date, Widget 3 for full detail). Users navigate to the visual that matches their target detail level and click export on that specific widget.
• While it eliminates parameter selection steps before downloading, it takes up significantly more dashboard complexity and multiplies widget maintenance if underlying schema definitions change.

If maintaining a single, unified pivot visual is the primary need, setting up parameter-driven aggregation at the dataset level is the robust approach.