Skip to Content

Understanding the Query Elements

Introduction

With the help of this document we will be able to correlate to the various tables in the backend where the objects of the Query are saved in the Query Designer.

To check the elements of a big complex query is really painful at times. We have to manually open each selection and look at the selections one by one.

However the easier solution is looking at the backend tables where all the entries of the tables are stored.
With the help of this document we will try to cover some of the important concepts of the BEX Query Designer, and how they can be cross referred and checked at the backend.

Important Tables Related To Query Elements

Table Name

Table Relevance

RSZCOMPDIR

Details of Reporting Query ,like the COMPUID(ID of the Reporting Query ) , owner name , last changed, creation date etc.

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

RSZRANGE

This table has the details of all the selections in the Query

RSZELTPROP

This table holds 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 reporting query.

RSZGLOBV

This table has the details of the variable (all variables).

Demystifying the queries:-

Now let’s concentrate on how to obtain various technical details of the Queries

In the sections to come we will cover the various technical objects of the queries like Default values, Filters, Row Structure , Column Structure ,Selections at row/column level ,Cell level properties, Cell restriction , Conditions and exceptions and so on.


Obtaining the COMPUID of the Table

In the Table RSZCOMPDIR enter the query technical name in the field COMPID to obtain the COMPUID of the query.

/wp-content/uploads/2015/11/2_823518.png

Table RSZELTREF


Now we have the COMPUID of the Query. Now we have to cross refer to various tables to find out the
query elements.

The table RSZELTXREF gives a list of cross references with the below values for the field LAYTP. Mentioned below is the segregation based on the LAYTP .

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

With each corresponding SELTUID we have a corresponding TELUID as mentioned in the below screenshot.

/wp-content/uploads/2015/11/3_823519.png

Now let’s discuss how we can obtain various elements of the query

Query Variable Sequence Details

In the RSZELTXREF table enter the COMPUID of the query in the field SELTUID ,  select TELTUID for all the entries where LAYTP = ‘VAR’ or ‘SOB’.

Now lookup for all these values in the standard table RSZELTTXT where ELTUID = SELTUID to obtain the text of the Query variable
sequence

Also the position of the Query variable can be found in Table RSZELTXREF in the field POSN.

/wp-content/uploads/2015/11/4_823523.png

In the screenshot above SELTUID is the COMPUID of the query and LAYTP is the type of selection , net to it is the Position .


NOTE: This detail will have the Text of the Variable and not the technical name, for further details on the technical names please refer to the respective row / column structure details.


Filter Details


The filter details of the query are maintained in the LAYTP  ‘SOB’. When we cross refer these values in the RSZELTREF table, the TELTUIDs
with LAYTP = “AGG” is displayed which are the filter values.

Let’s take a look at the detailed approach using screenshots.

To obtain the filter details we will select the TELTUID from the Table RSZELTXREF where LAYTP = ‘SOB’.

Step 1:- Details of the TELUID’s in the RSZELTXREF Table:-


For each SELTUID there can be multiple references which are tracked in the TELUID . It is basically a reference of an element in the query .

For example , each query will have multiple elements in it . Each element is identified by maintaining a list of cross references , so each element is a cross reference . A row structure will have various rows , each row will be identified as a cross reference . Now with a row ,  a selection in the row which can further be broken down into specific Info Objects used for this selection . All this information is traced via the cross references of SELTUID and TELTUID’s.


The first step is to find all the elements in the query . Start with the COMPUID of the query derived from the COMPDIR Table and get all the TELTUID’s by setting SELTUID= COMPUID as mentioned in the below screenshot .

/wp-content/uploads/2015/11/5_823560.png

Step 2 : Select the TELUID and copy it to the SELTUID where LAYTP = ‘SOB’ in the selection for master data of the TABLE RSZELTXREF to get the new list of
TELUID:-

/wp-content/uploads/2015/11/1_835893.png

Now in the above mentioned screenshot the  value TELTUID  where LAYTP =’SOB’ is copied into SELTUID Please note that in the above 2 screenshots the fields marked in red have the same values for TELTUID and SELTUID .

Now all the entries in the LAYTP as ‘AGG’ have all the details of the filters.

Step 3 : Select all the entries with LAYTP AS ‘AGG’ from table RSZELTXREF  and lookup on the Table RSZRANGE WHERE ELTUID = TELTUID, to obtain the technical details of the Filters as depicted in the below screenshot

/wp-content/uploads/2015/11/2_823518.png

Default Values Details


The filter details of the query are maintained in the LAYTP  ‘SHT’. When we cross refer these values in the RSZELTREF table, the TELTUIDs
with LAYTP = “AGG” is displayed which are the default values. Now this would be bit clearer from the below mentioned screenshots .

The below screenshot is the RSZELTXREF Details for the Query

/wp-content/uploads/2015/11/4_823523.png

Select the TELTUID Of the record which has LAYTP = ‘SHT’. Use the TELTUID as SELTUID to get the list of all the cross references .

The screenshot shows all the records with LAYTP = “AGG”.

