The Excel Quick View tool within OneStream gives users, particularly those in finance, the ability to use Excel as a front-end interface while OneStream does the heavy lifting on the back-end. Quick View allows for drag, drop, and pivot ad hoc reporting. In addition, the drill downs in Quick View create the ability to achieve faster analytics with increased detail.
In my experience, some of the most requested end user training sessions are around Quick Views. Quick Views are heavily utilized in all phases of an implementation. Whether they’re used for narrowing down variances during data recon, or by an end user after go-live for an ad hoc analysis, they always come in handy. I’ve highlighted and counted down the top 5 tips and tricks for users with any level of experience. It’s my hope that you will find them useful and that they’ll help you be more efficient in your day to day OneStream activities within Excel.
Tip# 5 – Manage Preferences
You may have noticed that there are two sets of Preferences within the OneStream Excel add in. While it might not be initially obvious, these Preferences are different. The Preferences under the OneStream XF tab apply to all Quick Views. Consider these your “master settings” that will be applied to any Quick View you create. The Preferences that are on the Quick View Context Pane, only apply to the selected Quick View in your Excel spreadsheet.
An example of setting end users frequently change to apply to all Quick Views:
Data styles will determine the format of your data. These data styles are same ones that are used within Excel. Feel free to select one that fits your needs. Note: you may need to restart all instances of Excel for these changes to apply after they’ve been selected.
Also, if you do not want the Quick View Context Pane to display every time Excel is open, you can uncheck the box highlighted in blue in the screenshot above.
As mentioned above, the Preferences within this window only apply to the active Quick View.
Tip #4 – Copy a Quick View
Copying a Quick View is a useful feature if there is an existing Quick View that you would like to use as a starting place. It carries over all of the settings of the original Quick View as well as the original POV. The original and the copy are not linked, which enables the user to make any changes they wish to their copy without affecting the original.
- Highlight a cell in your Quick View
- Go to OneStream Ribbon, and click on the Quick Views drop down
- Select Create a Copy of Selected Quick View
- Update Refers To section to area where you want copied Quick View to generate and click OK
Tip 3# – Where Clause Member Filter
Where clause expansions can be useful because they help you further filter data based on settings or attributes on the dimension. In this example I will show you how to filter the UD4 member based on its Text 1 field.
- Highlight a cell in your Quick View
- Click on the filter icon on the Quick View Context Pane next to the UD4 dimension member:
- On the Member Filter Builder select the tab Samples and browse to the Where Clause Expressions section. Contained within this section are all of the available Where Clause Expressions for Quick Views. Some of the expressions are dimension specfic, others can be applied to any dimension.
- Double click on the Where Clause you wish to apply. Note: for this case we will be using Any Dimension (TextN)
- Your expression should look similar to the one in Step 5. You will need to delete the first half of the expression “X#MyMember.Descendants” since we have already defined our member and expansion.
- Click OK to apply this new filter. Your Quick View will now be filtered by the specified Text1 field.
Tip #2 – Build a Quick View via Typing
If you’ve used Smart View before, you’ll know how useful this tip can be. This was a highly requested enhancement that allows Quick View users to create a Quick View from scratch via typing dimensions in cells. Below I will show you how to create a Quick View this way, and also how to add on to that existing Quick View with new dimensionality.
To build initial Quick View:
- Type the dimensions you want to use in your Excel Spreadsheet using the appropriate OneStream Tokens, and highlight all cells. Note: you can use member expansions as demonstrated in cell B4.
- On the Quick View Context Pane select the Create Quick View button.
- Click okay to generate a Quick View
To add additional dimensionality to an existing Quick View:
- Type in new dimensionality using appropriate OneStream tokens
- Highlight all cells.
- Click Rebuild Quick View button to add dimensions
Tip #1 Easily Hardcode a POV
You may have noticed that you do not have to define every dimension within your Quick View Point of View in order for data to be returned. This is because a Quick View will reference the Cube Point of View, as known as Default Point of View, on the POV tab if a dimension has not already been defined.
So why hardcode your entire Point of View?
- If the Workbook will be shared with other users, they might not have the same Default POV as you
- Hardcoding ensures that all users are looking at the same data set
Hardcoding each dimension can be tedious, so this tip will help you accomplish this with a few clicks of the mouse.
- Apply POV from Selected Cell – Hardcodes POV of selected cell to the Quick View Point of View
- Apply User POV – Hard codes Default Point of View from the POV tab on the Quick View Context pane
- Clear POV – Clears hardcoded Point of View and resets it to “default”