Introduction
In SAP BW on HANA or BW4HANA, there is no straightforward way to get query elements details like Variables details, Filter Details, Default Values Details, Row /Column Selection Details etc. used in a Bex query.
Especially in migration projects, it is very difficult to collect the inventory details for Bex queries. To check the elements of a complex query or multiple queries is really painful at times. We must manually open each selection and look at the selections one by one and it’s a time-consuming activity.
With the help of this document, we will be able to correlate various tables in the backend where the elements of the Query are saved.
In this blog, we will try to cover some of the important concepts of query elements and how they can be cross referred and checked in the backend tables.
Below are the some of the important tables used in this blog to understand elements of a BEx query.
Important Tables
Table Name
Relevance
RSZRANGE
This table has all the details related to the selections in a Query
RSZCOMPDIR
Details of Query, like the COMPUID(ID of the Query ) , owner name , last changed, creation date etc.
RSRREPDIR
This table is similar to RSZCOMPDIR table but additionally we will get info provider name on which query is built.
RSZELTXREF
Details of the various references of objects used in the Query like row details, column details, filter details , default values, exceptions ,conditions etc. This is the main reference table for all the objects where the technical references are maintained.
RSZCALC
This table has all the Formula details of the Query level selections
RSZELTPROP
This table has the details of all the properties of the query level selections like the hierarchy used at the default restrictions, scaling factors, input readiness of cells ,etc.
RSZELTTXT
This table has the text details of all the selections in the query.
Before going to the actual details, it would be great if we understand the structure of a tables RSZELTXREF and RSZRANGE
Structure of the table RSZELTXREFScreenshot 1: Structure of the table RSZELTXREF
The table RSZELTXREF gives a list of cross references with the below values for the field LAYTP. Segregation based on the below mentioned LAYTP types.
LAYTP
Reference to the query
VAR,QVS
Query Variable Sequence with the sequence mentioned in the field POS
NILL
Exceptions and Conditions
SHT
Cell restrictions, Row and Column Structure, Default values and Free Char
SOB
Row and column and filter values.
AGG
Filter Details
ROW
Row Details
COL
Column Details
CELL
Cell References
Structure of the table RSZRANGE
Screenshot 2: Structure of the table RSZRANGE
Some of the important fields in RSZRANGE table
Field Name
Relevance
ELTUID
Unique ID Of the selection
IOBJNM
The respective Info Object on which the restrictions have been made
ENUM
Order of restriction in a selection
SELTYP
Has various values to describe the selection type
SIGN
Describes the type of selection sign for ex Equal to or not equal to
i.e Include or exclude a selection
OPT
Has the sign of operator i.e., eq, lt, gt etc.
LOW
Has the low value
HIGH
In case of range values, the high value is populated
LOWFLAG
Describes the types of value used in a low field. Mentioned below are
the various allowed types
SHIFTLOW
In case of an offset usage, the flag is used to set the offset
values i.e(+,- the values)
HIGHFLAG
Describes the types of value used in a high field
HIENM
Name of hierarchy if any used
HIENMFLAG
Flag which holds the value of type of selection for hierarchy name
VERSION
Version name
VERSIONFLAG
Flag which holds the value of type of selection for Version
DATETO
The TO date of the Hierarchy.
DATETOFLAG
Flag which holds the value of type of selection for To Date.
Now let’s begin on how to obtain various technical details of the Queries.
In this blog post, covering the below topics in detail.
Filter DetailsDefault Values DetailsTechnical Info object Names of all the Default valuesFor Selecting the Hierarchy in Default valuesRow /Column Selection DetailsRow /Column formulae DetailsRow /Column Text DetailsCell References DetailsExceptions and conditions
Now let’s discuss one by one in detail.
1. Filter Details
Step 1 : In the Table RSZCOMPDIR / RSRREPDIR enter the query technical name in the field COMPID to obtain the COMPUID of the query.
Screenshot 3: Output of RSZCOMPDIR table
Step 2 : In the RSZELTXREF table, enter the COMPUID of the query(from step 1) in the field SELTUID and execute the table.
Screenshot 4 : Output of RSZELTXREF table
Step 3: Again, in the RSZELTXREF table, enter TELTUID (from step 2, where LAYTP = ‘SOB’) in the field SELTUID and execute the table.
Screenshot 5 : Output of RSZELTXREF table
Step 4: In the RSZRANGE table, enter the TELTUID (from step 3, where LAYTP = ‘AGG’) in the field SELTUID and execute the table to obtain the technical details of the Filters as depicted in the below screenshot.
Screenshot 6: Output of RSZRANGE table
Note: Follow the same process for ‘n’ number of queries.
2. Default Values Details
Step 1: In the Table RSZCOMPDIR / RSRREPDIR enter the query technical name in the field COMPID to obtain the COMPUID of the query.
Screenshot 7: Output of RSZCOMPDIR table
Step 2: In the RSZELTXREF table, enter the COMPUID of the query (from step 1) in the field SELTUID and execute the table.
Screenshot 8 : Output of RSZELTXREF table
Step 3: Again, in the RSZELTXREF table, enter TELTUID (from step 2, where LAYTP = ‘SHT’) in the field SELTUID and execute the table.
Screenshot 9 : Output of RSZELTXREF table
Step 4: In the RSZRANGE table, enter the TELTUID (from step 3, where LAYTP = ‘AGG’) in the field SELTUID and execute the table
Screenshot 10 : Output of RSZRANGE table
Note: Follow the same process for ‘n’ number of queries.
3. Technical Info object Names of all the Default values
In default values details (section 2), we got default values with restriction details only, but we may have some other info objects without restriction in default values section. To find the info objects names of all the Default values, follow the below steps.
Follow step 1, step 2 and step 3 from Default values details section.
In the RSZELTDIR table, enter the TELTUID (from step 3, where LAYTP = ‘AGG’) in the field SELTUID and execute the table to obtain Info object Names of all the Default values.
Screenshot 11: Output of RSZELTDIR table
4. For Selecting the Hierarchy in default values
Follow step 1, step 2 and step 3 from Default values details section.
In the RSZELTPROP table, enter the TELTUID (from step 3, where LAYTP = ‘AGG’) in the field SELTUID and execute the table to obtain the list of all the records with Hierarchies.
Screenshot 12 : Output of RSZELTPROP table
5. Row /Column Selection Details
Step 1: In the Table RSZCOMPDIR / RSRREPDIR enter the query technical name in the field COMPID to obtain the COMPUID of the query.
Screenshot 13 : Output of RSZCOMPDIR table
Step 2: In the RSZELTXREF table, enter the COMPUID of the query (from step 1) in the field SELTUID and execute the table.
Screenshot 14 : Output of RSZELTXREF table
Step 3: Again, in the RSZELTXREF table, enter TELTUID (from step 2, where LAYTP = ‘SHT’) in the field SELTUID and execute the table.
Screenshot 15 : Output of RSZELTXREF table
Step 4: Again, in the RSZELTXREF table, enter TELTUID (from step 3, where LAYTP = ‘COL/ROW’) in the field SELTUID and execute the table.
Screenshot 16 : Output of RSZELTXREF table
Step 5: In the RSZRANGE table, enter the TELTUID (from step 4) in the field SELTUID and execute the table to obtain the selection details in Rows/Columns.
Screenshot 17 : Output of RSZRANGE table
Note: Follow the same process for ‘n’ number of queries.
6. Row /Column Formulae Details
Follow step 1, step 2, step 3 and step 4 from above Row /Column selection details section.
In the RSZCALC table, enter the TELTUID (from step 4) in the field SELTUID and execute the table to obtain the formulae details in Rows/Columns.
Screenshot 18 : Output of RSZCALC table
7. Row /Column text Details
Follow step 1, step 2, step 3 and step 4 from above Row /Column selection details section.
In the RSZELTXT table, enter the TELTUID (from step 4) in the field SELTUID and execute the table to obtain the text details in Rows/Columns.
Screenshot 19 : Output of RSZELTTXT table
8. Cell References Details
Step 1: In the Table RSZCOMPDIR / RSRREPDIR enter the query technical name in the field COMPID to obtain the COMPUID of the query.
Step 2: In the RSZELTXREF table, enter the COMPUID of the query (from step 1) in the field SELTUID and execute the table.
Step 3: Again, in the RSZELTXREF table, enter TELTUID (from step 2, where LAYTP = ‘SHT’) in the field SELTUID and execute the table.
Step 4: In the RSZRANGE table, enter the TELTUID (from step 3, where LAYTP = ‘CELL’) in the field SELTUID and execute the table to obtain cell references of type selection.
Note: if you want to obtain cell references of types formulae then use the table RSZCALC instead of RSZRANGE in step 4. Similarly use the table RSZELTPROP to obtain just cell level references.
9. Exceptions and Conditions
Follow step 1, step 2 and step 3 from above section.
In the RSZRANGE table, enter the TELTUID (from step 3, where LAYTP = ‘NIL’) in the field SELTUID and execute the table to obtain the exceptions and conditions.
Note: In RSZRANGE table, in the field IOBJN, if the value is 1CONDITIONS then it’s for conditions and in case of Exception the value in the field IOBJNM is 1EXCEPTIONS.
IntroductionIn SAP BW on HANA or BW4HANA, there is no straightforward way to get query elements details like Variables details, Filter Details, Default Values Details, Row /Column Selection Details etc. used in a Bex query.Especially in migration projects, it is very difficult to collect the inventory details for Bex queries. To check the elements of a complex query or multiple queries is really painful at times. We must manually open each selection and look at the selections one by one and it’s a time-consuming activity.With the help of this document, we will be able to correlate various tables in the backend where the elements of the Query are saved.In this blog, we will try to cover some of the important concepts of query elements and how they can be cross referred and checked in the backend tables.Below are the some of the important tables used in this blog to understand elements of a BEx query. Important TablesTable NameRelevanceRSZRANGEThis table has all the details related to the selections in a QueryRSZCOMPDIRDetails of Query, like the COMPUID(ID of the Query ) , owner name , last changed, creation date etc.RSRREPDIRThis table is similar to RSZCOMPDIR table but additionally we will get info provider name on which query is built.RSZELTXREFDetails of the various references of objects used in the Query like row details, column details, filter details , default values, exceptions ,conditions etc. This is the main reference table for all the objects where the technical references are maintained.RSZCALCThis table has all the Formula details of the Query level selectionsRSZELTPROPThis table has the details of all the properties of the query level selections like the hierarchy used at the default restrictions, scaling factors, input readiness of cells ,etc.RSZELTTXTThis table has the text details of all the selections in the query.Before going to the actual details, it would be great if we understand the structure of a tables RSZELTXREF and RSZRANGEStructure of the table RSZELTXREFScreenshot 1: Structure of the table RSZELTXREF The table RSZELTXREF gives a list of cross references with the below values for the field LAYTP. Segregation based on the below mentioned LAYTP types.LAYTPReference to the queryVAR,QVSQuery Variable Sequence with the sequence mentioned in the field POSNILLExceptions and ConditionsSHTCell restrictions, Row and Column Structure, Default values and Free CharSOBRow and column and filter values.AGGFilter DetailsROWRow DetailsCOLColumn DetailsCELLCell ReferencesStructure of the table RSZRANGEScreenshot 2: Structure of the table RSZRANGE Some of the important fields in RSZRANGE tableField NameRelevanceELTUIDUnique ID Of the selectionIOBJNMThe respective Info Object on which the restrictions have been madeENUMOrder of restriction in a selectionSELTYPHas various values to describe the selection typeSIGNDescribes the type of selection sign for ex Equal to or not equal toi.e Include or exclude a selectionOPTHas the sign of operator i.e., eq, lt, gt etc.LOWHas the low valueHIGHIn case of range values, the high value is populatedLOWFLAGDescribes the types of value used in a low field. Mentioned below arethe various allowed typesSHIFTLOWIn case of an offset usage, the flag is used to set the offset values i.e(+,- the values)HIGHFLAGDescribes the types of value used in a high fieldHIENMName of hierarchy if any usedHIENMFLAGFlag which holds the value of type of selection for hierarchy nameVERSIONVersion nameVERSIONFLAGFlag which holds the value of type of selection for VersionDATETOThe TO date of the Hierarchy.DATETOFLAGFlag which holds the value of type of selection for To Date.Now let’s begin on how to obtain various technical details of the Queries.In this blog post, covering the below topics in detail.Filter DetailsDefault Values DetailsTechnical Info object Names of all the Default valuesFor Selecting the Hierarchy in Default valuesRow /Column Selection DetailsRow /Column formulae DetailsRow /Column Text DetailsCell References DetailsExceptions and conditionsNow let’s discuss one by one in detail. 1. Filter DetailsStep 1 : In the Table RSZCOMPDIR / RSRREPDIR enter the query technical name in the field COMPID to obtain the COMPUID of the query.Screenshot 3: Output of RSZCOMPDIR table Step 2 : In the RSZELTXREF table, enter the COMPUID of the query(from step 1) in the field SELTUID and execute the table.Screenshot 4 : Output of RSZELTXREF table Step 3: Again, in the RSZELTXREF table, enter TELTUID (from step 2, where LAYTP = ‘SOB’) in the field SELTUID and execute the table.Screenshot 5 : Output of RSZELTXREF table Step 4: In the RSZRANGE table, enter the TELTUID (from step 3, where LAYTP = ‘AGG’) in the field SELTUID and execute the table to obtain the technical details of the Filters as depicted in the below screenshot.Screenshot 6: Output of RSZRANGE table Note: Follow the same process for ‘n’ number of queries. 2. Default Values DetailsStep 1: In the Table RSZCOMPDIR / RSRREPDIR enter the query technical name in the field COMPID to obtain the COMPUID of the query.Screenshot 7: Output of RSZCOMPDIR table Step 2: In the RSZELTXREF table, enter the COMPUID of the query (from step 1) in the field SELTUID and execute the table.Screenshot 8 : Output of RSZELTXREF table Step 3: Again, in the RSZELTXREF table, enter TELTUID (from step 2, where LAYTP = ‘SHT’) in the field SELTUID and execute the table.Screenshot 9 : Output of RSZELTXREF table Step 4: In the RSZRANGE table, enter the TELTUID (from step 3, where LAYTP = ‘AGG’) in the field SELTUID and execute the tableScreenshot 10 : Output of RSZRANGE table Note: Follow the same process for ‘n’ number of queries. 3. Technical Info object Names of all the Default valuesIn default values details (section 2), we got default values with restriction details only, but we may have some other info objects without restriction in default values section. To find the info objects names of all the Default values, follow the below steps.Follow step 1, step 2 and step 3 from Default values details section.In the RSZELTDIR table, enter the TELTUID (from step 3, where LAYTP = ‘AGG’) in the field SELTUID and execute the table to obtain Info object Names of all the Default values.Screenshot 11: Output of RSZELTDIR table 4. For Selecting the Hierarchy in default valuesFollow step 1, step 2 and step 3 from Default values details section.In the RSZELTPROP table, enter the TELTUID (from step 3, where LAYTP = ‘AGG’) in the field SELTUID and execute the table to obtain the list of all the records with Hierarchies.Screenshot 12 : Output of RSZELTPROP table 5. Row /Column Selection DetailsStep 1: In the Table RSZCOMPDIR / RSRREPDIR enter the query technical name in the field COMPID to obtain the COMPUID of the query.Screenshot 13 : Output of RSZCOMPDIR table Step 2: In the RSZELTXREF table, enter the COMPUID of the query (from step 1) in the field SELTUID and execute the table.Screenshot 14 : Output of RSZELTXREF table Step 3: Again, in the RSZELTXREF table, enter TELTUID (from step 2, where LAYTP = ‘SHT’) in the field SELTUID and execute the table.Screenshot 15 : Output of RSZELTXREF table Step 4: Again, in the RSZELTXREF table, enter TELTUID (from step 3, where LAYTP = ‘COL/ROW’) in the field SELTUID and execute the table.Screenshot 16 : Output of RSZELTXREF table Step 5: In the RSZRANGE table, enter the TELTUID (from step 4) in the field SELTUID and execute the table to obtain the selection details in Rows/Columns.Screenshot 17 : Output of RSZRANGE table Note: Follow the same process for ‘n’ number of queries. 6. Row /Column Formulae DetailsFollow step 1, step 2, step 3 and step 4 from above Row /Column selection details section.In the RSZCALC table, enter the TELTUID (from step 4) in the field SELTUID and execute the table to obtain the formulae details in Rows/Columns.Screenshot 18 : Output of RSZCALC table 7. Row /Column text DetailsFollow step 1, step 2, step 3 and step 4 from above Row /Column selection details section.In the RSZELTXT table, enter the TELTUID (from step 4) in the field SELTUID and execute the table to obtain the text details in Rows/Columns.Screenshot 19 : Output of RSZELTTXT table 8. Cell References DetailsStep 1: In the Table RSZCOMPDIR / RSRREPDIR enter the query technical name in the field COMPID to obtain the COMPUID of the query.Step 2: In the RSZELTXREF table, enter the COMPUID of the query (from step 1) in the field SELTUID and execute the table.Step 3: Again, in the RSZELTXREF table, enter TELTUID (from step 2, where LAYTP = ‘SHT’) in the field SELTUID and execute the table.Step 4: In the RSZRANGE table, enter the TELTUID (from step 3, where LAYTP = ‘CELL’) in the field SELTUID and execute the table to obtain cell references of type selection.Note: if you want to obtain cell references of types formulae then use the table RSZCALC instead of RSZRANGE in step 4. Similarly use the table RSZELTPROP to obtain just cell level references. 9. Exceptions and ConditionsFollow step 1, step 2 and step 3 from above section.In the RSZRANGE table, enter the TELTUID (from step 3, where LAYTP = ‘NIL’) in the field SELTUID and execute the table to obtain the exceptions and conditions.Note: In RSZRANGE table, in the field IOBJN, if the value is 1CONDITIONS then it’s for conditions and in case of Exception the value in the field IOBJNM is 1EXCEPTIONS. Read More Technology Blogs by Members articles
#SAP
#SAPTechnologyblog
