Pages

Sunday, March 9, 2014

Automation of SmartView reports

The most commonly used reporting tool with Hyperion setup is the Financial Reporting aka HFR. HFR brings with it, the ability to design reports using various datasources - Essbase, Planning, SAPBW, MSOLAP, HFM. It has been quite popular to burst static PDF reports using inbuilt scheduler or using the command line utility for Essbase, Planning and HFM. Along with HFR, SmartView is another tool used for reporting.

SmartView provide options to download HFR reports in spreadsheets and carryout analysis as most analyst love to do. One common requirement which HFR do not offer as an out-of-box solution, is the ability to burst reports in spreadsheets. This post delves into an alternate approach to cater such requirements.

The Smart View has been enhanced lately to include lot of new capabilities - Retaining Excel based formatting, butterfly reports, multi-grid reports, etc. - which combined with its macros functions help analysts design, automate and burst spreadsheet based reports in an easy and convenient way. Rather than focusing on new features, we would focus on its automation capability and take a small example to demonstrate the same.

Let's consider a SmartView template for the sample application Demo-Basic. The template pulls data for all accounts by periods for all products & all markets. The formatting includes few conditions and relative icons to give template some cosmetic look and feel. We can make sure conditional formatting is retained by enabling excel formatting from SmartView options. To automate refreshing of such templates, SmartView uses functions which can be embedded within excel macros. 



Navigate to Developer tab in Excel and open Visual Basic to open VBA editor.


The left pane of editor shows the project which consists of sheets and modules. Sheets refer to the tabs in workbook and modules store code. The modules are files with extension .bas, usually function library or custom code.



Before using the SmartView functions we need to import the library of SmartView functions under modules. Right click on Modules -> Import and navigate to smartview.bas. This file is located under SmartView\Bin folder, usually C:\Oracle\Middleware\SmartView\Bin. This gets imported as SmartViewVBA
We need to add a new module where we will use these SmartView function as excel macros. Right click on Modules -> Insert -> Module and name it anything. We named it Module5.


The code to retrieve sheet is shown below
  • The code first checks existence of connection with name "MyConn". We can name it anything, more appropriately application-dbname
  • In case the connection does not exist, it creates a connection
  • Login to Essbase using the parameters passed
  • Retrieve sheet and logout. We can all multiple sheets to retrieve


In order to schedule it, we create a VBScript file which makes call to the subroutine "RetrieveSheets" and refresh sheet(s). 



We can also add some VBScript code to email the spreadsheet to users (code not included here). You can download these files here.

Attachments:

Run_SmartView.vbs.txt: Remove the .txt extension (I added it to avoid being blocked by server).
This is a vbscript file which makes call to the Excel sheet and executes the Macro embedded in it. You are required to update the path of Excel spreadsheet, username, password, appname & dbname in this file. This username, password, appname and dbname will be passed on to the Excel macro when it is called. Double clicking this file or calling it from cmd will run it. Right click -> Edit to edit file

SmartView_Retrieve.xlsm: Macro enabled spreadsheet. Please make sure you have macros enabled to run and view code.

Hope this helps !!!

8 comments:

  1. The double click vbscript doesn't work. SV addin loads only if the sheet is open. I have been trying to execute it using vbscript and the addin wont load whatever I try,

    ReplyDelete
    Replies
    1. Hey Everyone,

      No one has replied in a long time. I hope that you've had the opportunity to fix the issue. However if you haven't I've found something that has worked. Creating a .bat file that opens the wb so that SmartView loads correctly. cmd line Call function opens excel as if it was double clicking it vs how VBS runs it in a way Excel understands automation to be. How this plays out in Task Scheduler is I have .bat open the file, and I wait 2 minutes then schedule the vbs file to run all the macros. I hope this helps.

      Delete
    2. Jim Chan,

      I'm interested to find out how you got your excel vba code to run via Task Scheduler. What do you mean by the following "how this plays out in Task Scheduler is I have .bat open the file, and I wait 2 minutes then schedule the vbs file to run all the macros" How are you scheduling the VBS file? do you have sample code that i can look at? I've been trying for a while and I haven't been able to automate the running of my Smart View VBA code from a task scheduler or other scheduler programs (e.g. JAMS, etc.). I know how to execute it via command buttons, etc. when the user opens excel, but no fortunes trying to automate the macro from Task Scheduler. Please help!!

      Delete
    3. In the attachment you will have a vbs script which calls the macro in the smart view excel. This vbs scripts can be in turn called from a .bat file using the command:
      cscript VBS_Script_Name.vbs
      This .bat file can be scheduled with any scheduler program. Hope this helps !!!

      Delete
  2. i don't see an attachment. Please advise...

    ReplyDelete
    Replies
    1. There is a link available just above the "Attachments" for the excel file as well as vbs file.

      Delete
  3. Hi Team,
    I am able to connect and refresh using VBA, but after refresh there is a message that is displayed like SUMMARY OF REFRESH OPERATION: Table 1 Refresh Completed successfully.

    How can i disable the same so that i can schedule multiple refreshes using VBA.


    Thanks

    ReplyDelete