Ad Hoc Analysis

You use Ad Hoc Analysis to create data views. A data view is a "window" into a model and contains layout information for presenting dimensional data in a grid format. Data views are used in all interactions with model data, whether to add, edit, analyze, or report on it.

What you can do

In Ad Hoc Analysis, you can do the following:

Open Ad Hoc Analysis

You can launch Ad Hoc Analysis a couple of ways.

Open the Ad Hoc Analysis module

  • Click the logo at the top-left corner and select Ad Hoc Analysis.

Open Ad Hoc Analysis through a file

  • In Document Explorer, click a data view file; the file opens in a new  Ad Hoc Analysis tab.

Set options

You can set options for how you edit data and how data is displayed.

  • In the toolbar, select Options, and change any of the following:
    • Move selection after 'Enter' key: In a cell, where the focus goes after you press Enter.
    • Display missing data as: The text that appears in a cell that has missing data.
    • Display 'No Access' cells as: The text that appears in cells that users have no permission to view.
    • Automatically resize column widths: Turn on if you want columns to change to accommodate their data.

Open a data view

Open an existing data view from inside Ad Hoc Analysis

  • Select File >  Open.

Start a data view

  1. Select File >  New.

  2. If you have more than one model, select a model and click OK.

    The Edit Layout dialog opens.

  3. Define your data view's layout.

Add or edit data

  • Turn on Data Entry Mode.

Copy and paste data (optionally including headers)

  1. Select the cells and/or headers.

  2. Select Edit and Cut, Copy, or Paste. (You can also use Ctrl-X, -C, and -V.)

     Tip:  A cut/copied selection goes into the system clipboard and can be pasted outside of FP&A.

Copy and paste data with headers

Copy a selection of cells and, when you to paste to Excel, include their row and column headers.

  1. Select the data cells alone.
  2. Select Edit > Copy with Headers.
  3. Paste into Excel and the cells' row and column headers are included.

Undo/redo a cell change

  • Select  Edit > Undo or Redo.

Navigate through your actions

You can move the data view backward or forward through your most recent actions.

  • At the left of the toolbar, click or :

The actions that Ad Hoc Analysis keeps track of are: Expand, Collapse, Drill Up/Down/Replace, Pivot, and changing the member selection through Edit Layout or Member Selector.)

Change the selected member for a row or column

  • Right-click the row or column header and select Choose Members.

Hide rows or columns that do not contain data

  • Select  Edit > Exclude Missing Rows or Exclude Missing Columns.

     Tip:  This is the same as selecting the Exclude missing rows or Exclude missing columns check boxes in Edit Layout.

Hide rows or columns for which the user has no permission

  • Select  Edit > Exclude Rows with No Access or Exclude Columns with No Access.

     Tip:  This is the same as selecting the Exclude rows with no access or Exclude columns with no access check boxes in Edit Layout.

     Note:

    Turning on this option for a data view also hides No Access data from the user when the data view is used in the following:

    • Dashboard charts
    • Analyzer

Automatically adjust a column's size

  • Click the column header and select Edit > Autofit Column.

Swap the axes

  • Select  Actions > Pivot.

Save your work

  • Select File > Save or Save as.

     Note:  Ad Hoc Analysis also has an autosave feature.

Manage dimensions

You use the Pages drawer to manage the dimensions in an Ad Hoc Analysis data view.

  • In the side panel, click Pages.

Manage the data view layout

You use Edit Layout to define the screen layout for the dimensions, members, and pages in a data view.

  • In the side panel, click Edit Layout.

Get combination details

You can get detailed information on any member combination.

  • Select  View > Cell Properties.
 Tip:

When a cell contains #Err this indicates an MDX calculation error. You can view the details of the error in the Cell Properties dialog, in Miscellaneous:

Get a quick summary of values

You can get an ad-hoc summary for a cell selection from the data view's footer.

  • Select any group of numeric cells.

    The Average, Count, and Sum for those cells appears in the footer:

See combination history

To see the history of manual changes made to a member combination:

  • Right-click the cell and select Data History.

    The Data History dialog opens.

Add cell formatting rules

Adjust numeric data

  1. In the data view's grid, select one or more base leaf member cells.

  2. Select  Actions > Adjust Data.

    The Adjust Data dialog opens.

View related data

Use the drilling options to browse data structures outside the data view.

Use Spreading

Turn on spreading

If spreading is not available, you can turn it on:

  • Select Edit > Spreading.

Use spreading

  1. In a non-leaf cell, type the value, and press Tab. The Time Spread Method dialog opens.

  2. Select a spreading option and click OK.

     Tip:  If you want the method that you selected to be used automatically each time, clear Prompt every time a spread is performed.

Use supporting details

Show/hide supporting details indicators

A cell with a comment shows a red triangle at the top right corner; a cell with a line item schedule shows a blue triangle at the bottom right corner.

Add or view supporting details

These are Comments and line item schedules.

Create line item reports

These are reports for the line items in a data view or template.

Download to Excel

To download the current data view to an Excel spreadsheet:

  • Select File > Download Excel.

Along with the data view the Excel file also includes the details of the current Page member: