Zap Live add-in for Excel

After installing the Zap Live add-in or downloading a Zap Live workbook from Data Hub, you can interact with your data from three different areas inside Excel:

• The Zap Live task pane
• The Zap Live ribbon
• The Excel worksheet

The Zap Live task pane

Adding a query

When adding a query, you can either select an existing report from your Data Hub, or create a new custom query. Queries are added to the current worksheet, and only one query can be added to a worksheet at a time. The worksheet should be blank to accommodate the query results. Even if the query's Automatic table is turned off, the query is still associated with the worksheet it was added to.

When adding a query to a workbook with existing queries, the Back button will take you back to the previously selected query, without adding a new one.

The query toolbar

The task pane shows a single query at a time, and the toolbar at the top of the pane allows you to manage these queries. Some options are only available through the Query menu at the right of the toolbar.

The items in the toolbar are:

1. Query dropdown
2. View table
3. Automatic table
4. The Query menu
5. Cell functions
6. Open in Data Hub
7. Delete
8. Status

Query dropdown

The query dropdown shows the current queries added to the workbook, and an option to Add a new query. The Global filters item is a special query that applies filters to all other queries in the workbook (see Global filters for more details).

View table

Clicking the View table button will switch to the worksheet where the query was added, and select the top-left cell of the table. This cell is the reference point for all query formulas, allowing them to be updated when the query is refreshed.

Automatic table

By default, the results of queries are displayed in a table format on the worksheet, and refreshing the query will replace the table with the new results. When Automatic table is disabled, Zap Live formula results will still be updated, but the table will not be replaced. This allows you to customize the table without losing your changes when the query is refreshed, but some features will become unavailable, or behave differently:

• Expand/Collapse - All expand and collapse options will be disabled, since they rely on changing the structure of the table.
• Select Table - The Select Table button will be disabled, since the bounds of the table are no longer known.
• Slicers - Slicers will still be available, but they will only affect Zap Live formula values, and no rows or columns will be added to or removed from the results.
• Cell functions - The Cell functions toggle will be disabled, since it only affects how automatic tables are displayed.
• Rows and Columns design - The Rows and Columns placeholders of custom queries will be disabled, since they are used to define the structure of the table.

Cell functions

When cell functions are turned off, tables will be loaded using raw values for better performance. Cell functions are automatically disabled when loading large tables, but can be turned back on manually. This option only affects table updates, so cell functions copied outside the table will still update correctly. See Cell formula functions for more information about cell functions.

Open in Data Hub

The Open in Data Hub option opens the current query in Zap Data Hub within a new browser tab. Reports previously loaded from Data Hub will open in their original saved state, and can be modified to effect changes in Excel. Custom queries will be opened as a new Analysis report. Saving this report will not affect the custom query in Excel, but it can be loaded into Excel as a Data Hub report.

Delete

Removes the current query from the workbook. Formulas that reference the query will no longer work.

Status

The status bar underneath the toolbar indicates when the query is being loaded, and when the underlying data was last updated. Hovering over the last updated status will provide more detail about the last time the model was processed, and when the query was last refreshed in Excel.

Design tab

The Design tab appears for custom queries when you have design permissions in Zap Data Hub, and provides a streamlined version of Data Hub's query design experience optimized for ad-hoc analysis within Excel.

Model dropdown

Open the model dropdown to select any of the data models you have access to in Data Hub. Once a model is selected, the dimension tree will be populated. Using Refresh models button will refresh both the list of available models and the current dimension tree.

Dimension tree

The dimension tree provides a hierarchical representation of the model's semantic layer. At the top level are the measures dimension, as well as the dimensions provided by the model. Items can be expanded to see measure groups, attributes, and hierarchies, and expanding further will provide access to individual measures, levels, and members. Calculated members and named sets created in Data Hub will also be available in the dimension tree, underneath their configured parent.

Measure groups, measures, levels, members, calculated members, and named sets can be dragged and dropped onto the Rows, Columns, and Filters placeholders to create a query. Changes are automatically reflected in the results table in Excel.

Query placeholders

