Essbase Add-in

[KobusD]

I have written some VBA code to retrieve information using the Essbase
add-in. I am able to manage the Excel errors and warnings so that the
code can continue without intervention from the user, but I am not
able to do the same with the Essbase errors & warnings.

Has anyone had any experience with this so that I can be pointed in
the correct direction?

 

[Ron Coderre]

I have extensive experience automating Essbase through Excel via the API.

Start by downloading the demo in the EB_Pull_All_Sheets.zip from the
Contextures website:
http://www.contextures.com/excelfilesRon.html

The file is in the External Data section
with the heading "Pull Essbase Data into all Worksheets"


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Jim Thomlinson]

Manually you can go into Essbase -> Options -> Global and change the options
on the messages. If you want a more automated method then you need to look
into the API's for Essbase. In XL you can look at Essbase Help and search for
EssVGetGlobalOptions / EssVSetGlobalOption...

 

[Ron Coderre]

Good point, Jim.

This may help...In the file I referenced above, I annotated many of the
functions in the modEB_Declarations module with parameter options (mainly so
I wouldn't need to keep toggling between my code and the help files.) One of
the better documented functions is the EssVSetGlobalOption function.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[KobusD]

Thanks guys! Didn't expect such a prompt & detailed reply

In my solution I am more interested in the error / warning codes after
a connection has been made. Warnings such as "maximum database rows
have been exceeded" or the warning when a sheet retrieval produced
zero lines. Due to my strict deadline I had to come up with a "dirty"
work-around, but I need to now clean-up before the next deadline.

The first warning relates to an Essbase setting called the
SSPROCROWLIMIT. The warning message is displayed and no retrieval
takes place. I would like to capture some "warning code" and do some
VBA when this occurs, but I can't seem to find what these codes are.

A description about the setting (for interest sake): ********# This
controls the maximum number of spreadsheet rows Essbase processes on
an Excel Add-in user request. SSPROCROWLIMIT is in effect only for
Excel Add-in when the Suppress #Missing Rows option is selected The
rows are counted before suppression; that is, missing rows and rows
containing zero values are included.

When users zoom in on one or more members, Essbase must process a
larger grid containing selected members expanded to the zoom-in level
set in the options. When the Suppress #Missing Rows option is set,
Analysis Services returns only rows with at least one column
containing a non-missing value. SSPROCROWLIMIT defines the maximum
size (number of rows) of the larger grid that Essbase needs to
process. This setting prevents excessive memory usage for a single
spreadsheet operation.

When the Excel Suppress #Missing Rows option is not selected, the
limit is 64000. #********