Quickly build Ad Hoc Reports to gain insight into your data.

 

Why use this feature?

Ad Hoc Analysis is the fastest way to explore your data and make simple reports. Use Ad Hoc Analysis for:

• An intuitive drag-and-drop interface that is quick to learn and easy to use.
• Quickly gaining context in your data, exploring parent and bottom level data and transactional and historical save data.
• Saving your Ad Hoc reports to OneDrive or your desktop, which can be opened directly at a later time.

In this article, you will learn how to use Ad Hoc Analysis, including how to create and save Ad Hoc reports, how to quickly manipulate your data, best practices for use and troubleshooting tips.

 

Before you begin

If you would like to have this feature enabled, contact your Customer Success Manager or an Admin can follow the steps below:

1. Select the Admin tab. 
2. Select User Interface Properties. 
3. Select +Add Property. 
4. Under Property Name, type in contributorAdHocToggleEnabled or viewerAdHocToggleEnabled.
5. Select Create. 
6. Set the toggle for Value to true. 

Once this feature has been enabled, Ad Hoc Analysis can be accessed by a Manager at any time through the Manager tab.

In special cases, you may get access for Contributors to leverage Ad Hoc Analysis. In this case, Admins can grant Contributors Ad Hoc Analysis access by following the steps below:

1. Select the Admin tab.
2. Select the Policies page.
3. Select Default UI from the sidebar
4. Set the toggle for Tasks: Ad Hoc Reporting to ON.
5. Contributors can now access Ad Hoc Analysis.

 

Table of contents

• How to
  • 1. Creating and Opening your Ad Hoc Analysis Report
    • • Using Ad Hoc on Desktop Excel vs Excel Online
      • How to Create Your Ad Hoc Report
  • 2. Selecting a Data Model
  • 3. Selecting Dimensions for Rows and Columns
  • 4. Selecting Members
    • • Selecting Members via Parent Operation
      • Searching for Members
      • Clear Selection
  • 5. Refreshing to Retrieve Data
    • • Refresh Performance
      • Copy and View Query
  • 6. Interacting Directly with the Data View
    • • Zoom In
      • Zoom Out
      • Remove
      • Drill Transactions
      • Drill Down
      • Drill Saves
  • 7. Saving and Reusing Ad Hoc reports
  • Additional Information
    • • Parent Member Position
      • Suppress Zeros
      • Inserting Rows and Columns for Report Customization
  • Notes and Limitations

 

How to

1. Creating and Opening Your Ad Hoc Report

You can create and open your Ad Hoc report in one of two ways:

1. Desktop Excel
2. Excel Online

The following section outlines the differences between these two options.

 

Using Ad Hoc on Desktop Excel vs Excel Online

To use Ad Hoc Analysis, you must have an active Office/Microsoft 365 Subscription. You can use Ad Hoc Analysis through either Desktop Excel or Excel online. The differences between the two are described below.

You should use Ad Hoc through Desktop Excel if:

• You are more comfortable in Desktop Excel.
• You would prefer to manage your files on your desktop, where they can easily be accessed from a single computer.

You should use Ad Hoc through Excel Online if:

• You are more comfortable in Excel Online.
• You would prefer to manage your files on the cloud, where they can be accessed from any computer.

 

How to Create Your Ad Hoc Report

1. Select the Manager tab (or the Contributor/Tasks tab, if applicable).
2. Select the (Create Ad Hoc Report) button from the sidebar.
   
   
3. Select to open via Desktop Excel or Excel Online.
    
   • If you open via Desktop Excel: A new file will be downloaded. Select the document to open it.
   • If you open via Excel Online: A new browser tab will open with your Ad Hoc report.
4. Once your Ad Hoc report is open, you can start building your report by selecting a data model.

 

2. Selecting a Data Model

Once the Ad Hoc Builder is loaded, you'll be prompted to select a data model. 

To select a data model:

1. Select your intended data model from the task pane.
2. Select Apply.
   
3. The task pane will refresh to display your selected data model and allow you to select dimensions for filters, rows and columns.

 

 

3. Selecting Dimensions for Rows and Columns

Now that you have a data model selected, you will see its dimensions in the Filter category. Having dimensions in the Filter box is equivalent to having page options when building a template. All data shown in the report will be filtered by the members selected in the Filter box. 

To build an Ad Hoc Analysis, you must have at least one dimension assigned to row and column. To accomplish this, drag-and-drop your selected dimensions into the Row and Column boxes.

Multiple dimensions can be placed in the Row and/or Column boxes. The following section, Rows or Columns with Multiple Dimensions, explains this in more detail.

 

Rows or Columns with Multiple Dimensions

You can click-and-drag multiple dimensions into the Row and/or Column category. When setting up multiple dimensions in rows or columns, the order of dimensions in that box defines how those dimensions will be laid out on that axis. Simply rearrange their order to adjust their layout in the data view.

For example, the following image has multiple dimensions (Period and Department) in row. The Period dimension appears before the Department dimension in the row of the Ad Hoc report because the Period dimension is listed above the Department dimension in the task pane.

 

4. Selecting Members

To define which data you want Ad Hoc to return, you will need to select some members.

1. Select the (Pencil) icon to the right of the dimension you want to specify the member for.
2. Select the checkbox next to each member you want to select. You can select the (Arrow) icon next to a parent member to view/select child members.
3. Select OK when you've finished making your selections.
4. If you’ve selected multiple members, the number of selections will be displayed in parentheses to the right of the dimension name.
   
   If you’ve selected one member, the selected member's name will be displayed in parentheses to the right of the dimension name. 

In the example below, three members have been selected from the Period dimension and “(3 selections)” is listed next to that dimension. For the Department dimension that only has one member selected, the department name “(D10)” is included.

 

Selecting Members via Parent Operation

To select a group of members that fall under a parent, hover over the vertical ellipses to the right of the member name. This button will only appear next to parent members.

There are three Parent operations available—these work the same way as in other Vena features:

• Children, which selects all direct children
• Descendants, which selects all members that roll up to the given member
• Bottom Level, which selects all members that roll up to the given member, that do not have children themselves

See an example of selecting the Children of Statistical Accounts below. After the Children operation of Statistical Accounts is selected, the checkboxes for Accounts 5004R, 5010R, and 5500R are selected.

 

 

 

Searching for Members

To select specific members more quickly, simply type the member name into the search bar. Your search results will include each matching member from that dimension, as well as all of those members’ parents. Searching for members does not select any of them, you will then have to select the checkbox next to the specific members you want yourself.

 

Clear Selection

The Clear Selection button is a shortcut for quickly removing all current member selections for the dimension you are viewing. This can be especially helpful if your current selections do not successfully return data and you would like to clear them all out and start again.

 

5. Refreshing to Retrieve Data

Once your Rows, Columns and Filters are set, use the (Refresh) button to bring data back to the sheet. Data and row/column headers will appear in the top-left of the current Excel sheet.

 

 

Refresh Performance

In cases with poor refresh performance, you can add additional filters to reduce the amount of data being returned and the query time.

As guidelines, queries run more quickly when:

• The parent values requested have fewer bottom-level members
• There are fewer parent values requested
• There are fewer values requested overall
• There is less data in the data model

 

Copy and View Query

If you have performance concerns or you want to copy/view the query, you can do so directly in the task pane. Select the vertical ellipsis to the right of the data model name. If you select Copy Query, the query will be copied to your clipboard. If you select View Query, the query will appear in a pop-up window.

 

6. Interacting Directly with the Data

Ad Hoc Analysis provides several tools for exploring your data directly on the sheet. These tools are all located on the dark green sidebar of the task pane, as shown below

A) Zoom In
B) Zoom Out
C) Remove
D) Drill

 

Zoom In 

If you want to see a parent member in your data view broken down into its child members, instead of changing your selection through the Filter category, you can use the Zoom In tool. To use Zoom In, first select the cell you want to expand in the report, then select the Zoom In button. The children will appear in addition to the parent cell.

In the example below, the member All Departments is selected and the Zoom In feature is being applied.

Once selected, the Children of All Departments (D40, D30, D20, D10) all appear in the report, in addition to All Departments.

This selection will also be updated in the Filter category for that dimension, so you can always return to the Filter category to alter your selections. If you attempt to Zoom In on a member that is not a parent, you will receive an error message.

 

Zoom Out

To undo a Zoom In, or to simply view your data at a higher level, you can use the Zoom Out tool. To use Zoom Out, select any child member in the report and select the Zoom Out button. This will remove the selected members and its siblings. Only the parent member will remain. As with Zoom In, your selection will be updated in the Filter category for the corresponding dimension.

In the example below, the member D10 <Marketing> is selected and the Zoom Out feature is being applied.

