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 the 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:

 

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:

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

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

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

The client needed 3 variables to fill in for the form: Category, SubCategory, CouncilRound.  These were setup in a list in setting the 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:

 

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

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:

 

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

Add a Table View Business rule:

 

Select the Business rule that was created:

 

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

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: