Navigating the Excel Add-In

OneStream Ribbon

After installing the OneStream Add-In, there will be a OneStream menu item and a ribbon in Excel.

The ribbon is organized as follows:

Logon

This displays the current user and application. A user can logon to a different application by clicking this icon.

Data

Refresh Workbook

This pulls down updated data from the server and refreshes the entire Excel workbook.

Refresh Worksheet

This pulls down updated data from the server and only refreshes the selected worksheet.

Submit Workbook

After editing data in Excel, click this icon to send it back to OneStream. This icon will send data back for every tab in the Excel workbook.

Submit Sheet

After editing data in Excel, click this icon to send it back to OneStream for Cube Views, Quick Views, XFSetCells, and Table Views on the active sheet.

Numeric Precision

Decimals are automatically truncated after the ninth character in a cell or a function.

Calculation

Consolidate/Translate/Calculate

If permission is granted, these calculations can be performed on the selected cell.

Analysis

Quick Views

Create a Quick View

This will create a new Quick View in the worksheet’s selected cell.

Create Copy of Selected Quick View

This will copy the selected Quick View in order to paste a version of it in another spreadsheet.

Create Quick View Using POV from Selected Cell

This will create a new Quick View based on the current POV from the selected cell. This can be done using a Quick View cell’s POV or a Cube View cell’s POV.

For more details on this feature, see Quick View in About Excel Add-In.

Cube Views

Add a Cube View to an Excel sheet.

Click Cube Views > Cube View Connections.
From this window, the cube views added to an Excel workbook can be managed. You can add, remove, edit, or go to styles. Click Add, to add a new Cube View.
Name the connection and then choose the Cube View.
Resize Initial Column Widths Using Cube View Settings is the default setting. If you uncheck it, you can change the columns and save the Cube View. However, if you go back into the same Cube View Connection, the check box will be enabled and you’ll need to uncheck it again to keep your new cube view settings.
Select whether there needs to be inserted or deleted rows and/or columns when resizing. This setting will move around other content in the sheets if the size of the Cube View changed since the last refresh.
You can select Include Cube View Header to add header rows to the spreadsheet.
Retain Formulas in Cube View Content allows you to enter formulas in the Cube View (Excel or Spreadsheet) and retain those formulas pre and post submission of the sheet or workbook. When the sheet or workbook is refreshed the formulas will remain. If the value resulting from the value is different than the value of the OneStream database, the cell will initially become a dirty cell and will turn the cell format to yellow.

NOTE: When using external Excel workbooks, or after any updates to referenced sheets within the same workbook, you must Refresh Sheet to visualize the dirty cells and then Submit Sheet, unless Dynamically Highlighted Evaluated Cells is turned on.
After the Cube View is added, it will appear on the sheet. If formatting was applied to the Cube View (see Cube Views in Presentation), the formatting will come forward into the Excel sheet. Otherwise, apply Excel Styles. These styles are stored in the Excel sheet and can be copied from workbook to workbook. For more information on Excel Styles, see Styles.

NOTE: In order to copy Excel spreadsheet cells into a Data Explorer Grid on the web, click CTRL, select the cells desired, and then click CTRL-C. Navigate to the Data Explorer Grid, select a cell, and click CTRL-V, this will paste the cells into the grid. This can also be done from a Data Explorer Grid into an Excel Spreadsheet.

Retain Formulas in Cube View Content in Excel and Spreadsheet

Retain Formulas in Cube View Content allows you to form Cube View grids of data in Excel, using the Cube Views menu function, that can be linked to other Excel models for easy submission into OneStream. This feature allows formulas (to writeable cells) in Excel (or Spreadsheet) for an attached Cube View to be retained on submission and retrieval instead of being replaced with the value of the represented formula.

The Retain Formulas in Cube View Content feature, allows users to plan, budget or forecast and use the familiar functionality of Excel while still submitting data back to the OneStream database.

Use the Retain Formulas in Cube View Content feature to enter formulas in the Cube View (Excel or Spreadsheet) and retain those formulas pre and post submission of the sheet or workbook. When the sheet or workbook is refreshed, the formulas will remain. If the value resulting from the formula differs from the existing value in the OneStream database, the cell will initially become a dirty cell and will turn the cell format to yellow.