Once Zoom Out is selected, D10 <Marketing> and all sibling members (D40 <Sales>, D30 <HR>, D20 <IT>) will no longer be visible in the report. Only the parent member All Departments will remain.

This selection will also be updated in the Filter category for that dimension, so you can always return to the Filter category to alter your selections. If you attempt to Zoom Out on a member that does not have a parent member, you will receive an error message.

 

Remove

If you want to reduce the number of members being shown in your data view, instead of going back to the Filter category, you can use Remove. To use remove, select a header in your data view and click on the Remove icon. This will remove the selected member from the data view, as well as from the corresponding dimension’s Filter category. It is not possible to remove columns or rows with multiple dimensions. In the example below, 2019 is removed from the report.

 

Drill Transactions

If you store additional information about intersections in staging tables, such as transactional data, you can view that data through Drill Transactions. To Drill Transactions, select a bottom-level member, hover over the Drill button and then select Drill Transactions.

You are prompted here to select which Staging Table you want to drill to and which columns you would like to be returned.

The results of the Drill Transaction will be open on an additional sheet.

 

Drill Down

If you want to further investigate the values that roll up to a parent intersection, you can use Drill Down to view this. To Drill Down, select a bottom-level member, hover over the Drill button and then select Drill Down.

Your drill down data will open in a new sheet.

 

Drill Saves

If you want to further investigate a bottom-level intersection, you can use Drill Saves to view the most recent historical saves. To use Drill Saves, select a bottom-level member, hover over the Drill button and then select Drill Saves. Your historical save data will be shown in a pop-up.

 

7. Saving and Reusing Ad Hoc Reports

Ad Hoc reports can be saved and reused. When you open a saved Ad Hoc report, the report will be exactly how you left it, including your dimension and members selections in the Ad Hoc side panel.

When you create a new Ad Hoc report through Desktop Excel, the report is downloaded by your browser onto your computer. To save your report while you’re working on it, simply use the Excel Save (or Save As) functionality.

When you create a new Ad Hoc report through Office Online, the report is loaded into your connected OneDrive account. Your report is saved any time you make a change, so you can leave your file at any time and your work will be preserved.

To quickly access your OneDrive account from an Ad Hoc report open in Office Online, first select the file name in the top-left corner of the screen. When the pop-up window opens, select Ad Hoc in the location section. This will take you directly to a folder that Vena created to contain your Ad Hoc reports—although they can still be reopened outside of that folder.

In each case, you can duplicate, rename and transfer the Excel files that contain your Ad Hoc reports. These files are also transferable between Desktop Excel (Windows and Mac) and Office Online. Simply upload or download the Excel files to/from your OneDrive account.

 

Additional Information

Parent Member Position

By default, the parent member will appear below its child members. You can switch the formatting for parent members to appear above its child members by following the steps below:

1. Hover over Account in the bottom-left corner of the task pane.
2. Select Settings which will appear to the right of the Account icon.
3. Uncheck Below Children from the Parent Member Position Category.
4. Select Apply.
   
5. Select the Refresh button to apply the changes to your current report.

 

Suppress Zeros

By default, any intersections that do not contain any values will not appear. This is to avoid having many empty rows/columns. If you would prefer to see these empty intersection in your report, you can switch the settings by following the steps below:

1. Hover over Account in the bottom-left corner of the task pane.
2. Select Settings which will appear to the right of the Account icon.
3. Uncheck Suppress Zeros from the Data Category.
4. Select Apply.
   
5. Select the Refresh button to apply the changes to your current report.

 

Inserting Rows and Columns for Report Customization

To use the power of Excel during Ad Hoc Analysis, you can insert additional columns or rows in between your data rows or columns, then add to them values, formulas and formatting.

To do this, simply add rows or columns as you would normally in Excel. The easiest way is by right-clicking on the number of a row and selecting Insert (on Desktop) or Insert Rows (on Office Online).

Ad Hoc recognizes which rows or columns you have inserted and will effectively ignore them. This will allow your values, formulas and formatting in those rows/columns to persist through a refresh.

These 'ignored' columns and rows will be removed if:

• A dimension is pivoted to or from the corresponding axis.
• Member selection is changed on a dimension in the corresponding axis.

 

Notes & Limitations

• Ad Hoc files cannot be reopened via SSO. If your tenant is SSO-enabled, you cannot reopen Ad Hoc files.