Ad hoc grids are great for exploring, but board packs and management reports often need a fixed, beautifully formatted layout where each number sits in a specific cell. Smart View functions do exactly that: each cell asks the cube for one value.
In this article
The core function: HsGetValue
=HsGetValue("MyConnection", "Year#FY25;Period#Q1;Scenario#Actual;Account#Revenue;Entity#North")- First argument — the connection name (a private connection, or the shared connection alias).
- Second argument — the members, each written
Dimension#Memberand separated by semicolons. - Dimensions you leave out use the connection’s POV (point of view) defaults.
Build it from cells, not typed text
=HsGetValue("MyConnection", "Period#" & C$3 & ";Account#" & $A5 & ";Entity#" & $B$1 & ";Scenario#Actual")Put periods across row 3, accounts down column A and the entity in B1. One formula copied across the grid fills the whole report, and changing B1 re-points every cell.
Refreshing
Functions calculate when you Refresh (Smart View ribbon → Refresh, or Refresh All for every sheet). Normal Excel recalculation does not fetch new data. While not connected, cells may show an error such as #NO CONNECTION — connect and refresh. Refreshing many sheets? Use VBA to refresh Smart View.
Other useful functions
| Function | What it does |
|---|---|
| HsGetValue | Reads one data value |
| HsSetValue | Sends a value back to the cube when you submit (planning apps, with write access) |
| HsGetText | Reads cell text / comments (where supported) |
| HsDescription | Returns a member’s description/alias |
Functions vs ad hoc: which to use?
| Functions | Ad hoc grid | |
|---|---|---|
| Layout | Any layout, fixed format | Grid decided by Smart View |
| Speed on big reports | Slower (one call per cell) | Faster |
| Exploring / drilling | No | Yes (zoom, pivot) |
| Best for | Board packs, KPI dashboards | Analysis, reconciliations |
New to Smart View? Start with ad hoc analysis for beginners.