Posted: Nov 27, 2004 5:03 PM


Hi Patrick,

Glad that was helpful. I have not found specification of the repository sheets anywhere. I have done some mapping myself. I am not sure how well this will paste here, but I'll give it a try.

I give both letter and number of each column. If I don't mention a column it is either blank or I am clueless.

Be aware that the details will change whenever you upgrade to the next version of BW.

In each workbook containing BW queries, all of the local query definitions and filters are stored in hidden worksheets.

There is one sheet that gives the query definition (which characteristics and key figures are in the query; which characteristics are in the results table, which Key Figures are displayed and which are hidden; how many decimal points to show, et cetera). This sheet is named "SAPBEXqueries".

The other contains everything else - which means filters and conditions (filters embedded in the workbook itself, conditions which are active, hierarchies which are active, et cetera). This sheet is named "SAPBEXfilters".

SAP called these "repository sheets", or "repo sheets" for short. These sheets can not be made visible from within Excel. But, they can be made visible from the Visual Basic editor, or by using Visual Basic code.

Nearly everything that can be accomplished through OLAP functions and Change query (local view) can be accomplished as well by changing the entries on these sheets. Knowing exactly which cells to change and how to change them is not trivial. It is far easier for the average user to utilize the OLAP functions and Change query! The only reason to ever want to make changes directly to the repo sheets is to automate functions ... for example, to use the results of one query to set the filters for another query. Or, to jump from one info-provider to another to enable drill-through that is not possible from one info provider alone.

The following describes the layout of these sheets.

The primary sheet is the "SAPBEXqueries" sheet. In this sheet there are 8 arrays as follows:
1. A table listing all queries embedded in the workbook (Query table)
2. A table of the query and results table’s dimensions (DIM table)
3. A table detailing Key Figures (MEM table)
4. A table detailing non-navigational (display-only) attributes (ATR table)
5. A table detailing display attributes of Key Figures that are shown (CEL table)
6. A table that is always blank for me ... (HRY_TYPES table)
7. A table listing refresh screen variables and the filters last entered for these (VAR table)
8. Another table that has more junk about the query definition that I never have quite figured out. (PRPTYS table)

The layout of each array is similar in this regard: at the top right of the table are two numbers. The first number, in row 2, gives the number of rows in the table below; let’s call this “x”. The second number, in row 3, gives the number of columns in the table below, let’s call this “y”. The Table always starts in row 4, and continues through to row x+3. The table will have y+1 columns.

Table 1, the list of queries, is critically important. This table header numbers are found in cells “A2” and “A3” of the SAPBEXqueries sheet. Cell A2 tells BW the number of queries embedded in the workbook. When you press the “refresh all” button, BW will refresh the queries starting with the last query in this table and work its way up the list. Let’s say there are 5 queries in the workbook; cell A2 will contain the number 5. If you (just for the heck of it) change this number from 5 to 3, then BW will only refresh the first 3 queries in the list. The other two queries are still there, but are invisible to BW until you decide to change the number in A2 back to 5.

All the other tables in both repo sheets refer back to this table. The reference is by which row each query is listed. Let’s say you wanted to change the order in which queries are refreshed when you press the “refresh all” button. You might try to re-sort Table 1. This would work, as long as all 5 queries are identical. But, if there are any differences in the way the 5 queries are displayed or filtered, you will be in for a rude surprise. If all you do is re-sort Table 1, then BW will associate all of the other tables in both repo sheets with the queries that are now in those rows, making a complete mess out of your workbook.

If you want to change the order in which queries are refreshed, you would need to ... along with re-sorting Table 1 ... re-number every row in every table in both repo sheets to correspond with the new order. This is rather tedious to do by hand. I have developed a utility that does this.

Let’s look at the columns in Table 1.

Column Contains
B/2 number of times query refreshed this session
C/3 Query ID (server) – query properties “Information” tab
D/4 Is the query valid?
E/5 Has layout been committed to the server?
F/6 Query ID (local) – query properties “Information” tab. This number always has the format “SAPBEXq000x”, where x is a number like 1, 2, or 3. Generally, the first query you embed will be named SAPBEXq0001, the second will be named SAPBEXq0002, et cetera.
G/7 First data row in result table (relative to first header row)
H/8 First data column in result table (relative to first column)
I/9 text elements and filter cells must be updated with next refresh?
J/10 blank/not used
K/11 blank/not used
L/12 Adjust format after data refresh? – query properties “Display” tab
M/13 result was clipped on last refresh -> hierarchy ops are denied!
N/14 If chart is attached, index of attached chart
O/15 Enable interactive functions? – properties “Interaction” tab
P/16 Save and reuse variable values? – query properties “Interaction” tab
Q/17 Adjust column width on refresh – query properties “Column width” tab
R/18 If map is attached, index of attached map
S/19 version of BW
T/20 show filter cells for structure dimensions?
U/21 Refresh query when opening workbook? – properties “Interaction” tab
W/22 string: tag from last jump
X/23 repeat last jump on double-click
Y/24 Return to global definition on refresh? – properties “Interaction” tab
Z/25 query does not have a persistent definition in the BW Server?
AA/26 query does not allow drill operations?


For every Table, except the first table, there are never any blank spaces. Although some spaces appear to be blank, they always contain at least a tic-mark (‘). The first column always contains the tie back to Table 1. This entry is an integer. All other entries are strings, preceded by a tic-mark (‘).

