Spreadsheet or Excel Add-in?
Spreadsheet or Excel Add-in? As we know, the Spreadsheet and Excel Add-in capabilities extend OneStream's functionality, providing financial analysts and FP&A professionals with a familiar interface to access, manipulate, and submit company data. The use of Spreadsheet and the Excel Add-in is manly focused around two areas: Data Retrieval and Analysis: They allow users to connect directly to the OneStream database to retrieve, analyze, and refresh live financial data. Users can leverage Excel's comprehensive formula and charting capabilities while ensuring that the underlying data is accurate and sourced directly from the single version of the truth within OneStream. This capability is vital for ad-hoc analysis, detailed drill-downs, and creating custom management reports that may be too complex or specialized for the standard built-in reports. Data Submission and Workflow: In some contexts, they are used to facilitate the submission of budget, forecast, or journal data back into OneStream. Templates can be designed in the tool, which then uses the specific functions to upload data, maintaining the integrity of the data submission process by adhering to security and validation rules. Now, what are the differences between these two options? Well, the differences between the Excel Add-in and the Spreadsheet feature lie primarily in their environment, functionality scope, and dependence on Microsoft Excel. Here is a breakdown of the key features and distinctions: So, when you should use one option over the other? Well, you should use the Spreadsheet feature over the Excel Add-in when you need an easy access to the tool and, most important, governance and control: And when you should use the Excel Add-in over the Spreadsheet feature? As we know, the Spreadsheet feature is excellent but cannot replace the power of the dedicated Add-in for advanced tasks. You must use the Excel Add-in when you need: VBA Macros/Automation: Any time you need to use Visual Basic for Applications (VBA) to automate data retrieval, processing, or submission actions; External Data Integration: When you need to combine live OneStream data with data from external sources or other Excel files within the same workbook; Advanced Excel Functions: When you need to leverage highly technical Excel features like Goal Seek, Solver, or specific advanced charting types that are not fully supported by the internal Spreadsheet feature; Complex Report Formatting: For highly customized, production-quality financial reports that require the full fidelity of desktop Excel's formatting, printing, and charting capabilities. In summary: if a user needs the full, uncompromised power of Excel (VBA, Solver, etc.), they must use the Excel Add-in. If a user only needs basic Excel functionality, ad-hoc reporting, and data submission inside the OneStream platform for a seamless experience, the Spreadsheet feature is all they need to use.
Pivot and Submit Data through a Spreadsheet with Table Views
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. Philip on LinkedIn Perficient Inc.
