OneStream Ribbon

After installing the Excel Add-In, there will be a OneStream menu item and a ribbon in Excel. The ribbon contains these categories: 

 

NOTE: When working in Excel, decimals are automatically truncated after the ninth character in a cell or a function.

OneStream

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


Explore

The Explore category enables you to quickly build a Quick View, Cube View, or Table View in Spreadsheet or Excel.

Quick Views

Create Quick View

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

Copy Selected Quick View

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

Paste Quick View

This will paste the copied quick view to the current spreadsheet or another workbook.

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.

See Quick Views for more information.

Cube Views

Cube View Connections

Create a new Cube View Connection in the selected cell. .

Selection Styles

Create selection styles for Cube Views. See Selection Styles for Cube Views.

Manage Selection Styles

This enables you to modify available selection styles.

Table Views

Select Table Views to insert a Table View into your Excel sheet.

Analysis

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.

Drill Down

Drill down on a specific cell in order to see more details or gather more information.  See Drill Down.

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 gray.  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.

Refresh

Refresh Sheet

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

Refresh Workbook

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

  Refresh Sheet Refresh Workbook
Function Behavior Refreshes the selected tab only Refreshes all tabs in the file
Data Impacts Clears all dirty cells on the selected tab only Clears all dirty cells on all tabs regardless of selected tab
Parameter Impacts (CV Only) Prompts the user with any Parameters used on the selected tab Prompts the user with all the Parameters used in the workbook

Calculation

Consolidate/Translate/Calculate

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

Submit

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.

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 Submit Workbook
Function Behavior Identifies data changes on the selected table and stores these changes to the database Identifies data changes on every tab and stores these changes to the database
Data Impacts Submits all data for the selected tab only Submits all data for all tabs
Parameter Impacts (CV Only) No prompts No prompts

Spreading

Enables 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

Fill

This fills each selected data cell with the value in the Amount to Spread property.

Clear Data

This clears all data within the selected cells.

Factor

Multiply all cells by the specified rate.

Accumulate

This takes the first selected cell’s value and multiplies it by the rate specified. It then takes that value, multiplies it by the specified rate and places it in the second cell selected, and does this for all selected cells.  For example, four cells are selected and the first cell has a value of 900.

Even Distribution

This takes the Amount to Spread and distributes it evenly across the selected cells.

445 Distribution

This takes the Amount to Spread and distributes it with a weight of 4 to the first two selected cells and a weight of 5 to the third cell.

454 Distribution

This takes the Amount to Spread and distributes it with a weight of 4 to the first selected cell, a weight of 5 to the second cell and a weight of 4 to the third.

544 Distribution

This takes the Amount to Spread and distributes it with a weight of 5 to the first selected cell and a weight of 4 to the second and third cells.

The Accumulate Spreading is setup as follows with a rate of 1.5:

When the spreading is applied the outcome is as follows:

Each cell’s value is a factor of the previous cell amount.

Proportional Distribution

This takes the selected cell’s value, multiplies it by the specified Amount to Spread, and then divides it by the total sum of all selected cells. If all the cells have a zero value, the Amount to Spread will behave like an Even Distribution.  

A proportional amount of 50,000 is applied to the cells:

Result:

Spreading Properties

Amount to Spread

Specify the value to spread over the selected cells.  The value defaults to the last cell selected. The way the amount in this field spreads varies by Spreading Type.

Rate (Factor and Accumulate Spreading Types Only) Enter a rate to multiply by a cell value.

Retain Amount in Flagged Input Cells

Users can flag specific cells in order to retain the data within the cell. If this property is set to True, spreading will not apply to the selected flagged cells.

Include Flagged Readonly Cells in Totals

Set this to True to include locked base-level cell values when calculating spreading totals. True is the default.

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.

File Operations

File Explorer

Use File Explorer 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.

Save to Server

Use this option to save your file to the OneStream folder structure. You can save files to any location matching your security access. If the file was opened from a OneStream location, it will be saved back to that location. If it wasn't, File Explorer will open.

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.  

General

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 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.

In-Sheet Actions

Create buttons to execute a Data Management Sequence, Submit data, or Refresh data, without leaving the sheet. See In-Sheet Actions.

Parameters

Insert a new parameter to quickly filter Cube Views and Table Views or manage existing parameters in the workbook. See Parameters.

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.

Preferences

Set Preferences for how to interact with and display your OneStream data. Settings include: 

General

  • Enable Macros for OneStream 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: 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.

  • Use Multithreading for Retain Formulas: When set to True, processing steps for collecting and applying formulas to Cube Views with retain formulas enabled will run concurrently for multiple worksheets, allowing for faster refresh rates. This is for Excel Add-In only, not the Spreadsheet feature in OneStream Windows App.

  • 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: 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.

  • Preserve Hidden Rows and Columns: This setting becomes active when the option Retain All Formatting when Saving Offline is set to True. When set to True, the rows and columns remain hidden when the workbook is saved offline. Rows and columns are visible when set to False.

  • 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.

  • Force Add-In Compatibility for Registration: When set to True, this setting forces a re-registration of the OneStream Add-In. This should be used when the Add-In is turned off on start-up by external add-ins.

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. 

Default Display Settings for New Quick Views and Default Suppression Settings for New Quick Views.

See Quick Views.

Display Context Pane

To display the OneStream task pane on the right-hand side of the screen, check this box. To hide the task pane, disable the box.

Default Settings for Preserve Formatting

  • Preserve Excel Formatting by Default (Quick Views): When set to True, formatting will be preserved by default for Quick Views. The default setting is False.

  • Preserve Excel Formatting by Default (Cube Views): When set to True, formatting will be preserved by default for Cube Views. The default setting is False.

  • Preserve Excel Formatting by Default (Table Views): When set to True, formatting will be preserved by default for Table Views. The default setting is False.

Tasks

Task Activity

View all running, scheduled, and completed tasks.

Automatic

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.