Wednesday, 13 January 2021

Smart View Drill-Through to Excel in EPM Cloud

Hey guys – Mike back again to highlight a great new feature Oracle have brought out to EPM customers who make use of the Smart View Excel extension to review their figures directly in Excel.

This new feature allows you to drill-through directly into Excel, where previously we had to drill into a web browser interface. You could already export to Excel from the browser, but only certain browsers worked, the interface was slow and clunky, and it took a lot of clicks.

Let’s see it in action - head to the Smart View tab and select Options.

Then select the Advanced tab and select your preferred option in the Drill-Through Launch drop-down menu. My preference is Prompt me to Choose Target so I can pick whether I want to drill to the traditional web browser or to a new sheet at runtime.

When I drill-through in Smart View in the usual way, selecting a cell with a green box and pressing drill-through we get this popup:

If you select Open as new sheet then the drill will open directly into a new sheet, as below:

You can also select In New Sheet in your Smart View options to skip the prompt step as well, making this a single click from a high-level number in Smart View directly to all level zero rows, with all additional detail columns that were lost during the data management step. This works extremely quickly and effectively and makes a process that took a lot of clicks to get into Excel take seconds to export large volumes of data.

You can even drill-through to source from each record if you've already got this configured for the web version - simply select the cell, scroll to Smart View and select Drill-Through to Source. Our customer uses this to drill back to their on-premise Oracle GL system.

It's as simple as that! 

There’s a million use cases for this, our customer plans at a role level, but sometimes needs to get into the employee actual details to explain variances at month end. We’ve got the role as our lowest dimension member, but now with one click a user can export all 20000 rows of employee level actual payroll costs in around five seconds.

The benefit of EPM Cloud is that features like this come out every month, and don’t require any setup. Anyone using drill-through currently in EPM Cloud can take advantage of this today with no further setup than what I’ve shown in this blog.

Isn’t the cloud amazing?

There is one minor issue with this – it will export all columns that exist in your data management backend, even if they are irrelevant to the drill you’re doing. So you may have to delete a bunch of empty columns. I’ve created an idea on Oracle Cloud Customer Connect which you can view here to support and help it become a reality!

Until next time,