Underneath the dimension tree are query placeholders for Rows, Columns, and Filters. Dragging elements from the dimension tree onto these placeholders will add them to the query. Once added, elements can be rearranged by dragging them within the placeholders, or through the move options in their context menu. Elements can be removed using the Remove option in their context menu. An element's context menu can be accessed by left-clicking on it.

Rows and Columns

The Rows and Columns placeholders are used to define the structure of the query. Measures, levels, and members can be added to these placeholders to create a table-like structure. Measures will be aggregated based on the levels and members in the query. Changing the contents of these placeholders will change the structure of the table in Excel, and so the query's Automatic table setting must be enabled.

Elements from up to 3 hierarchies can be added to the Rows and Columns placeholders.

Filters

The Filters placeholder is used to filter the results of the query. Adding members to this placeholder will filter the results to only include those members. Adding levels won't filter the results, but they can be turned into slicers to allow for dynamic filtering of the results.

Functions

Member, measure, and calculated member elements can have a range of functions applied to them from their context menu. The available functions depend on the type of element, and can include:

Function	Description
Total to date (TTD)	Aggregates all values up to the context period.
Year to date (YTD)	Aggregates all values from the start of the year up to the context period.
Month to date (MTD)	Aggregates all values from the start of the month up to the context period.
Quarter to date (QTD)	Aggregates all values from the start of the quarter up to the context period.
Week to date (WTD)	Aggregates all values from the start of the week up to the context period.

Context period

The functions above require a context period to be defined, which provides a reference point-in-time for the function. Context periods can be defined in several places:

• The element itself - If the element is from a date hierarchy, it provides the context for functions applied to it.
• Columns and rows - If a single date hierarchy is present elsewhere on Columns or Rows, its members will provide the context period. e.g. If the "Total to date" function is applied to an "Invoice line amount" measure on Columns, and a Months level is added to Rows, the report will provide a running count of sales month-by-month.
• Slicers - Single select, Time context, and Date range slicers for date hierarchies will provide context to any functions in the query.
• Current date - If no other context is available, the current date will be used as the context period.

note

The context period can come from any level of a date hierarchy. For time intelligence function to work correctly, make sure that the context period is at the same level or lower than the level being calculated with. For example, for the "Quarter to date" function to work correctly, the context period must be at the Quarter level or lower (e.g. Month or Date).

Slicer design

Elements in the Filters placeholder can be turned into configurable slicers by clicking on an element in the Filters placeholder and selecting one of the Slicer options from the context menu.

The following slicer options are available:

• Hidden - No slicer will be visible, and the element will filter the query directly.
• Single select - The slicer will display an expandable list of members, allowing the user to select one member at a time.
• Multi select - The slicer will display an expandable list of members, allowing the user to select multiple members at a time.
• Time context - Similar to the Single select slicer, except that the selected member will not filter the query directly. Instead, it will be used as the context period for the time intelligence functions applied within the query.
• Date range - The slicer will display From and To dropdowns, allowing the user to select a range of date periods.

Slicers tab

The Slicers tab is available for all queries, and shows any slicers that are part of the query's design. Slicers allow you to quickly filter the query results by selecting members from the slicer. The query results will be updated automatically when slicer selections are changed.

Slicers are shown as an expandable list. Individual slicers can be expanded or collapsed by click on their name.

Different types of slicers will provide different ways to select members:

• Single select - Allows selecting a single member at a time. Members can be expanded to select their children.
• Multi select - Allows selecting multiple members at a time. Members can be expanded to select or de-select their children. A "Select all" option is available to quickly select or de-select all members in the slicer. If no members are selected, the query will not have any results.
• Date range - Provides From and To dropdowns to select a range of date periods. The query will be filtered to include all periods within the selected range. If the "All" option is selected as the From period, then all date up to the To period will be included in the query results.

Global filters

Global filters is a special query that applies has no columns or rows, but whose filters apply to other queries in the workbook.

All queries in the workbook that use the same data model as the global filters will be affected by it, and any changes to the global filters or it slicers will automatically update the results of those queries.

Slicers configured in the Global filters will appear in its Slicers tab, as well as in the Slicers tabs of any other queries that are affected by the global filters, with an icon indicating that they are global slicers.

If any slicers are for the same hierarchy as a global slicer, they will be hidden, and the global slicer will take precedence. See Understanding slicer and filter precedence for more information about this behavior.

