Ad Hoc query from HsGetValue

I have recently been on the edge of an FCCS implementation and one of the areas that is overlooked when compared to HFM is the use of functions in Smart View. For an HFM implementation it is standard to use Smart View functions at some point. The output can be configured to be exactly what the user wants and this can then be automated using Excel macros or VBA. Some of these function sheets can be quite big but generally the response time with HFM as the source application is very good. When Essbase is the source (and FCCS uses Essbase as its storage) response times are excellent for ad hoc queries but are poor for the functions. This means that using Smart View functions to create large reports is not recommended as they take a long time to refresh. So the problem is that the users want to use functions in Smart View but the system architects are saying not to use them because they are too slow. In this article I will go through a possible solution to the problem: ad hoc drill downs from a Smart View function cell.

This article looks at some advanced VBA topics so I am assuming you are already reasonably competent in VBA for Excel. I will not be going into all the detail of how to do this as a full explanation of all the techniques would be too long. I will however point out where you can get more information or the relevant Excel function to use. You can download an example spreadsheet at the end so you don’t have to read the explanations.

Overview

Smart View functions are used to create static reports which can then be formatted as required. The problem is that each function is a separate call to the source. Some of the detail on the report could be removed to improve response time but the user needs a quick and easy way to access the data that has been removed. One solution is to create a macro that automatically generates an ad hoc query based on the parameters in the Smart View function. The idea is that the user double-clicks on a cell that contains an HsGetValue function and the macro creates an ad hoc sheet based on the parameters for the HsGetValue function.

I have always used a particular method for HsGetValue parameters: the dimensions are referenced individually (i.e. not a single string with a semi-colon separator), fixed dimensions are referenced using a named range (usually beginning with POV) and ‘grid’ dimensions use the Dimension# prefix plus the cell reference. This makes identifying the ad hoc column and row dimensions much easier for the macro:

Excel Event scripting

If you are familiar with FDM or FDMEE you will probably already know the concept of event scripting i.e. a script that executes in response to an event. In Excel the number of events is much larger and there are separate events for the workbook and the worksheet. For an explanation of how to add extended and robust event scripting to Excel see Chip Pearson’s excellent website:

http://www.cpearson.com/Excel/AppEvent.aspx

The following screenshot shows the starting point:

1: Double-click ThisWorkBook

2: Create a private variable called ExcelEvents as the type clsExcelEvents

3: In the Workbook_Open procedure instantiate the ExcelEvents object

4: Create the class module clsExcelEvents (Insert | Class module)

Now double-click clsExcelEvents. Create the variable XLApp (or whatever you want to call it) using the keyword WithEvents and as the type Application. Then instantiate it in the Class_Initialize procedure:

You have added extended event scripting to your Excel workbook. Select XLApp in the Object drop-down list and you will see a list of Procedures (Events) in the drop-down list to the right:

This shows you the events you can intercept and execute code for.

Event SheetBeforeDoubleClick

The event SheetBeforeDoubleClick is the one we want. Every time the user double-clicks a cell this procedure is executed. When the user double-clicks a cell with a HsGetValue formula, we want to pick up the dimension information and create an ad hoc sheet using those dimensions. This is where we want to put our code for creating the ad hoc sheet:

The user can double-click anywhere so you need to check that the cell the user clicked has a formula (ActiveCell.Formula) and that formula is HsGetValue. If it is an HsGetValue cell, you can start getting the metadata. This is an example of the format of the HsGetValue formula:

This makes the placing of the dimensions for the ad hoc grid simple: any dimension on the same row as the clicked cell will be placed in the row section of the ad hoc. Everything else goes to the columns. So split the formula parameters at the comma; the first member is the connection and the rest are dimensions. Check each dimension and if it contains # then it’s on the grid. Check the row reference for those with # and if it’s the same as the current row, then save it for the row members. Everything else is saved for the column members.

The key part here is that you do not want to save the reference, you want the value that is in the referenced cell. And you do that with the Excel function Evaluate:

In the formula, the connection is referenced using the named range ‘conn’. In the above screenshot, the value in the named range is evaluated so sConnection now contains FCCSDEV (or whatever my connection name is). You do the same with the dimensions; you are just interested in the member name and whether it’s going into the columns or rows:

At the end of processing the formula, you want four pieces of information: the connection name, the name of the new ad hoc sheet, the members for the column headers and finally the members for the row. Create the new sheet and on that new sheet add the column headers and row members. Once you have done that you need to make it an ad hoc sheet:

In order for these Smart View functions to work you need to add the file smartview.bas to your project. You can find it in the bin folder under the SmartView installation folder (default is C:\Oracle\SmartView\bin).

And the final touch is to make sure that the double-click option is set for this new sheet:

That’s it. Now whenever you double-click a cell which contains an HsGetValue formula, an ad hoc sheet is automatically generated using the dimension members referenced in the HsGetValue formula.

Example spreadsheet

You can download an example spreadsheet here. You will need to review the Control sheet to check the custom dimension names in column A. If you want to use this against HFM change cell A5 to Year#: