Have you ever struggled with copying worksheets from Smart View? In our series of Smart View tips, we share our experiences so you can get even more out of your Oracle applications. This time, we’ll take a look at – the quite unknown – feature of ‘cascading’ Smart View ad-hoc sheets. How does it work, and why is this feature so helpful?
If you use ad-hoc sheets, you may have missed the ability to copy your current sheet. Often, because you wished to retain your current sheet and wanted to continue with a new sheet based on your current one. Unfortunately, copying a worksheet with the Excel functionality does not work, simply because Smart View does not recognize the new sheet as a ‘Smart View sheet’. When you refreshed the copied worksheet, you’d have received the following warning:
This is very annoying. Probably, you have often made the choice to save your current Smart View file and then ‘save as’ to start with your current sheet in a new file. This way, you’d at least overcome the situation where you’d have to create a new Smart View sheet from scratch.
Luckily, there is a solution. It’s good to know that there is native function available within Smart View: cascading (copying) Smart View sheets.
Cascade offers you the functionality to not only copy your existing sheet, but also to create multiple sheets for each selected (POV) member. For example, if you want to have your current sheet ‘cascaded’ for a range of entities or products, an ad-hoc sheet will be created for each one of the selected combinations. You can ‘cascade’ the sheets within the same file, a new Excel file or into different Excel files.
Let’s create a cascading copy step by step. We’ll start with a simple ad-hoc Smart View sheet:
If we now choose ‘Cascade’, a popup appears where we can choose for which members we want to create ‘Cascade’ sheets. A handy feature is that the POV members in this pop-up are automatically populated by the members which are in the POV of your source sheet.
If we choose one member for each dimension, we’ll end up with one ‘cascaded’ sheet. In other words, we’ll just make a copy of our source sheet:
To cascade our source sheet using multiple entities, we’ll take the following step:
In the example above, you also see how many cascaded sheets will be created. In this case we’ll create two: one for ‘Europe’ and one for ‘Holding’.
The result will be:
Please note that it’s very important to limit the number of combinations. Don’t create too many combinations by selecting various members in the ‘Select Cascade Members’, as Excel resources are limited. This can result in too many potential cascade sheets, causing Excel to crash during the creation of so many sheets. The solution is to put that dimension in your row or column of the sheet, in order to limit number of cascaded sheets. Just to give some insight, creating over 30 cascaded sheets might be already too much for Excel to cope with.
If you’ve ever wanted to copy your current sheet in Smart View – and who hasn’t? –, you’ll be pleasantly surprised at how handy cascading is. Using this quite unknown feature enables you to easily create multiple copies of your current sheet. This will save you time and increases the reliability of your ad-hoc sheets.
Interested in more of our tips to get even more out of your Smart View? Please contact Bert per mail. Get more out of your Oracle applications with our support for Oracle.
Text: Bert Dotinga