Smart View VBA Macros - Revisited

Several years ago I wrote a post on using VBA to enhance your Smart View experience, since then Excel has changed quite a bit, and some of the steps are slightly different, or at least may look different.  With that said, Today I will revisit this, and simply give you the steps required to implement some simple Excel macros to improve you Smart View experience.

If you want the details on some of the how and why, please visit my previous post here but the macros we are going to add do the following:

1. Include Members On - When you drill into a member, this will retain the member you drilled from
2. Include Members Off - opposite of above, when you drill in, the member you click on will be removed the the returned result.
3. Suppression On - This will turn suppression on for Rows with Missing and 0
4. Suppression Off - This will turn suppression off for Rows with Missing and 0
5. Toggle Indentation - This will switch between having member indentation enabled or disabled, one click to flip, and you do need to refresh the sheet after clicking to see the change.

On with the show!

Step 1 -Open excel

It is recommended that you restart excel before this step because we will need to close all sheets at some point ahead.

I was going to put a screen shot here, but I think you all know what Excel looks like.

Step 2 - Add Excel Developer Ribbon

This is not required if you have done this in the past, or are currently using Excel Macros.

• Open 'Excel Options'
• Select 'Customize Ribbon'
• Check 'Developer'

Step 3 - Record a Macro

At this point, we need to record a basic macro, this will create a 'PERSONAL.XLS' book for you to store macros in.

• Click Developer Ribbon and click on "Record Macro"

• Store macro in your Personal Macro Workboo

• do something in excel (select some cells or something)
• Click the Stop button (its in the lower left hand corner of your excel window

At this point, you have created a Personal Macro Workbook, which we will use to store our new macros.

Step 4 - Import SmartView.bas

The next step we need to do is bring in the smart view functions to Excel, back on the developer ribbon, click on Visual Basic (or press Alt+F11)

The default install location of SmartView is C:\Oracle\SmartView but if this was changed during install your folder may be different.  Once you find SmartView folder, got the \bin and select smartview.bas it should be the only file that shows up in that folder.

Step 5 - Add the Macro Code

This step is simple, open the Module 1 'file' under your personal excel workbook (PERSONAL.XLS) 

and paste in this code:

Sub SupressionOn() x = HypSetSheetOption(Null, 6, True) x = HypSetSheetOption(Null, 7, True) End Sub Sub SupressionOff() x = HypSetSheetOption(Null, 6, False) x = HypSetSheetOption(Null, 7, False) End Sub Sub IncludeMemberOff() x = HypSetSheetOption(Null, 2, False) End Sub Sub IncludeMemberOn() x = HypSetSheetOption(Null, 2, True) End Sub Sub TogleIndentation() x = HypGetSheetOption(Null, 5) If x = 0 Then x = HypSetSheetOption(Null, 5, 1) Else x = HypSetSheetOption(Null, 5, 0) End If End Sub

We are almost done.  

Step 6 - Make Macro Shortcuts

You have added the Macros at this point, and they should be functional, but you probably want to make them more accessable.

Go to your Excel Options again, and select Quick Access Toolbar

From the Drop Down at the top to chose commands, click on macros, and Add the 5 new macros. Once added, you can use the Modify button to give them Icons, you can also add Separators or rearrange the icons using the up and down arrows on the far right. While you can use any icon you want (or none?) I recommend once in my screen shot for the following reasons:

1. Include Members On - The blue i, because it is Included
2. Include Members Off - the red x, because it make sense next to the blue i
3. Suppression On - the 4 cell box, because with suppression on, you will retrieve less cells
4. Suppression Off - the 9 cell box, because with suppression off, you will retrieve more cells.
5. Toggle Indentation - the left justify text icon works well here for obvious reasons.

Step 7 - Save it

The last step in this process is to save all of this work, otherwise you get to do it all again. The easiest way to save it to close excel, you will be prompted to save changes to PERSONAL.XLS, click on yes.  after that, start excel back up and enjoy these simple shortcuts.

I hope this helps someone, and if you have any questions, please comment below, I will do my best to get back to you as soon as I can.