BEx 7.0 -- SAPBEXqueries hidden sheet gone -- where is the data stored now?

[Former Member]

We are currently in the process of upgrading from BEx 3.5 to 7.0, and in 7.0, both the SAPBEXqueries and SAPBEXfilters (very) hidden sheets are gone now. Where is BEX 7.0 storing that data?

With BEx 3.5, we built a very elaborate Excel VBA solution to automate the processing of BEx reports by reading the stored variable values in the SAPBEXqueries (very) hidden sheet, but with that sheet gone, what alternative place can I programmatically read the variable values from now?

Thank you.

[pavel_afanasiev · Accepted solution]

Hi Stephan,

the hidden sheets are gone, and the data is stored in a script object embedded into the workbook.

Besides the text object options, your other choice is to add the variable restriction into the Filter section in the query designer, and that way you will have the selection displayed in the navigation/filter objects

Regards,

Pasha

[Former Member · Accepted solution]

Thank you all for getting back to me -- I appreciate your replies, and my apologies for not getting back to you sooner.

Yes, I found out that all that metadata is now in the BExRepositorySheet's embedded XML script, which I retrieved with this little bit of Excel VBA code:

XMLScript = Worksheets("BExRepositorySheet").Scripts(1)

Debug.Print XMLScript

Then I copied all the xml content from the VBA IDE's Immediate window to all new file, and after searching through all the xml, found that the saved variables information was in there, like this:

...more xml...

<VAR>

<RRX_VAR>

<VNAM>ZSELLDGR</VNAM>

<SIGN>I</SIGN>

<OPT>EQ</OPT>

<LOW>LL</LOW>

<HIGH />

</RRX_VAR>

<RRX_VAR>

<VNAM>ZFISCPER</VNAM>

<SIGN>I</SIGN>

<OPT>EQ</OPT>

<LOW>2008001</LOW>

<HIGH />

</RRX_VAR>

etc...

</VAR>

...more xml...

Now, my next question is, where is the SAP documentation for working with the BExRepositorySheet's embedded XML script and reading from and writing to these nodes?

But I will close this thread since my original question has been answered and post a new question.

Thanks.

[pavel_afanasiev · Accepted solution]

Hi Stephan,

it is great that you could access the script with VB. I had to find Office 2003, as the microsoft script editor has been removed from 2007 and 2010.

I haven't seen any SAP provided documentation on functionality, but check out these two sessions from one of the BI and Portals

conferences:

http://csc-studentweb.lr.edu/swp/Berg/Conferences/SAP_Reporting_analytics_2007/pdf/Track8_session5.p...

http://csc-studentweb.lr.edu/swp/Berg/Conferences/SAP_NW2008_and_Admin_2008/BI/Track2/Track2_session...

Regards,

Pavel

[matthias_gemmel · Accepted solution]

Hi all,

there is a reason why BExRepositorySheet is hidden and also the metadata XML is very hidden in the Scripts collection. This XML should not be changed at all. Anyway it is only used by BEx Analyzer to store the metadata when saving the workbook and build up its internal object model when the workbook is opened.

With Excel >= 2007 the metadata is also existing but as Excel removed the Scripts collection with Excel 2007 file formats the metadata is hidden somewhere else. I guess someone already found out where it is stored but as mentioned before the XML should not be modified.

Regards

Matthias

[Former Member · Accepted solution]

Pavel and Matthias, I appreciate your additional input.

As for the BExRepositorySheet, I know we're "not supposed" to change the metadata in there, but then again, in 3.5, we weren't supposed to change the metadata in the SAPBEXqueries sheet either, but we did for this custom Excel VBA solution to automate the SAP BEx reports -- you simply have to be very precise about doing it.

And I might add that this custom Excel VBA solution has been a HUGE timesaver for the users in our company enterprise-wide. For an example, if an user needs to run 50 reports for a financial close, instead of sitting at his desk for hours, endlessly clicking through parameter screen after parameter screen, waiting for each report to finish and having to repeat the process all over again for each report, with the Excel VBA solution, with one click, automatically processess all 50 reports by writing to the SAPBEXqueries's cells where the variable values are stored for each report, then refreshing the report's query.

[matthias_gemmel · Accepted solution]

Hi,

7.0 Analyzer uses the same runtime than ABAP Web Runtime. With this there are various commands available to manipulate the query data, e.g. setting variable values. These commands can also be used in 7.0 Analyzer with the button item. Here you can set the commands to manipulate the variables for different data providers.

Regards

Matthias

[Former Member]

Since 7.0 pre-delivers the button for the filters, we chose to place the variables below the filters.

Adding Variables below filter:

1. In Design mode of a workbook, choose insert text button (we added the Variables below filters).

2. Double click on newly created text box and you will need to choose the range where you would like to put the variables. We chose to add the variables in C60:D85 to make sure to be below the filters and have enough space for larger sets.

3. You will also want to choose Display Caption.

4. On Filter tab, choose the Display Static Filters:

Good luck

[matthias_gemmel]

Hi Stephan,

I think the ooption you have now is to use a text element from BEx Analyzers design toolbar and display all variables (static filters) in a hidden sheet created by you. This only displays the static filters. Dynamic filters can be shown with the list of filters.

Regards

Matthias