Skip to main content
A spreadsheet is a dashboard page laid out as a large editable grid with a formula bar. You can type values in its cells and write formulas, as in a spreadsheet application. Formulas can reference the cells of v2 table widgets, on the same page or on any other page of the dashboard.
A spreadsheet page with two pivot tables v2 and a formula being edited in cell B5. The formula bar shows =(E5 - E7) / E4, and the referenced cells are outlined in the colors of their references
Use the spreadsheet to run quick calculations on top of the widgets of your dashboard, without leaving Atoti UI. For example, compute the change of a measure between two dates, or the ratio between two figures shown in different tables. The measures still come from the cube. Formulas only combine the values that widgets display.
The spreadsheet is an extension. It is not part of core Atoti UI. To get access to it, contact your account manager. Your IT can then install it.
The spreadsheet is a minimum viable product (MVP). It is already useful, but it has known limitations. They will be lifted in upcoming releases.

​
Spreadsheets and v2 table widgets

The spreadsheet extension adds three table widgets: Pivot table v2, Table v2 and Tree table v2. Each one appears in the widgets panel next to its standard counterpart. An orange dot marks their icons.
A spreadsheet page with the widgets panel collapsed, and the icons of the Pivot table v2, Tree table v2 and Table v2 widgets outlined
Formulas can only reference cells of v2 table widgets. They cannot reference cells of standard table widgets, charts or other widgets. v2 table widgets are a work in progress. They do not cover as many features as their standard counterparts yet.

​
What v2 table widgets support

  • Displaying the result of any MDX query.
  • Drilling down members.
  • Selecting cells.
  • The context menu, including the menu items added by extensions.

​
What v2 table widgets do not support yet

v2 table widgets also load differently from standard ones: see Current limitations. Use a v2 table widget when you need to reference its cells in a formula. Keep using the standard table widgets for everything else. Upcoming releases will close this feature gap step by step. Ultimately, v2 table widgets will replace the standard table widgets. The standard table widgets will remain supported.

​
Create a spreadsheet

  1. Open a dashboard.
  2. In the Insert menu, click New spreadsheet.
Atoti UI adds a page named Spreadsheet 1 to the dashboard, and opens it. The number increases with each new spreadsheet.

​
Add a widget to a spreadsheet

A widget in a spreadsheet is anchored at a cell: its top-left corner sits on that cell, and it covers as many cells as its data needs. All the widgets of a spreadsheet share the same grid and the same scrollbars. To add a widget:
  1. Select an empty cell.
  2. In the data model, click a measure or a hierarchy.
Atoti UI anchors a Pivot table v2 at the selected cell, with the field you clicked. To add more fields, click them in the data model or drag and drop them into the wizard, like you would for a table widget in a standard dashboard page. While a cell of the widget is selected, the Tools panel shows the fields of the widget, and you can edit them there.
A spreadsheet page with a pivot table v2 anchored at cell B2. One of its cells is selected, and the outlined Tools panel shows its fields: Currency on rows, pnl.SUM and delta.SUM as measures
To change the type of the widget, select one of its cells and click Table v2 or Tree table v2 in the widgets panel. Other widget types cannot be anchored in a spreadsheet.
A widget needs free cells to grow into. If typed values or another widget are in the way, the widget collapses to its anchor cell, and the cell shows #SPILL!. Select that cell to see the range the widget needs, then clear or move what blocks it.
A spreadsheet page where cell D3 shows #SPILL!. The pivot table v2 anchored at D3 cannot expand, because the text "In the way" typed in F5 blocks the range it needs. With D3 selected, a dashed outline shows that range

​
Type values in cells

Type a number or some text in any cell that no widget covers.
  • To replace the content of a cell, select it and start typing.
  • To edit the content of a cell, double-click it or press F2.
  • To validate, press Enter or Tab, or click outside the grid.
  • To cancel, press Escape.
  • To clear the selected cells, press Delete.

​
Write a formula

A formula starts with =. It combines numbers, cell references and function calls with the operators +, -, * and /. You can use parentheses to group operations, for example =(I6 - H6) / G18. The formula bar sits above the page. On its left, the name box shows the address of the selected cell, for example B5. The formula bar shows the content of the selected cell. For a formula cell, it shows the formula, and the cell shows its result. To write a formula:
  1. Select the cell that will hold the result.
  2. Click the formula bar. You can also type directly in the cell.
  3. Type =.
  4. Click the cell you want to reference. Its reference appears in the formula. For a value of a v2 table widget, the reference is a GETPIVOTDATA call: see Reference a cell of a widget.
  5. Type an operator, such as -.
  6. Click another cell to reference it.
  7. Repeat steps 5 and 6 as needed. Type ( and ) wherever you need them.
  8. Press Enter.
The cell shows the result of the formula.
A spreadsheet page with two pivot tables v2. Cell B5 is selected and shows the result of its formula, while the formula bar shows the formula =(E5 - E7) / E4

​
Reference cells by clicking them

