Last month I talked about OneStream Books. I had an idea on what I wanted to discuss this month and right on cue a coworker messaged me saying I love what I read on Books, but what if my client wants to be able to drill down? Cue my next blog.
Books are great for exporting reports, but this is a point in time export, and you cannot drill down or refresh without rerunning. While this type of report has many functional uses, what if your client wants something more dynamic that they can refresh, drill from, or even add some additional schedules that may not exist in OneStream. This is a great opportunity to utilize the Excel Add-In and Cube Views together.
Excel Add-In
I could talk for days about how to use Quick View but what I want to focus on today is accessing Cube Views within the Excel Add-In tool. Doing this we can create a multi tabbed reporting package that is actively linked to OneStream. This means that users can refresh, select parameters, and drill down to source data direct from Excel.
Using Cube Views also ensures that all users are viewing the same detail as a refresh will dynamically pull in any new rows, columns, or updates that were made since the last use.
Cube Views In Excel
To add a Cube View within Excel you need to be logged in to your Application. On the OneStream XF tab click on the drop-down menu next to Cube Views and select Cube View Connections.
This will open a window with the option to navigate out to a Cube View. Click the ellipsis and select your Cube View. There is also an option here to ‘Include Cube View Header’. I recommend checking this box if you want to pull in the header details.
Note that if you do not see the option to include the header it was not available in versions prior to 7.1. This was an addition I was very excited to see because there are very few times when you would not want to include this.
Once you select your Cube View any parameters that are utilized in the Cube View will pop up for you to select.
Make selections and your report will open. I renamed the tab to be meaningful to me.
A Named What?
Ok – so if you’re an avid OneStream user you are probably thinking big deal, I knew how to do this already. This is where you can take things a step further and you can nest your parameters within your workbook by creating a defined name.
For those not familiar with naming in Excel, this is when you define a name for a cell or range of cells. When naming one cell this is called a defined name, when you name a range of cells this is called a named range.
We will create a defined name for our parameter cells and use a Cube View’s named range to set the values available for selection.
Gather Your Parameters
To do this you need to know the names of your parameters. If you are not sure log into OneStream and check the Cube View for references. I have a reference to Entity, Year, and Month in my Point of View as well as a Scale parameter in my Cube View Cell Format.
I created a new tab in my Excel workbook I am calling ‘Parameters’. You will need to select the cell you want your parameter reference in and put the corresponding parameter in the defined name box within Excel.
I am going to add one for each of my four parameters. You will use the parameter name without any pipes or exclamation points. I did this in column B so I could add titles in column A.
Now that this is set up, I can type in column B to input a parameter result.
When I refresh my Cube View, I no longer get prompted. My Cube View is now looking at the named cells rather than a pop-up box to populate my parameters.
What if I Don’t Know All My Dimensions?
We could stop here but this requires the end user knowing all their entity dimensions. We can take this one step further and use a second Cube View to dynamically populate our entity list. Why use a Cube View rather then just creating a list? If a new entity is added to the hierarchy the Cube View will populate with the new entity when refreshed meaning your list will always be up to date – no additional maintenance needed!
You will just need to create a simple Cube View with the entities you want in your row. I have no columns in this Cube View.
You will need to ensure that the dimension you are creating a list of is set to a Header Text of Name.
You will also want to make sure your suppression settings are all turned off, so your Cube View renders despite having no data.
Using the same steps above we used to connect to our first Cube View, connect to your Entity Cube View on a new tab. This is what my results looked like.
Connecting All The Pieces
Now navigate back to your parameter tab and select the named cell with your entity parameter in it. On the data tab click the Data Validation drop down then Data Validation.
Change the Validation Criteria to allow a list and the Source will be set equivalent to your Cube View name. In this case my Cube View name was simply ‘Entity’. When you connect to a Cube View a named range gets created in the background automatically. We are telling Excel we want the named range to populate our list. Doing it this way instead of selecting cells ensures that the full list will be included even if the number or rows changes.
Doing this will populate a combo box with all the entities in the list you created.
If I add new entities next month and refresh my workbook my list will update to include anything new. We used entity as an example here, but this can be done with any dimensions you would like to pull in. Another benefit of doing this is if you have multiple reports with the same prompts, you only need to set once on your parameter tab and can refresh the entire Excel workbook.
Using this you can create an Excel workbook that is similar to a OneStream book containing multiple reports.
Right Click
By right clicking on any number within my report I can use the OneStream Drill, Cell POV, and other system functionality.
By integrating both OneStream and Excel features, we can develop a user-friendly reporting package that eliminates the need for tedious monthly maintenance tasks.
