Skip to main content


Pivot and Submit Data through a Spreadsheet using Table Viewer in OneStream

Istock 1223455328 (1)

Thing Planning Solution

Perficient was recently engaged with a client that was implementing a Thing Planning solution to manage thousands of line items for their planning process.  The client wanted to be able to look at a small set of data in a form and submit the changes to just those rows without having to go through the entire workflow process.  Their items also had 7 years of data with a row for each year and needed to pivot on the years so that they were in columns.

Here’s a sample of some dummy data in Thing Planning:

Sample Thing Planning Data

Spreadsheet Business Rule

Table Viewer in OneStream allows you to bring this data into a Spreadsheet and change the data.  There are 3 main functions of the Spreadsheet business rule:

  1. Set your variables  (Case Is = SpreadsheetFunctionType.GetCustomSubstVarsInUse)
  2. Get the Table View (Case Is = SpreadsheetFunctionType.GetTableView)
  3. Save/ update the Table View (Case Is = SpreadsheetFunctionType.SaveTableView)

The project team setup these Private functions under each main function:

  1.  “Filters” for Set your variables.
  2. “GetProjectPlanningDetails” for getting the table view
  3. “UpdateProjectPlanning” for saving the table view

Spreadsheet Functions Snip

The client needed 3 variables to fill in for the form: Category, SubCategory, CouncilRound.  These were setup in a list in setting the variables:

Setting Variables

The project team pivoted the data to put the years in rows to columns in the Get Table View function using SQL on the Thing Planning Table:

Gettableview Function

The save table function is where column names are set to a TableViewColumn:

Save Tableview Column Names

The .IsDirty function is used to check to see if there’s a change to the value in a column and update the table if there is a change:

Onestream - Modern Accounting: How to Overcome Financial Close Challenges
Modern Accounting: How to Overcome Financial Close Challenges

Improvements in each of the following period-close core tasks can provide transformative change and are reviewed in this guide include closing the books and external reporting, periodic reconciliations, and managing the period-close process.

Get the Guide

Savetableview Update Table


Once the spreadsheet business rule is setup, add the table to a Spreadsheet in OneStream:

Spreadsheet Tableview

Add a Table View Business rule:

Spreadsheet Add Table View Bus Rule


Select the Business rule that was created:

Save Tableview Business Rule Select


Named ranges were also used with the same name as our variables.  For example, cell A3 is named “CouncilRound”

Namedranges For Variables

The values we wanted for those variables were entered in those named ranges.  If named ranges were not used for the variables then there would be a pop-up box for the user to enter the values for the variables.

Once Refresh sheet is clicked the data will appear from the Thing Planning table:



The table updates after making a change.  In the example below the 1st value was changed by $2 and submit was clicked:

Submitted Table


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Phillip Oswald

CPM consultant with 20 years of experience focused on making continuous improvements in the efficiency and accuracy of organizational accounting systems. Recently, I've had 2 different projects for Budgeting and Forecasting with one of them using Thing Planning.

More from this Author

Follow Us