New Contributor III


We were recently engaged with a client implementing a Thing Planning solution, which would 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 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 so that years would appear in columns.

Here's a sample of Thing Planning test data:

Image 1 - Dummy data Thing Planning.PNG

Structure of the Spreadsheet Business Rule

Table Views in OneStream allow you to bring this data into a Spreadsheet and change the data. The Spreadsheet business rule will have 3 sections:

  1. Setting variables
    Case Is = SpreadsheetFunctionType.GetCustomSubstVarsInUse
  2. Retrieving Table View data
    Case Is = SpreadsheetFunctionType.GetTableView
  3. Saving/updating Table View data
    Case Is = SpreadsheetFunctionType.SaveTableView

In order to keep our code clean, each function is typically implemented as a separate method in the class. In our case:

  •  "Filters" would set our variables.
  • "GetProjectPlanningDetails" would build the Table View
  • "UpdateProjectPlanning" would save the Table View

Image 2 Business Rule.PNG


The client needed 3 variables to fill in the form: Category, SubCategory, and CouncilRound.  These were declared by placing them in a List returned by the Filters method.

Image 3 - substring variables.PNG

Data Retrieval

In order to pivot data to have years in columns, we used a bit of SQL on the Thing Planning table:

Image 4 - pivot table.PNG

(Note: this particular SQL query could do with improvements. Can you guess which?)

Data Update

In order to save our data, we had to create TableViewColumn objects.

Image 5 table view column.PNG

The .IsDirty method was used to check to see if there's a change to the value in a column, in which case we will update it in the database:

Image 6 - IsDirty.PNG

Using the Table View

Once the Spreadsheet business rule is setup, we could easily add the table to a Spreadsheet:

Image 7 - Table View.PNG

Image 8 Table View business rule.PNG

Image 9 - Select Business rule.PNG

Named Ranges were set up to have the same name as our variables. For example, cell A3 is named "CouncilRound"

Image 10 - Council Round.PNG

The values we wanted for those variables were entered in those Named Ranges. If Named Ranges had not been used for our variables, a pop-up box would have appeared for users to enter values.

Once Refresh Sheet was clicked, the data was retrieved from the Thing Planning table. If values were changed, the new ones would get saved automatically. In the example below, the 1st value was changed by $2 and Submit Sheet was clicked:

Image 12 - sample submit.PNG

About the Author

Phillip is a CPM consultant with 20 years of experience focused on making continuous improvements in the efficiency and accuracy of organizational accounting systems. His recent engagement include several Budgeting and Forecasting projects, some involving Thing Planning.

:link: Philip on LinkedIn 
:briefcase: Perficient Inc.