Skip to Content
author's profile photo Former Member
Former Member

Changing the key fugure in SAPBEX query by means of VBA

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Feb 10, 2005 at 06:29 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 11, 2005 at 04:02 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.