Changing the key fugure in SAPBEX query by means of VBA

[Former Member]

Hello!

Is it possible program interface on VBA which allows to change a key figure in the query by user (e.g. using some controls on the worksheet).

Example.

InfoProvider has 2 key figures

- CostsUSD

- CostsEUR

Query initially designed with structure which contains key figure CostsUSD.

Is it possible to program some controls on the worksheet, something like "currency selector" and by mean of it switch from key figure costsUSD to key figure CostsEUR in the query?

Any ideas and answers are highly appreciated!

Sergey

[Former Member]

Hi Sergey,

If you followed Scott's advice, then you know already that none of the API functions allow you to change the display of Key Figures.

There is a way you can do this with VBA, but ... it is not documented ... there is no warranty from SAP ... and it is quite possible that a future system upgrade (or even a patch) could really mess you up.

Now that I've warned you, I'll tell you how to do it.

1. You need to look at the "very hidden" worksheet named "SAPBEXqueries". To make it visible, you can either:

- in the Visual Basic Editor, look in the Project Explorer window, locate the sheet named "SAPBEXqueries". Double-click to select it. In the Properties window, change the Visible property to Visible. -OR-

- in VB code, use the following:

Sheets("SAPBEXqueries").Visible = True

To make it very hidden again:

Sheets("SAPBEXqueries").Visible = xlSheetVeryHidden

2. If there is more than one query in the workbook, be sure that you know which is which. Use the local query ID (right-click, select Properties, look under the Information tab). You will find this in column F of the SAPBEXqueries sheet. Make note of which ROW the local query ID is found. This is critically important.

3. Locate the table which governs the display of Key Figures. Depending on which release you are using, you will probably find this table starting in column CM of the SAPBEXqueries sheet. If so, then column CR is the one that determines whether or not the KF is visible in the result table. Column CR will contain an X if never shown, a Y if hidden but can be shown, and will have only a tic-mark (') if the KF is visible.

If you need more help on this subject, post your e-mail address.

- Pete

[Former Member]

Sergey,

There exists really good documentation on the SAP Help website that go over the VBA Macros that are included in the BEx Analyzer. You can use the resultSet returned in the

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

macro and modify it.

Goto

http://help.sap.com

Under SAP Netweaver

SAP Business Information Warehouse -> BI Suite: Business Explorer -> Analysis & Reporting: BEx Analyzer -> Evaluating Query Data -> Queries in Workbooks -> Integration with Visual Basic for Applications

Hope this helps...

Cheers,

Scott