Most items in the query toolbar do not apply to global filters, and will be disabled when the global filters query is selected.

The Zap Live ribbon

The Zap Live ribbon provides ways to interact with data that you've loaded into your workbook.

Show Queries

Shows the Zap Live task pane.

Refresh

The Refresh menu provides two options for refreshing data in the workbook:

• Refresh all - Refreshes all queries in the workbook.
• Refresh selected - Refreshes the query for the currently selected cell or table. If the selected cell uses Zap Live functions for multiple queries, all of those queries will be refreshed. If the selected cell is not associated with a query, the Refresh selected option will be disabled.

Expand/Collapse

The Expand/Collapse section provides several options for expanding and collapsing a selected row header in a query table:

• Expand Member - Expands the selected row header to show its children.
• Collapse Member - Collapses the selected row header to hide its children.
• Expand Level - Expands all row headers at the same level as the selected row header.
• Collapse Level - Collapses all row headers at the same level as the selected row.

Select Table

When a cell inside a results table is selected, the Select Table button will select the entire table. Customized cells around the table will not be selected.

Clear Formatting

Clears any custom table formatting applied to the selection. See Table formatting for more information.

Sign Out

Signs out of the current Data Hub session, allowing you to connect to a different Data Hub site, or sign in with a different user account.

Help

Opens the Zap Live documentation in your web browser.

The Excel worksheet

When a Zap Live query's Automatic table is enabled, the results of the query will be displayed in a table format on the worksheet. The table will be automatically updated when the query is refreshed, and can be customized using Excel's built-in table formatting options.

By default, data cells will be populated using the Zap Live formula functions, which will automatically update when the query is refreshed. These functions can be used to reference specific cells in the table, and can be copied to other cells in the worksheet. If the Cell functions toggle is turned off, the table will be loaded using raw values for better performance, but cell functions copied elsewhere will still work as expected.

Cell formula functions

Zap Live provides several functions that can be used in Excel formulas to reference data from the query results. These functions can be used to create custom calculations, or to reference specific cells in the query table. The simplest way to use these functions is to copy them from the query table, but they can be used piecemeal in any formula.

ZAP.CELL function

The ZAP.CELL function is used to reference a specific cell in the query table. Rather than relying on the position of the cell, it uses the members that make up the row and column of the cell to identify it. This ensures that if the row or column move around, the function will still reference the correct cell.

Syntax

ZAP.CELL(table, member1, member2, ...)

Argument name	Description
table (required)	A named range that refers to the top-left cell of the query table.
member1, member2, ... (required)	The members that identify the row and column of the cell. Members can be specified using the ZAP.MEMBER, ZAP.CALCULATION, and ZAP.SUMMARY functions.

ZAP.MEMBER function

The ZAP.MEMBER function is used to reference a specific member in the query results. It can be passed to the ZAP.CELL function, or used on its own to provide information about the member.

Syntax

ZAP.MEMBER(table, hierarchy, key1, key2, ...)

Argument name	Description
table (required)	A named range that refers to the top-left cell of the query table.
hierarchy (required)	The unique identifier of the attribute or hierarchy that the member belongs to.
key1, key2, ... (required)	The key values that identify the member. The number of keys will depend on the model design.

ZAP.CALCULATION function

The ZAP.CALCULATION function is used to reference a specific calculation in the query. It can be passed to the ZAP.CELL function, or used on its own to provide information about the calculation.

Syntax

ZAP.CALCULATION(table, alias)

Argument name	Description
table (required)	A named range that refers to the top-left cell of the query table.
alias (required)	The alias of the calculation to reference. The alias is the name of the calculation as it appears in the query design, potentially with a number suffix to disambiguate between multiple calculations with the same name.

ZAP.SUMMARY function

The ZAP.SUMMARY function is used to reference a specific axis summary in the query. It can be passed to the ZAP.CELL function, or used on its own to provide information about the summary.

Syntax

ZAP.SUMMARY(table, hierarchy, type)

Argument name	Description
table (required)	A named range that refers to the top-left cell of the query table.
hierarchy (required)	The unique identifier of the attribute or hierarchy that the summary belongs to.
type (required)	The type of summary to reference. Can be one of the following: "Average", "Maximum", "Minimum", "Total", or "Subtotal".