The headers for Table 2 are found in cells AE2 and AE3. This table gives the layout of the queries and results tables. There are 54 columns in Table 2 as follows:
AE/31 layout of query – integers that are the row number this query is listed in Table 1
AF/32 technical name of the characteristics and Key Figures (ex: “0SOLD_TO”)
AG/33 local name of the characteristics and Key Figures (ex: “” Customer”)
AH/34 “X” if it is a characteristic; blank if it is a Key Figure
AI/35 “X” if it is a Key Figure
AJ/36 axis for drill (X is drill horizontal; Y is drill vertical)
AK/37

For free characteristics – gives the order that the items are listed in the original query definition; for items used in query result, gives order they appear in results table
AL/38 Key displayed in filter cell
AM/39 Text displayed in filter cell
AN/40 Internal filter value??
AO/41 Hierarchy value
AS/45
How displayed: 0=Key+Name; 1=Name only; 2=Key only; 3=Name+Key; 6=not displayed

The headers for Table 3 are found in cells CM2 and CM3. This table dimensions the Key Figures. There are 9 columns in Table 3 is as follows:
CM/91 Ties back to Table 1 - integers that are the row number this query is listed in Table 1
CN/92 structure key figures appear in
CO/93 A unique identifier for this Key Figure
CP/94 Key figure local name
CQ/95 order that key figures appear in the original query definition
CR/96 X if never show; Y if hidden but can be shown; blank if shown
CS/97 ?

The headers for Table 4 are found in cells DG2 and DG3. This table details non-navigational (display-only) attributes. I haven’t used it, so I haven’t mapped it.

The headers for Table 5 are found in cells EA2 and EA3. This table gives details about display of Key Figures
EA/131 Ties back to Table 1 - integers that are the row number this query is listed in Table 1
EB/132 A unique identifier for this Key Figure – ties back to table 2
EC/133 ??
ED/134 ??
EE/135 Number of decimal places in the display

The headers for Table 6 are found in cells EU2 and EU3. This table details hierarchies used. I haven’t used it, so I haven’t mapped it.

The headers for Table 7 are found in cells FY2 and FY3. This table shows entries on the variable refresh screen
FY/181 Ties back to Table 1 - integers that are the row number this query is listed in Table 1
FZ/182 Technical name of variable
GA/183 ??
GB/184 ??
GC/185 I if an inclusion filter; E is an exclusion filter
GD/186 EQ if equal to; BT if between (are GT, LT, LE, GE allowed?)
GE/187 Internal filter value – if a range, this is the first half of the range
GF/188 External (display) filter value – if range, this is the first half of the range
GG/189 if a range, this is the second half of the range - Internal value
GH/190 if a range, this is the second half of the range - External (display) filter value
GI/191 Name for filter (what is displayed as text)

GS/201 Technical name of characteristic that filter will be set on

The headers for Table 8 are found in cells HW2 and HW3. This table gives more details about the query itself
HW/231 Ties back to Table 1 - integers that are the row number this query is listed in Table 1
HX/232 An identifier for this information
HY/233 The corresponding information for this query

Some examples of Table 8 information:
Column HX
(leading zeros dropped) Column HY

10001 Query ID (server) – query properties “Information” tab; same entry as was found in column C
9001 Text element “REPTXTLG”
100 Suppress repeated key values (query Properties, Display tab). If checked, this cell is X; if not checked, this cell is blank
103 Adjust format after data refresh (query Properties, Display tab). If checked, this cell is blank; if not checked, this cell is X.


Secondary Table is SAPBEXfilters. It has 3 tables:
1. The first table, beginning in cell CW2, I have no experience with.
2. The second table, beginning in cell EZ2, connects the characteristics and conditions with the filter values, which are in the third table:
3. Beginning in cell GX, this table gives details of filter values that are placed in the workbook itself (using OLAP), or conditions.


Like the tables in the query repo sheet, there are never any blank spaces. Although some spaces appear to be blank, they always contain at least a tic-mark (‘). The first column always contains the tie back to Table 1. This entry is an integer. All other entries are strings, preceded by a tic-mark (‘).

The headers for Table 2 are found in cells EZ2 and EZ3. This table connects the characteristics and conditions with the filter values in Table 3.
EZ/156 Ties back to query Table 1 - integers that are the row number this query is listed in
FA/157 A unique identifier for this characteristic or condition – ties to filter Table 3
FB/158 Display name for this characteristic or condition
FC/159 X if filter or condition is active; L if condition is not applicable; blank if condition is not active
FD/160 ?
FE/161 ?
FF/162 Technical name of characteristic or condition

The headers for Table 3 are found in cells GX2 and GX3. This table details filters and conditions.
GX/206 Ties back to query Table 1 - integers that are the row number this query is listed in
GY/207 A unique identifier for this characteristic or condition – ties back to filter Table 2
GZ/208 Structure or value for condition; blank for characteristic
HA/209 Technical name of structure that condition appears in; blank for characteristic
HB/210 If there are multiple filter on a characteristic, this counts the number
HC/211 1 if structure or characteristic; 2 if value for condition

HF/214 I for inclusion filter or E for exclusion filter
HG/215 EQ for equal; BT for between; other values are GT, LT, GE, LE
HH/216 Internal filter value – if a range, this is the first half of the range
HI/217 if a range, this is the second half of the range - Internal value; else, is blank
HJ/218 External (display) filter value – if a range, this is the first half of the range
HK/219 if a range, this is the second half of the range – external (display) value; else, is blank

HP/224 Display text for filter value– if a range, this is the first half of the range
HQ/225 if a range, this is the second half of the range – display text; else, is blank

полезная инфа