Background
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:
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:
- Setting variables
Case Is = SpreadsheetFunctionType.GetCustomSubstVarsInUse - Retrieving Table View data
Case Is = SpreadsheetFunctionType.GetTableView - 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
Variables
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.
Data Retrieval
In order to pivot data to have years in columns, we used a bit of SQL on the Thing Planning table:
(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.
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:
Using the Table View
Once the Spreadsheet business rule is setup, we could easily add the table to a Spreadsheet:
Named Ranges were set up to have 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 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:
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.
