Charts From Excel, A Quick Guide

Note: the process described below was tested on a OneStream 7.3.1 environment. The steps may differ depending on the OneStream version.

As a CPM platform, OneStream offers many different possibilities to build more specific charts. The combination of multiple data loading methods and the BI Blend tools allows users to create solutions with a lot of flexibility.

One of those possibilities is pulling data tables from Excel files. This is one of the quickest ways to get manipulable data into the environment when testing reports.

Import the Excel File into the OneStream File System

As a starting point, import the Excel file into the OneStream File System:

  • Click the File Explorer icon at the top-left corner of the window.
  • Choose a destination folder to import your Excel file, for example Public.
  • Click the Upload File button to import your file.
OneStream File Explorer

Then go to the Dashboards menu by clicking Dashboard in the application left panel.

OneStream Application Presentation Dashboards

Create the Data Adapter

Create a new Data Adapter in a new or existing Maintenance Unit. To create a table from an Excel file, the options should be set as follows:

  • Command Type = Method
  • Method Type = ExcelFile
  • Method Query = use the ExcelFile syntax
  • Results Table Name = optional

The Method Query syntax is:

{FileSourceType}{UrlOrFullFileName}{Sheet}{NamedRange}{UseFirstRowForColumnNames}{IncludeNumericColumns}{Filter}
OneStream Excel Data Tables

Each field should be filled as follows:

  • FileSourceType = Application
  • UrlOrFullFileName = Documents/Public/FileName.xfDoc.xlsx
  • Sheet = SheetName or Empty
  • NamedRange = RangeName or Empty
  • UseFirstRowForColumnNames = True or False
  • IncludeNumericColumns = True
  • Filter = Empty

It is important that the file extension is xfDoc.xlsx, that the path matches the folder where the file is saved, and that column names do not contain blank spaces or special characters if the first row is used as column names.

OneStream Spreadsheet Tool

Test the Data Adapter

If needed, the Excel file can be opened, edited, and saved using the Spreadsheet tool. Here is a sample query:

{Application}{Documents/Public/DataForOneStream.xfDoc.xlsx}{ByProduct}{}{True}{True}{}

In other words, the sheet ByProduct is being pulled from the public folder, the first row is used as column names, numeric columns are included, and no ranges or filters are being used.

To test the data adapter, click Test Data Adapter.

OneStream Data Adapter
Source Excel file sample
Resulting data table preview

Build the Chart in BI Viewer

The columns with the _N suffix are clones from the source column with numeric format. These columns can then be used in BI Blend to create customized reports.

Assign the data adapter to a new or existing BI Viewer component:

  • Click on the BI Viewer component.
  • In the Data Adapters tab, click the plus button.
  • Select the data adapter.
  • Confirm your selection.
  • Save your changes.

Finally, click on the BI Designer tab to start building your chart.

MindStream Analytics

Want to learn more about OneStream Software? Please complete the form below and we’ll get back to you shortly.

OneStream BI Viewer