Excel VBA code needed to automate Essbase query

[gjl]

I've been searching the net for days now. I asked our IT department, they don't know either. Not sure if this is the right place, so apologies if I'm mistaken.


In the office we use Excel 2003 combined with Hyperion Essbase 6.5 to retrieve data from our databases.

Every month we prepare a large list of management and sales reports using our Hyperion database and the Essbase Excel add in. Due to the nature of our business clients and products (literally thousands for both of them) change every month. A number of other characteristics are added as well.


Currently we retrieve all products and clients in chunks (per report and update the report by doing vlookups both ways in order to add and delete clients and products.


Essbase has a query designer that I used to create a custom query. Did the same with the advanced member selection. However after I save these query files I don't know how to load and execute them in Excel using VBA.


Static retrieves using VBA are no problem using the standard Essbase functions. The retrieve I build is quite complex and is ill-suited for a standard retrieve. Does anybody have any experience with this and has some VBA code with which I can run the queries I saved.


I want to speed up the reporting process and my only other option is to retrieve all info by hand and put it into an Access database and fill my Excel sheets from here. Yes I know, it sounds silly and pretty desperate. If you have a practical solution I would really appreciate it.

 

[Hui]

Have you tried

1. Using the Macro Recorder when you make a query

2. Using the Macro Recorder when you make an extraction

 

[gjl]

Hui thanks for your suggestion.


The Excel macro recorder doesn't recognize Essbase.


I found another solution that I want to share


When saving a query two files are generated an query file (*.eqd) and a report file (*.rep)

You can drag the report file into Excel where it is displayed as plain text.

After that you can perform the retrieval, but you have to enable the freeform retrieval option. I’m not sure what the template option does though, have it disabled for now.

The easiest thing to do is to save the query embedded as text in an Excel sheet.

Done this way you can also link to variables on other sheets, which makes you more flexible while retrieving.

Note that Excel does not pick up the year automatically on our system. You have to add year in front of the number.


Query designer is quite fast while retrieving (notwithstanding your network of course .

Also it gives you great flexibility in designing retrievals

It fast and easy to build retrieves once you get the hang of it.

There is an option to use filter, e.g. to create top10/20 lists etc.


One caveat though, it seems that Essbase does not warn you when you get past the 2 EXP 16 lines in Excel 2003 . It simply truncates the rest.


Undoubtedly there are more possibilities/issues. I’ll find out in due course.