NOTE: When using external Excel workbooks, or after any updates to referenced sheets within the same workbook, you must Refresh Sheet to visualize the dirty cells and then Submit Sheet, unless you’ve turned on Dynamically Highlight Evaluated Cells in the cube view.

Retain Formulas in Cube View Content links to other Excel worksheets or worksheets in other Excel workbooks.

From the OneStream menu, select Cube Views > Cube View Connections.
Click Add in the Cube View Connection window or click Edit if you already have a cube view.
Click Retain Formulas in Cube View Content box and click OK.
Add the Cube View, if one is not already selected, and click Close.

Dynamically Highlight Evaluated Cells in Excel or Spreadsheet

When Retain Formulas in Cube View Content is enabled, the option to Dynamically Highlight Evaluated Cells becomes available to enable. When it’s enabled, every time you make a change to a cell in Excel or Spreadsheet that is referenced in a Cube View, the cell will immediately update and show the update with a change in color. This cell update is called a dirty cell, which indicates that the cell value is different from the information in the OneStream database.

Dynamically Highlight Evaluated Cells saves you a step because the cell changes without requiring a refresh. This feature identifies the values in the cube view that have changed relative to its original value in the database. Evaluating all the cells in the spreadsheet.

Excel users who want to continue working in Excel to access can log in through the OneStream menu, update the cube view content and submit it to the database without leaving Excel. You can also perform these tasks in Spreadsheet within the application.

You can Retain Formulas in a Cube View Content that are related to values within a function, within an existing workbook, within a sheet, within other sheets, in external workbooks, and in external renamed worksheets in Excel. Spreadsheet also offers this functionality, but it doesn’t allow you to point the cell references to external workbooks.

Click Refresh Sheet to see all changes within the cube view content and then click Submit Sheet or activate Dynamically Highlight Evaluated Cells and the cell updates automatically.

When a value for a formula in the cube view is changed by a cell reference, or a function related to a different cell is modified, if the value is different than what is in the database, a dirty cell is created. This means the value of the cell is different than the value of what is in the database and the cell will change colors.

The number of cells with formulas in the cube view determines the amount of time it takes to update the cells. You can turn the feature on or off and only use Refresh Sheet to update the values in the cells. Changes will show very quickly, no matter the size of the worksheet, when using Spreadsheet.

Using Retain Formulas and Dynamically Highlight Evaluated Cells

You can use retain formulas and dynamically highlighted evaluated cells within a cube view to automatically display updated values in an existing workbook, a sheet or sheets, external workbooks, and external renamed worksheets in Excel. You can also do this in Spreadsheet within the OneStream application, however, you can’t point the cell references to external workbooks.

In Excel, go to the OneStream menu and Log on.
Click Cube Views > Cube View Connections.
Click Add.
In the Cube View Connection window, click Cube View.
Select your choice and click OK.
Click Retain Formulas in Cube View Content to activate Dynamically Highlight Evaluated Cells.
Then click Dynamically Highlight Evaluated Cells so you can see the changes as they are made.
Even if you don’t activate the dynamically highlight evaluated cells feature, you can click Refresh Sheet after you make changes to see them.
If you’re prompted, click OK once you’ve selected the parameters for the cube view.
Once the cube view has been added, you can click Edit to review, if needed.
Make changes to the sheet and press <Enter> to see the updated cell, which will change from white to yellow.
Click Submit Sheet to automatically save changes to the database.

Use Cases

These use cases are for both Excel and Spreadsheet unless otherwise noted.

The placing of formulas or cell references. Retain Formulas can reference the following types of formulas. In all instances the formula will stay after refresh and/or submission.

Cell References of individual cells of data on the same sheet.

Cell References to a cell on the same sheet, factored by another value.

Cell References to cells on other sheets. These can also be factored by another value as well.

Referenced cell(s) on another saved workbook can also be factored by another value. (This applies to Excel only.)

Best Practices

Well-Formed Grid

It is suggested to create a “Well-Formed Grid” (Root.List or Comma Separated List) in Cube Views. When using this “Well-Formed Grid” (Root.List or Comma Separated List) in Cube Views, the Excel/SpreadSheet relative (=C2) and absolute formulas (=$C$2) will be retained.

However, when using these relative and absolute formulas within an Excel/Spreadsheet formula, users can use either the cell reference or text within the formula depending upon how members will be added or removed:

=VLOOKUP(D30,Sheet1!A:B,2,FALSE) will work in a List or Comma-Separated list (Well-formed grid) when Accounts are added to the end.
=VLOOKUP("52000 - Promotions",Sheet1!A:B,2,FALSE) will work in a case when a Member of a Row is moved up or down.

Member Expansion Functions

When using Member Expansion Functions in Cube Views for Excel and SpreadSheet, the cell being referenced within the function (Vlookup, etc), will need to be adjusted and/or referenced as text.

=VLOOKUP("52000 - Promotions",Sheet1!A:B,2,FALSE) will work in a Dynamic or when a Member of a Row is moved.
=VLOOKUP(D30,Sheet1!A:B,2,FALSE) will NOT work in a Dynamic or when a Member of a Row is moved as this is using the cell ref of D30.

Other Notable Considerations

Deselecting the Retain Formulas for Cube View Content will eliminate all formulas that were established /existed on the Cube View grid.
Pivoting the existing Dimensions of the Cube View will break formulas.
Changing the “structure” of the Cube View grid in the rows or columns will also break the formulas. For example; If you have Account, Entity, UD3 as the dimensions used in the row and switch it to UD3, Entity, Account, it will break the formulas.
Users can change the POV to select a new dimension. This will change the Cube View results but retain the existing formulas that were established. The user at this point can choose to utilize the existing formulas, modify or delete. If the original formulas are modified or deleted, the last action will be saved.
Linking to a white cell (writeable cell) to another cell in a different workbook will work ONLY in Excel and NOT in Spreadsheet.
Prior to establishing links to an external workbook, the user should save the external workbook being referenced.
When the user renames or saves as the (referenced) file, the user will need to update the links to the newly created file. Updating the links on the spreadsheet should be done BEFORE doing a refresh or submit.
Formulas with cell references (VLOOKUP, INDEX(MATCH(, etc) that return errors (#N/A, #ERROR, etc) or non-numeric data will not retain the formula and return to its original value from the Cube View ; this error text cannot be converted into a number so the formulas will not retain.
If a Dimension Member Name is renamed; i.e.; “52200 – Rent” is now “52200 – Rent Commercial”, the formula will break.

File Explorer

Use this option to upload and download files.

Create Folder
This creates a new folder under the selected folder on the left-hand side of the File Explorer pane.

Delete Selected Folder/File
This deletes the selected folder on the left-hand side of the File Explorer pane or the selected file.

Edit Selected Folder/Edit Selected File Information
This edits the Description, Maintenance Group, and Access Group for the selected folder or file.

Upload File
This uploads the selected file and allows the user to save.

Download Selected File
This downloads the selected file and allows the user to save.

Download Selected File’s Content File
This downloads the selected file’s content file and allows the user to save.

Data Attachments

This pulls up the Data Attachments dialog to show existing comments or attachments on a selected cell, or to allow data attachment edits.

Cell Detail

Enter Cell Detail for a Cube View or Quick View data cell. See Cell Detail in Using OnePlace Cube Views for more details on this function.

Drill Down

Drill down on a specific cell in order to see more details or gather more information. See Drill Down in Using OnePlace Cube Views for more details on this function.

General

Copy POV from Data Cell

This captures the Point Of View of the currently selected cell. After clicking this option, the Paste POV As XFGetCell becomes available and the Copy POV From Data Cell goes to grey. The ability to paste this into another cell is now available and OneStream will automatically convert this into an XFGetCell formula with all of the appropriate Parameters.

Paste POV As XFGetCell

This option is only available after clicking Copy POV From Data Cell. After clicking this option, OneStream will convert the copied cell into an XFGetCell formula. Click Refresh Data to retrieve the data.

Convert to XFGetCells

This will convert an existing Quick View into an XFGetCells. After clicking this option, OneStream will prompt with the following: Are you sure you want to convert all of the data in Quick View ‘Name of the Quick View’ to XFGetCells? By clicking OK, the Quick View definition will be deleted and converted to XFGetCells.

Object Lookup

Use the Object Lookup to insert objects from OneStream into Excel such as Foreign Exchange Rate Types when building formulas. If creating an Extensible Document in Excel, users can also use the Object Lookup to insert Parameters, Substitution Variables, or Image Content. See Object Lookup in Presenting Data With Books, Cube Views and Dashboards for more details on this feature.

Select Member

Select a Dimension Type from the drop-down list in order to view the Members of that Dimension. Select a Member of the hierarchy, and the Member name will display in the selected cell.

Save Offline Copy

Use this to save an offline copy of the current worksheet without the functions. Users without the Excel Add-In can open this copy and see the saved values.

Spreading

Allows users to see what type of spreading was used to spread data values over several columns or rows without having to type in each cell’s values.

Spreading Types

Spreading (Even) This distributed the active cell amount evenly across all selected cells.
Spreading (445) This distributed the active cell amount using a weighted 445 pattern across all selected cells.
Spreading (454) This distributed the active cell amount using a weighted 454 pattern across all selected cells.
Spreading (544) This distributed the active cell amount using a weighted 544 pattern across all selected cells.
Spreading (Factor) Multiplied all cells in the data range by the specified rate.
Spreading (Fill) Filled all cells in the data range with a specified value.
Spreading (Proportional) Distributed a value in all cells in the data range by the proportional based on the number of cells in the range.
Spreading (Accumulate) This starts with the active cell amount and cumulatively multiplies it by the specified rate.
Spreading (Clear) Cleared all data that was previously entered in the data range.
Flag Selected Cells Flags selected cells so the original amount in the cell is retained during the spreading process.
Clear Flags Select this to clear any flagged cells.

Administration

Display Context Pane

In order to display the OneStream task pane on the right-hand side of the screen, check this box. To hide the task pane, uncheck the box.

Preferences

General

Enable Microsoft Sign In

Set this to True if Azure is used for authentication to sign into the Excel Add-In. Setting this property to true will enable the Microsoft Sign In button on the login dialog allowing users to enter their Azure credentials. Set this to False, to disable the Microsoft Sign In button and users will be prompted to enter their username and password.

Enable Macros for Event Processing

If set to True, this enables Excel macros for OneStream API calls. The default is False.

Invalidate Old Data When Workbook is Opened

If set to True, this will force a data refresh on the opened workbook. The default is False.

Use Minimal Calculation for Refresh Sheet

This is for Excel Add-In only, not the Spreadsheet feature in OneStream Windows App. The default is True this will only calculate formulas and Excel functions in the active sheet. Set to False to revert to a full calculation of all workbooks and all sheets.

NOTE: Performance is best when Excel is set to use Manual Calculation Mode.

Disable Interactive User During Refresh

Accounts for a known Excel situation when running on certain touchscreen hardware. If the Refresh Sheet or Refresh Workbook is pressed but the cells containing functions do not complete their calculations when processed, change the Disable Interactive User During Refresh setting under Preferences to True.

NOTE: Setting this to True may result in incompatibility issues with other Excel Add-ins.

Retain All Formatting when Saving Offline Copy

This is for Excel Add-In only, not Spreadsheet. The default is False to derive basic formatting and better performance. Set this to True to obtain all character by character formatting, this will force a data refresh on the opened workbook.

Use Add-In Compatibility Filter

When True, only cell selection change events, such as keystrokes or mouse clicks, are allowed. Third-party add-ins and macros cannot change cells. When False, users, third-party add-ins, and macros can make cell changes.

Quick View Double-Click Behavior

Default Expansion for Rows/Columns
This determines what level of expansion displays when a user double-clicks a Quick View Row or Column Header. NextLevel is the default setting and allows multiple expansion paths when a user double clicks a row or column header. There is also the ability to double-click an expanded item to collapse it again. This feature only works with the NextLevel setting.

For the following properties, See Quick View in About Excel Add-In In
Default Display Settings for New Quick Views
Default Suppression Settings for New Quick Views

Excel Calculation

The Excel Calculation icon has the option of Automatic, Automatic Except for Data Tables, and Manual. It is recommended that the Calculation be set to Manual when using OneStream spreadsheets because the Automatic setting results in an Excel re-calculation every time a OneStream’s interactive workbook changes data (e.g., when navigating a Quick View). However, this is not forced because a user might prefer Excel’s Automatic calculation, especially when there is not a significant amount of OneStream data in the workbook.