For Smart View Users - Quick Tips using simple VB macros

In my last post I showed how to use VBA to make deploying and configuring Smart View easy across a large user group, now I'm going to show you how to use VBA to make using Smart View easier. Let’s face it, most of the time spent in Smart View, specifically relating to Essbase and Planning, is going to be spent doing ad-hoc reporting /analysis.  If you have spent any time doing this, you know how powerful of a tool this can be, but you also know that some things are just not ‘in the right place’. 

There are a couple of things in the Smart View options, that as an ad-hoc user, you will find yourself changing several times a day, often times during the same reporting session. For me 3 of these stand out, “Row Suppression”, “Member Retention” and “Indentation”. First, for those who are new to ad-hoc analysis using Smart View, let me break down what these functions are, then I’ll show you how to make your life easier.

Row Suppression – this is probably the option changed more often than any other in Smart View.  Row suppression allows you to suppress / remove from your report rows that are empty (No Data / Missing), zero, or that you do not have access to.  Most of the time users are flipping all 3 of these options on and off as they drill into different parts of their report. Often times you need to start the report with this enabled until you get deep enough into the data to actually have valid data points, once there, you want to remove all other data, enabling suppression is your friend here.

Member Retention – After suppression, member retention hits my list.  Member retention allows you to either include or remove the item you are drilling from in the results. For example with retention enabled if you zoom into Q1, you will have Q1 and 3 months in your results. With it disabled you would only have the 3 months in your results.

Indentation – This is an option I use less often, but it really helps to make a roll up readable, especially if there are multiple levels.  The two columns below show you the difference.

YearTotal                         YearTotal
Q1                                      Q1
Jan                                         Jan
Feb                                         Feb
Mar                                         Mar

So now that you know what the 3 options we are going to make easier are, let’s see how to make life simple. The first thing you need to do is install Smart View... well I guess first you need to install Windows and Office, but I'm going to assume these things are already done if you got this far. So let’s start over.

Phase 1 – Prepare Excel

Step 1 – First open up Excel to a new blank workbook (make sure no other workbooks are open), we are going to create a couple of macros, and we want to store these in our personal macro workbook (PERSONAL.XLSB). This is a hidden workbook that Excel uses for macros that you want available to use from any other workbook, so you don’t have to make the macros every time you open a new workbook. If you already have this workbook, skip to Phase 2, if not we need to create it.

To create the workbook you first need to enable the Developer tab on the Excel ribbon, to do this go to File->Options->Customize Ribbon, under Customize the Ribbon in the box that says Main tabs, make sure that there is a check in the box for Developer, then click on OK.

 

Step 2 – Next we need to create a macro in the workbook so that we can save and edit it in the VBA editor, to do this go to the newly visible developer tab and click on “record macro”, you can name it anything, the default “Macro1” is ok, we are going to delete it after. The important part here is under “Store macro in:” you need to select Personal Macro Workbook.

Click on OK then do anything in excel, I normally just select a range of cells. Once that’s done, click on “Stop Recording”,

Step 3 - Now close Excel, you should get a prompt asking if you want to save changes made to your personal macro workbook, click yes. Once Excel is closed, open it back up.

Phase 2 – Create Macro

Step 4 – Now that we have a personal macro workbook to store our macros, let’s write them.  You can open the VBA editor by going to Developer Tab and clicking on “Visual Basic” or just press Alt+F11.

 In the Project Explorer you should see PERSONAL.XLSB visible now. if you don’t see projects on the left, go to View and click on Project Explorer. Next expand that and expand Modules.  If you followed step 1 you should have Module1, double click this and you should see in the right pane now Macro1.  You can delete this, as it’s no longer needed.  

Step 5 – We are going to use 2 SmartView functions for our macros, before you can use the VBA functions; you need to import them into your workbook. To do this just import the Smart View.bas file into the project in the VBA editor, by default you can find this file in the C:\Oracle\Smart View\bin directory. So to do this right click the Modules folder, and click on Import File, select the smartview.bas file and click Open.

Step 6 – now that we have the Smart View functions, let’s create some macros with them.  The first macro we are going to create will disable suppression, we can do this with the HypSetSheetOption function, for our case we are going to be changing suppression for Empty and Zero cells, these are parameters 6 and 7 so our macro looks like this:

Sub SupressionOff() x = HypSetSheetOption(Null,6, False) x = HypSetSheetOption(Null, 7, False) End Sub

 

If we have a macro to turn suppression off, we probably want one to turn it on as well:

Sub SupressionOn() x = HypSetSheetOption(Null, 6, True) x = HypSetSheetOption(Null, 7, True) End Sub

Next we add macros to turn member suppression off and on, this is parameter 2, so those macros look like this:

Sub IncludeMemberOff() x = HypSetSheetOption(Null, 2, False) End Sub Sub IncludeMemberOn() x = HypSetSheetOption(Null, 2, True) End Sub

We also want to turn indentation off and on, but in this case, I like to use a single button and toggle the option instead of having separate macros, we can do this with a simple If statement by using HypGetSheetOption first to find the current value, then running the appropriate change. Since indentation has more than just enabling and disabling you might want to include all 3 options. A value of 0 will disable indentation, 1 will indent sub items and 2 will indent totals.  Macro ends up looking like this:

Sub TogleIndentation() x = HypGetSheetOption(Null, 5) If x = 0 Then x = HypSetSheetOption(Null, 5, 1) ElseIf x = 1 Then x = HypSetSheetOption(Null, 5, 2) Else x = HypSetSheetOption(Null, 5, 0) End If End Sub

Now we have set all the Smart View options, there is one other thing I like to make easier, Number Formatting. Sure you could always use the default Excel formats to ‘one-click’ format, but that requires you be on the Home Tab of the ribbon, and offers little in the way of customization.  Since we are already writing macros, why not add 2 more:

Sub FormatCurrency() Selection.NumberFormat = "#,##0_);[Red](#,##0)" End Sub Sub FormatPercent() Selection.NumberFormat = "0.0%" End Sub

You can edit the format strings based on whatever formats you like, but these are what I use most often.

When done, it should look like this:

We now have created all of our macros, lets save the personal workbook and close out VBA editor. You can make sure that the personal workbook is saved by closing excel, if it’s not saved Excel will prompt you to do it on close. I recommend that you do this.

Phase 3 – Make Macros Easily Accessible

Step 7 – We have all the macros we need, they are in our personal workbook, let’s make them easy to use.  There are a lot of ways to do this, but I'm going to take you through my personal favorite. I like to add my new macros to the Quick Access Bar.  To add new commands to the bar, click Customize Quick Access Toolbar -> More Commands

Step 8 – Under “Choose Commands From:” select “Macros” from the drop down, you should see your macros now, move them over into the quick access, I would add a separator first, then put the commands in a logical order, I like to do it like this:

This groups my standard commands, Smart View commands and formatting commands. But the icons that are assigned don’t help very much, so let’s give each macro a unique icon. 

Step 9 – To assign an icon click on the macro you want to assign it to, then click modify, select the icon you like, and click OK. Here are the icons that I use, I’ll explain why I choose each one.

• Suppression Off – With row suppression off, we get ‘lots’ of rows of data, so I use the icon that tells me ‘lots of data’
• Suppression On – with row suppression on, we get a lot less data, so I use the icon that tells me ‘little data’
• Member Retention Off – I use the red x, it tell me something will go away (member you clicked)
• Member Retention On – I use the blue I since it tells me something will be included
• Toggle indentation, I just use the only icon that shows indentation
• Format Currency – this one is self-explanatory
• Format Percent – this is the closest thing to a percent sign, so I picked it, it’s worked well for me.

Once you have all of these set, just click on OK and they should be visible in your quick access bar.

You can now access all of these commands, even when you are in the Essbase, Planning, or Smart View ribbons. All that’s left to do is to open up an Ad-Hoc sheet and play with your new commands, make sure everything works as you expected. If this doesn’t improve your Smart View experience I would be shocked, they have made my life so much easier, and I hope that they do the same for you.

This one-time set up is likely to take less than 15 minutes, but the savings of time every day during Smart View Analysis makes it well worth it. It’s also worth mentioning that these macros will continue to work after a Smart View upgrade, without any changes as long as oracle does not depreciate the commands.

In this post I have gone through setting up just a couple of macros, there are literally limitless possibilities to what you could set up using a variation of this, consider this as a guide with an example, and expand this to what options you find yourself most often accessing, don’t feel tied to the options I presented here.  

If you have any questions or issues pleases feel free to comment below.