While you write a formula, you can click a cell instead of typing its address. This works wherever the formula expects a value: right after =, an operator, (, or ,.
  • Click a cell to insert its reference.
  • Click another cell to replace the reference you just inserted.
  • Use the arrow keys to move the reference to a neighboring cell.
  • Type an operator or a parenthesis to keep the reference and continue the formula.
While you edit a formula, each referenced cell is outlined in the grid with the same color as its reference in the formula. The cell whose reference you just inserted has a dashed outline. You can also type references by hand, for example =B2 * 2.

​
Reference a cell of a widget

You can reference a cell of a v2 table widget the same way as any other cell: click it while you write the formula.
  • Clicking a value writes a GETPIVOTDATA call, which reads the value by its members. See Read a value with GETPIVOTDATA().
  • Clicking any other cell, such as a header, writes a reference to its position.
A reference to the position of a widget cell depends on where the widget is:
  • If the widget is anchored in the spreadsheet you are editing, the reference is the address of the cell in the spreadsheet, for example D5.
  • If the widget is on another page, the reference points to the cell in the widget’s own grid, for example 'p-2/0'!C7.

​
Reference a cell on another page

A formula can reference cells on any page of the same dashboard: another spreadsheet, or a regular page containing v2 table widgets.
  1. Start writing the formula, and stop where you want the reference, for example after = or +.
  2. Click the tab of the other page at the bottom of the dashboard. The formula stays in edit mode, and you keep typing in the formula bar.
  3. Click the cell you want to reference. Atoti UI writes the reference to that cell.
  4. Continue the formula. To reference cells from other pages, click their page tabs.
  5. Press Enter.
A reference to another page starts with an identifier between single quotes, followed by !, for example 'p-2'!C7.
  • For a cell of another spreadsheet, the identifier is the key of its page, for example 'p-2'!C7.
  • For a cell of a v2 table widget, the identifier combines the key of its page and the key of the widget, for example 'p-2/0'!C7.
Page keys are internal identifiers. They differ from the page names shown in the page tabs. Click cells to write references to other pages rather than typing them.

​
Read a value with GETPIVOTDATA()

A reference to the position of a widget cell, such as E5, reads whatever value sits there. When the layout of the widget changes, another value can take that position. For example, a sort, a drilldown, a filter or a live update changes the layout. The formula then reads that other value, without warning. The GETPIVOTDATA function reads a value by its members instead:
This formula keeps reading the pnl.SUM of Berlin, wherever Berlin moves in the widget anchored at B2. To write one, click a value of a v2 table widget while you write a formula, on any page. Atoti UI writes the call for you.
A spreadsheet page with a pivot table v2 anchored at B2, showing pnl.SUM by city. A formula is being edited in E4, and the formula bar shows the GETPIVOTDATA call that clicking the value of Berlin wrote
GETPIVOTDATA takes the widget first, then its members:
  1. The first argument names the widget by the cell where it starts. For a widget anchored in a spreadsheet, it is the anchor cell, for example B2. For a v2 table widget on a regular page, it is the top-left cell of the widget’s own grid, for example 'p-2/0'!A1.
  2. The next arguments name members by their full unique names, in any order. Write each one as text between double quotes, or reference a cell holding that text.
You can leave out:
  • A hierarchy of the rows or the columns, to read its total. For example, =GETPIVOTDATA(B2, "[Measures].[pnl.SUM]") reads the grand total. But always name a member of a hierarchy without a total.
  • The measure, when the widget shows a single measure.
The cell shows #REF! when the widget shows no value at these members, for example once a filter removes Berlin. To write references to positions when you click values, uncheck Generate GETPIVOTDATA in the settings. The formulas you already wrote keep their references.

​
Formula results and updates

A formula recomputes when a cell it references changes. This includes widget cells that change after a query update, or through a live update.
  • An empty referenced cell counts as 0.
  • A reference to a cell holding text, for example a member caption, shows that text.
  • A formula can reference other formula cells.
When a formula cannot compute a result, its cell shows an error: If you validate a formula with a syntax error, for example =A1 +, Atoti UI rejects it and the cell keeps its previous content.

​
Current limitations

The spreadsheet is a minimum viable product (MVP). It has the limitations below, which upcoming releases will lift. Formulas support the operators +, -, * and /, parentheses, text between double quotes, such as "Berlin", and the GETPIVOTDATA function. They do not support yet:
  • Most functions, such as AVERAGE, VLOOKUP or IIF.
  • Ranges, such as A1:A10.
  • Absolute references, such as $A$1.
  • Extending a formula to neighboring cells, for example by dragging it.
A v2 table widget loads its rows and columns as you scroll to them. A GETPIVOTDATA call shows #REF! until the widget loads the value it reads. The spreadsheet does not support yet:
  • Resizing its columns.
  • Inserting or deleting rows and columns.
  • Formatting cells, including the results of formulas. For example, a result shows all its decimals.
  • Copying, cutting and pasting cells.
v2 table widgets run their query as soon as you open the dashboard, even if their page is not displayed. A standard table widget runs its query only when it appears on screen. A dashboard with many v2 table widgets can therefore take longer to load and put more load on the server. In an upcoming release, v2 table widgets will run their queries only when displayed, or when a formula needs one of their cells.

​
Out of scope

The spreadsheet brings Excel-like flexibility to the data of the cube. It does not aim to replace Excel. The following will not be supported:
  • Excel plugins, such as the Bloomberg Excel add-in.
  • VBA macros.
  • References to cells of standard table widgets. v2 table widgets will ultimately replace them.