Creating a Document in Microsoft Excel

The example below is a summary balance sheet built in Excel. It shows how to:

  • Use a parameter

  • Use the XFGetCellVolatile retrieve function.

  • Use the Excel IF function.

  • Incorporate an Excel chart derived from the functions in an Excel spreadsheet.

In this example:

  1. A parameter named |!GetCellEntity!| was created in order to allow the user to select a specific entity on which to base the data for this spreadsheet. When the extensible document processes at run-time, it prompts to select an entity.

  2. The XFGetCellVolatile function was used to retrieve specific data from OneStream and update the Excel chart once the data is refreshed. Excel requires a volatile function for proper refreshing when using charts that reference calculated cells. This XFGetCellVolatile formula derives from the |!GetCellEntity!| prameter and display updated data when selecting an entity at run-time.

    NOTE: See Retrieve Functions in the Excel Add-In for more details.

  3. The IF Excel function was also used which derives from the XFGetCellVolatile function. This data updates when the spreadsheet launches from OneStream and the data refreshes. An example of the IF formula is as follows:

    Copy 
    =IF((F6=0),"", (D6-F6)/F6)

  4. An Excel chart inserted into this spreadsheet is driven by the data. The chart display the correct values whenever it is refreahed.

When the document processes, it prompts to select an entity and then runs the Excel spreadsheet. Log into the Excel Add-In and click Refresh Datato see updated values.