Zap Live for Excel

Zap Live is an Excel add-in that integrates Excel with Zap Analytics, enabling direct access to reports within Excel worksheets. This section covers installation, connection setup, and core functionality of the add-in.

note

Zap Live is currently only supported on Zap Cloud.

Loading a report in Excel

There are two ways to load a Data Hub report in Excel:

• Download a report from Data Hub as a Zap Live workbook.
• Load the report from within Excel.

Downloading a Zap Live workbook

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.

Loading a report from within Excel

1. If you haven't already, install the Zap Live add-in and connect to Data Hub.

2. Open the Resource drop down in the Zap Live task pane.

   

3. From here, you can choose any analysis report you have access to.

4. Click Load to bring it into Excel. The table data will be displayed within the current sheet.

Designing a custom query (preview)

Instead of loading existing reports from Data Hub, you can create your own custom queries directly within the Zap Live task pane.

tip

Custom query design in Zap Live is a streamlined version of the full query design experience in Data Hub, optimized for use within Excel. It may be useful to familiarize yourself with the fundamental concepts of Data Hub query design before using this feature.

When adding a new query, select Create custom query. This will display a Design tab, which you can use to build your query.

The Design tab will be empty to begin with. Select a model from the drop down to load its dimension tree. From here, you can drag measures, levels, members, named sets, and calculated members into the Rows and Columns placeholders below to add them to the query. The results will automatically be loaded into the query's sheet.

Elements in the placeholders can be moved within the placeholder, or to another placeholder, by dragging them around, or by clicking on them and choosing a Move option from the context menu.

Applying functions

Member, measure, and calculated member elements can have a range of time intelligence functions applied to them. To do this, click on the element in a placeholder, and select one of the Functions options from the context menu. Selecting a different function will replace the current function, while selecting an already-applied function will remove it from the element.

Adding filters

You can apply filters to your query to refine the data displayed in your reports. To add a filter, drag a member or level onto the Filters placeholder. Filters 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 the selected member will not filter the query directly. Instead, it will be used as the context 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.

Opening a custom query in Data Hub

From the More options menu at the top-right of the Zap Live task pane, select Open in Data Hub. This will open Data Hub in a new browser tab and create a new analysis report based on your custom query. From here, you can design much more complex reports using the full range of Zap Analytics features.

Changes to this report will not affect the custom query in Zap Live, and vice versa. To use the customized report, save it in Data Hub, and then load it into Excel.

Designing the model in Data Hub

Similar to the dimension tree in Data Hub, if you have model design permissions, you can navigate directly to the pipeline that produces a measure group or dimension by right-clicking on it and selecting the Open pipeline… option. This will open the pipeline in a new browser tab, allowing you to view the model and make changes if necessary.

Global filters

Global filters allow you to filter data across multiple queries in the same workbook. To add a global filter, select Global filters from the queries drop down at the top of the Zap Live task pane. This will provide a Design tab similar to custom queries, with only a Filters placeholder available. Note that only a single data model can be used for global filters, and they will only apply to queries that use the same model.

Similar to filters in custom queries, global filters can be turned into configurable slicers. These slicers will appear in the Slicers tab of all affected queries, allowing you to change their selections from any query in the workbook.

Adding another report to Excel

Once you've loaded a Data Hub report in Excel, you can add more reports by selecting Add a new query... from the query dropdown at the top of the Zap Live task pane. You can switch between multiple queries by selecting them in the query dropdown.

important

You can only have one Zap Live query per sheet.

Working with Zap Live queries

Zap Live consists of a task pane for loading reports and slicing data, and a ribbon for manipulating tables. This section explains how to access interface elements and perform common operations.

Refreshing data

The data displayed in Zap Live tables can be updated through the Refresh ribbon button. After pressing, the following refresh types are available:

• Refresh all to refresh all queries in the workbook.
• Refresh table to refresh only the currently selected table within the sheet.

The selected query will show a loading message while it is being refreshed.

Expand/collapse

Zap Live tables support hierarchical data display with expandable and collapsible rows, allowing users to control the level of detail shown.

Users can expand or collapse hierarchical data directly within Excel by clicking the arrow icon displayed in header cells.

Additionally, Zap Live enhances this functionality through dedicated ribbon controls. The Expand Member and Collapse Member options apply to a single member, while the Expand Level and Collapse Level options affect all members at the current depth in the hierarchy.

Accessing the task pane

The task pane can be opened at any time by clicking Show Queries from the Queries section of the ribbon. It shows the current report's name in the query dropdown, and the report's slicers (if it has any)

Slicing

Slicers provide interactive filtering capabilities for Zap Live tables. When a report loads into Excel, associated slicers from the Zap Analytics report become accessible in the Zap Live task pane.

Navigate to the Slicers section within the task pane to view all available options for the current report. Select specific values from the available options to slice the data. The table data updates automatically to reflect these selections.

Customizing tables

The automatic nature of Zap Live tables prevents safely adding custom rows or applying custom cell formatting directly to the table. When these tables are updated during normal operations (such as expanding/collapsing hierarchies, refreshing data, or applying slicers), any manual customizations to the table will likely be overridden when the table is refreshed. Zap Live provides the Automatic table toggle to disable these structural and formatting table updates, while continuing to update the data of tables when refreshing queries.

important

Enabling Automatic table will immediately re-load the entire table to the sheet it was originally loaded in. The table will be loaded from cell A1 and overwrite any cells in its required range.

Copying tables

An alternative solution to customizing tables is to create a copy of the automatic data table that you can format and edit as required:

1. Select a cell of the automatic table you wish to copy and click Select in the Table section of the Zap Data Hub ribbon.
2. Copy this table (press ctrl + c or Right click > Copy).
3. Paste this table onto another sheet (press ctrl + v or Right click > Paste).

The cells that have been pasted will contain member and measure formulas that query Zap Analytics directly, instead of cell references to the first sheet. This means you can now rearrange the original table without affecting the new one. You can now update the formatting of the copied cells or add new rows with Excel formulas to extend the report. Because this table is not interactive (you can no longer expand or collapse the rows), these cell references will be maintained when refreshing your data. Values will still be updated when refreshing or slicing the original table, but no rows will be added or removed.

important

Never use cell references to the original interactive table on the first sheet, as expanding or collapsing will break your formulas. A cell must be visible on the original table for data to appear in it in the copied table.

Zap functions

Zap Live tables are implemented using Zap.Cell, Zap.Member and Zap.Calculation functions in the cell formulas instead of raw values. This is primarily to ensure that references to these cells are correct after the table is refreshed.

If these functions are causing slow performance, tables can be loaded using raw values by toggling Cell functions in the task pane. If the query is not using an Automatic table, this setting will be disabled.

note

Cell functions are automatically disabled when loading large tables (1000s of cells). This can be manually reverted by toggling the setting.

Ending your session

To sign out of Zap Live, click Sign Out on the ribbon to disconnect from Zap Data Hub.

Appendix

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.

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.