/wp-content/uploads/2015/11/5_823560.png

Please note the fields marked in RED show the logic of SELTUID = TELTUID.

FOR Default Value Selection

We select the TELTUID from the RSZELTXREF and lookup in the RSZRANGE Table :-

/wp-content/uploads/2015/11/6_835907.png

Thus we get all the restrictions of the default values.
However we still need to find default values which have no restrictions or have hierarchy restrictions.

Technical Info object Names of all the Default values

Next step is to get the text of the Default values and we make lookup on the table RSZELTDIR with ELTUID = TELTUID (from RSZELTXREF table) .

For Selecting the Info-object Name , the Default Hint field gives the name of the Info Object used for Selection

/wp-content/uploads/2015/11/7_835908.png

For Selecting the Hierarchy .

We also make a Lookup on the table RSZELTPROP, ELTUID =  TELTUID (from RSZELTXREF table) to obtain the list of all the records with Hierarchies included as mentioned in the below ,
screenshot

/wp-content/uploads/2015/11/8_835909.png

Please note the fields marked in Red follow the logic of ELTUID = TELTUID .

Row /Column Selection Details

This is a bit more complex work. Let’s take a look at the screenshots to demystify the Rows/Columns.

Please note that the approach for ROW/Column level details is same. In this example , I have taken Column as base.The similar approach can
be used to derive ROWS details  as well

The below screenshot is the RSZELTXREF Details for the Query


/wp-content/uploads/2015/11/9_835920.png

Now Select the all TELTUID Of the record which has LAYTP = ‘SHT’ and cross lookup on the RSZELXREF table with SELTUID = TELTUID.

Now we have details of all the LAYTD =’SHT’ and new TELTUIDS.Now Select values with TELUID for the LAYTP as ‘ROW’ /’Column’ as mentioned in the below example.

/wp-content/uploads/2015/11/3_823519.png

Now again lookup on the table RSZELTXREF to obtain the list of all the related items of LAYTP = ‘Row/Column’. Please remember to set SELTUID = TELTUID.

The below screenshot has all the related references of the LAYTP =’COL’ .If you note the TELTUID of the above screenshot is similar to the
SLETUID of the below screenshot

/wp-content/uploads/2015/11/1_835893.png

I have trimmed down this screenshot as this usually would be huge as it contains all the cross references of your row’s/column’s.

Now we have a list of all the references and now we need to find the details of selections, filters, text and simple info objects with no selections. Please note in the below examples we look up with the tables with the condition that ELTUID (in the new tables) = TELTUID (all values obtained from the table  RSZELTXREF).

 

To Obtain the Details of Selections

Select all the entries and lookup in the table RSZRANGE To obtain the restrictions :-

/wp-content/uploads/2015/11/4_823523.png

To Obtain Details of Formulae

Lookup in the table RSZCALC

/wp-content/uploads/2015/11/5_823560.png


To obtain the text refer to the table RSZELTXT


/wp-content/uploads/2015/11/6_835907.png

Infoobject details at row/column level

In case if just an Info object is used in the row/column level this info object details can be looked up in the RSZELTDIR Table.

/wp-content/uploads/2015/11/7_835908.png

Exceptions and Conditions

By now we are clear of obtaining the basic details in the RSZELTXREF table, so I have skipped the first step of obtaining the list of all the related objects in the query .

Select the LYTP ‘NIL’ from the table RSZELTXREF to select the details of the Conditions and Exceptions.

Now cross refer to the table RSZRANGE to select the details of Conditions and exceptions as mentioned below in the screenshot.

/wp-content/uploads/2015/11/8_835909.png

Now follow the usual process of  select ELTUID (RSZRANGE) = TELUID (RSZELTXREF)

/wp-content/uploads/2015/11/12_835942.png

Please note that in case of Conditions, in the field IOBJN in RSZRANGE  the value is 1CONDITION and in case of Exception the value in the field IOBJNM the value is 1EXCEPTION marked in red in the above screenshot.

Cell References Details

Select the LYTP ‘SHT’ from the table RSZELTXREF to select the details of Cell References. Now cross refer to the Table RSZELTXREF where SELTUID =
TELTUID where LYTP = ‘SHT’. Now Select the TELTUID of records with LAYTP = ‘CELL’.

/wp-content/uploads/2015/11/13_835961.png

For cell references of type formulae we lookup on the table RSZCALC with the selection ELTUID = TELTUID.


/wp-content/uploads/2015/11/14_835962.png

For cell references of type selection we look up on the table RSZRANGE with the selection ELTUID = TELTUID.

And for just cell level references we look up on the table RSZELTPROP

/wp-content/uploads/2015/11/15_835963.png

Understanding Table RSZRANGE

The RSZRANGE table has all the details related to the selection in a Query. Now let’s look at the structure of the RSZRANGE table.

Now let’s discuss some of the important fields

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 as mentioned in the
  below screenshot

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

SHIFTHIGH

n case of an offset usage , the flag is used to set the offset values
  i.e(+,- the values)

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.

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply