Automate Smart View Reports in Two Steps

Avatar

By
January 29th, 2014


Smart View reports often rely heavily on the appropriate option settings. Luckily, you can automate them in two steps using VBA.

First, import the smartview.bas file:

  1. In Excel, navigate to Developer tab (if not visible, enable the Developer tab in Excel Options ) and select Visual Basic
  2. In Visual Basic explorer, select File \ Import File
  3. Navigate to oracle \ SmartView \ Bin \ smartview.bas

Second, create a VBA module:

  1. From Visual Basic explorer menu select Insert \ Module
  2. You can write text similar to that below or simply enter the same instructions for two distinct options profiles (In this text, the first section turns on suppression and zooms to the bottom.  The second section turns off suppression and set zoom to next level.)

Automate Smart View example

 

In the function HypSetSheetOption:

✓ Option 2 – enables ‘include selection’ setting

✓ Option 1 sets zoom level to 0 next level, 1 all levels or 2 bottom level

✓ Option 6 enables suppress missing setting

✓ Option 7 enables suppress zeros setting

NOTE: If you want to remind the user of which settings are active, you can add a message box.

To attach a shortcut key, navigate to View tab, click Macros button and select View Macros in pull-down.  Click macro name and click Options button; then select shortcut key in “Macro Options” window.

See the Smart View User Guide under VBA functions for a listing of other Options settings.


Avatar

About TopDown Team

The TopDown Team includes members of TopDown Consulting who want to let the community know about webcasts, conferences, and other events. The team also conducts interviews on various EPM industry topics.

3 comments on “Automate Smart View Reports in Two Steps”

  1. Ravikiran says:

    Hi,

    Currently I dont have smartview.bas file but I have the smart view add-in. Is it possible to crate a vba connection without smartview.bas file?

    1. Al Ciborowski says:

      The .bas file should be included in the SmartView \ bin directory as part of your installation. If you have not do so already, I recommend installing Smart View again. It will save time from having to develop from scratch and make distribution easier since other users should have Smart View .bas files on their computers as well.

Leave a Reply

Your email address will not be published. Required fields are marked *