Table formatting

Zap Live tables inherit structure and styling from their source Zap Analytics report, and custom queries use the default Zap Analytics styling. Tables can be further customized using Excel's built-in table formatting options, such as changing the font, background color, and number formatting. See Restrictions for the list of formatting properties that are supported.

When a Zap Live query is updated, formatting will follow the members and measures in the query, rather than the position of the cells. This means that if a member is moved to a different position in the query, its formatting will be applied to the new position, rather than the old one.

Formatting individual data cells in the table is straightforward, but there are some special rules around formatting larger sections of the table:

• Headers - Only the top-left cell of a header is checked for formatting, and after the query is refreshed, the entire header will be formatted with the same style as the top-left cell.

  Before refresh

  

  After refresh

  

• Rows and columns - If an entire row or column of data cells is formatted the same way, when the query is refreshed, the formatting will be applied to the entire row or column in the new results, rather than the cells that were visible before the update. This means, for example, that if you format an entire column of data, and then expand a row to show more data, the column formatting will be applied to the new data cells as well.

  Formatted column before expanding

  

  After expanding the row

  

• Column headers - If all of the column headers are formatted the same way, when the query is refreshed, the formatting will be applied to all of the column headers in the new results, rather than just the ones that were visible before the update.

  All column headers formatted

  

  After adding an extra column

  

• Entire table - If the entire table is formatted the same way, on update the formatting will be applied to the entire table in the new results, rather than just the cells that were visible before the update.

Clearing formatting

Selecting the Clear Formatting button in the Zap Live ribbon will remove any formatting customizations applied to the current selection, leaving the cells with whatever formatting is inherited from the source Zap Analytics report or default styling.

Note that clearing formatting follows similar rules to the formatting described above. For example, if you've formatted an entire column of data, clearing the formatting on a single cell in that column will have no effect; the entire column must be selected in order to clear its formatting.

You can clear all formatting customizations by selecting the entire table using the Select Table button in the Zap Live ribbon, and then clicking the Clear Formatting button.

Installing the add-in

Zap Live is available directly from the Excel Add-in store. Follow the steps below to install:

1. Open Excel.

2. Navigate to the Home tab of the ribbon.

3. Click Add-ins.

   

4. In the Add-ins dialog, search for "Zap Live".

5. Click Add to install the add-in.

   

6. Wait for the Zap Live task pane to appear on the right side of the screen.

More documentation for adding and removing add-ins in Excel can be found here.

Connecting to Data Hub from Excel

Once you have installed the add-in, you will need to sign in to Data Hub:

1. In the Zap Live task pane, locate the Data Hub URL field.
2. Enter the URL of your Zap Data Hub site, e.g. https://my-company.zapbi.com/.
3. Click Sign in.
4. Sign in with your Zap Data Hub username and password.

note

Use the same credentials you normally use to access Zap Data Hub.

Downloading a Zap Live workbook

A Zap Live-enabled workbook can be downloaded directly from a Zap Data Hub report using the following steps:

1. From the Resource Options menu of an analysis report, select Zap Live in Excel.

   

2. Save the workbook to your computer.

3. Open the downloaded workbook in Excel. The Zap Live add-in will be installed if it isn't already.

   note

   The sheet will appear empty until the report has finished loading.

4. Sign in to Data Hub if prompted (see Connecting to Data Hub from Excel).

5. The report will be loaded automatically.

Restrictions

Zap Live tables inherit structure and styling from their source Zap Analytics report, maintaining visual consistency between platforms while adapting to Excel's environment. Due to fundamental differences between these products, certain limitations exist regarding what visual elements can transfer to the Excel interface. The add-in preserves the following formatting attributes:

• Font color
• Background color
• Bold
• Italic
• Underline
• Number formatting

note

Conditional formatting configured in Zap Analytics is currently not supported in Zap Live.

Query design limitations

Custom queries in Zap Live have a limit of 3 hierarchies used in each of the Rows and Columns placeholders, and currently only a handful of analytic functions are supported. For more advanced reporting requirements, consider building an Analysis report in Data Hub, which can then be loaded into Excel using Zap Live.