My Perficient team recently completed a project with a client that had a typical challenge that most clients have when moving from a manual, user maintained Excel-based reporting platform, to a more system-based reporting platform. Yes of course, we can generate all reports out of OneStream and download to Excel, but building a report that would take a selected parent entity member and then burst out reports by descendant entities of the parents, as individual tabs in Excel, was a new challenge. When all hope seemed lost, once again the beauty of the OneStream platform came through with the power of Cube Views and Books.
The Requirement
In previous budget cycles, using their old budgeting and planning system, the client developed a set of “report packages,” which were a series of Excel workbooks containing several worksheets of various financial data. The tabs were arranged in a manner of descending order down a reporting hierarchy based upon their entity/department structure for Expense Planning. The first tab was a predefined level and consisted of summary data of the children below. These worksheets were fed by a series of links to an Excel source document, which was a pivot table, and refreshed periodically when data was changed. This process was very manually and was typically handled by central personnel and lacked the ability for “dynamic” refresh by the reporting entities when required.
For the OneStream project, the key requirements where:
- Provide the ability to generate the summary/detail reports dynamically based upon the Entity selected.
- Allow the users to generate the reports without the need for a central administrator to execute the process.
- Prevent the need to have to manually update the reporting packages due to any changes in the entity/department rollup due to changes in the reporting hierarchy.
- Overall, simplify the process!
The Solution
The initial thought was to create the same report packages defined in the client’s original implementation by creating Excel Add-In Quick Views connected to OneStream XF and get rid of the links to original source documents pointing to the finance team’s data warehouse. This would work, but we still couldn’t make this very dynamic and if an entity/department rollup changed, the admins would still have to manually update the report packages/Quick Views. This would meet most of the requirements, but not all of them.
Then, after some serious research and experimentation (goes without saying with a new platform), we found we could leverage the same set of Cube Views, but through the use of Books in OneStream, we could dynamically generate an exported Excel workbook with Summary/Detail information required without creating individual reporting packages by reporting entity. The solution involved the following steps:
- Create a series of Cube Views for the Summary & Detail reports
- Create a Book to take those Cube Views, exported as Excel Export Items by Summary then Detail
- Utilize the Loop and Change Variable features in the Books to dynamically generate the Summary and Detail based on the entity selected
The key feature which made this dynamically generated reporting package possible was the Loop and Change Variable function in the Books. The Cube Views would rely on a parameter for the Entity and as the Book process went along, the Entity would change based on the level being processed down the Entity hierarchy in OneStream.
The Report Package in OneStream
Top Level of the Report Package – When the Book is executed, the users will select the Parent Entity for the Summary page.
After the Summary Page is generated, the Book will then begin the first Loop of all the children of the Entity selected in the first step. The Loop definition will be defined for the entity parameter and the children of that member selected. The Loop Variables are then used to help process all the Excel Items to be created in the generation process.
The Change Parameters feature takes the Loop Variable defined in the step above and sets this as the parameter for the Excel Export Items to be generated in the next step of the Loop
These Loop Variables not only help facilitate the generation of the required reports by entity, but can also be used to define how the output or tab name generated in the Excel spreadsheet which further increases the usability of the report package for end users.
The OneStream XF Design and Reference Guide provides good insight and instructions on how the Books and Looping features behave. These features provided a powerful tool for our client to not only get rid of a manually and very laborious process (very large Excel workbooks as well) and move to a more systematic, reliable, and dynamic method for their reporting and management process. This was only possible through the power of the OneStream XF platform which once again proves its versatility and position in the CPM market place.
