SAP Note

1780163 - Report optimization: Access to further tables

Component: SD-SLS-GF-RE (Sales and Distribution > Sales > Basic Functions > Reporting), Version: 7, Released
On: 27.01.2025

Symptom
The optimized reports use a predefined set of database tables for the selection (VBAK and
VBAP, among others).

However, you want to add additional tables to this list. This may be delivered tables (for
example, MARA) as well as customer-specific tables (for example, in the Z* namespace).

Other Terms
VA05, VA15, VA25, VA45, SDO1
SD_SALES_DOCUMENT_VA15
SD_SALES_DOCUMENT_VA25
SD_SALES_DOCUMENT_VA45
SD_SALES_DOCUMENT_VIEW
SD_SALES_DOCUMENT_SDO1

Reason and Prerequisites

The optimized reports are available as of SAP Enhancement Package 6 for SAP ERP 6.0, version
for SAP HANA. To be able to use the optimized reports, you must activate the business
function SD for optimized reports (LOG_SD_REPORT_OPT; transaction SFW5) and also activate
the optimized version for each of the transactions separately in Sales and Distribution
Customizing (Sales -> Lists -> Optimized Lists). The optimized reports are used by default
in SAP S/4HANA and cannot be reverted to the previous version.

Solution
This consulting note describes how you can adjust the optimized reports according to your
requirements. For this, this SAP Note describes several scenarios as examples.

Note that this may have a negative impact on system performance.

New fields (columns) in the output list
For this, the enhancement concept provides the following Business Add-Ins (contained in the
enhancement spot ES_SDOC_WRAPPER; see transactions SE18, SE19, and SE20):
1. BADI_SDOC_WRAPPER
a) Method ADAPT_RESULT_COMP
The system calls this method before it displays the selection screen to provide
additional result fields.
You can use the parameter IV_APPLICATION_ID to find out the
transaction from which the BAdI was called. The used constants are displayed by the
interface IF_SDOC_SELECT (attribute CO_APPLICATION_ID).
You can add the fields that you require to the changing table
CT_RESULT_COMP (see the examples below). The sequence of the entries determines the
sequence of the displayed columns.
 For the table CT_RESULT_COMP_EXTEND, this is an optional interface
parameter. This enables the maintenance of currency and quantity reference fields (for an
example, see SAP Note 2761154). Note that both tables - CT_RESULT_COMP and
CT_RESULT_COMP_EXTEND - must be maintained.
The changing table CT_ADDITIONAL_TABLE_METADATA is required for
setting up dynamic JOIN conditions, which must be set up to enable the selection of
additional database tables (see the examples below). The sequence of the entries is
irrelevant.
b) Method POST_PROCESSING
This method is called for each line of the output list to calculate additional
fields or to remove the line from the output list.
The importing table IT_FIELD_SEL contains the field names of the
output list, which are determined using database selections.
The importing table IT_FIELD_CAL contains the field names of the
output list, which must be calculated in this method.
The changing field CV_IGNORE_RESULT can be set to ABAP_TRUE if you
do not want the current line to be displayed in the output list (for example, due to an
implemented authorization check).
The changing structure CS_RESULT contains the current line of the
output list, whose fields can be manipulated here. Note that the typing of this structure
can be set only at runtime because it depends on the selection of the chosen columns (layout
variant).
2. BADI_SDOC_WRAPPER_MASS
a) Method POST_PROCESSING_MASS
This method is called once for all lines of the output list to calculate additional
fields or to remove lines from the output list.
The importing table IT_FIELD_SEL contains the field names of the
output list, which are determined using database selections.
The importing table IT_FIELD_CAL contains the field names of the
output list, which must be calculated in this method.
The changing table CT_RESULT contains all lines of the output list,
whose fields can be manipulated here. Note that the typing of this table can be set only at
runtime because it depends on the selection of the chosen columns (layout variant).

In general, the following applies: If you use the BAdIs to define additional output fields,
they are initially not contained in the output list. However, you can use the button for
changing the layout to add them to your display variant.

The following use cases are described in detail below:

1) Field without direct database reference (calculated field)
2) Field from a database table selected by default
3) Field from a database table not selected by default
4) as in 3) but via several steps
5) Using table aliases
6) Postprocessing in mass mode
7) Customer-defined column headers
1) Field without direct database reference (calculated field)
You want to calculate an additional field from the fields that are already available by
default. This means that this field is not directly selected from the database. For example:
You want to add a field with the internal name "MYCALCFIELD", which uses the data element
WEEK_DAY. To do this, you implement the method ADAPT_RESULT_COMP as follows:

data:
lt_fieldname type tdt_fieldname.
 append 'ERDAT' to lt_fieldname.

insert value #( field = 'WEEK_DAY'

name = 'MYCALCFIELD'
text = 'My Weekday'
required_comp = lt_fieldname )
into table ct_result_comp.

Note that the entry date (ERDAT) is used for the calculation of the weekday. Therefore, this
must be specified as a required component (REQUIRED_COMP).

The result is an additional column in the output list. This column has the heading "My
Weekday". (If the field TEXT is not explicitly filled in ADAPT_RESULT_COMP, the system uses
the heading from the data element WEEK_DAY.) This field is filled in the method
POST_PROCESSING:

field-symbols:
<lv_erdat> type datum,
<lv_my_calc_field> type char10.

try.
assign component 'MYCALCFIELD' of structure cs_result
to <lv_my_calc_field>.
assign component 'ERDAT' of structure cs_result
to <lv_erdat>.
if <lv_erdat> is assigned and
<lv_my_calc_field> is assigned and
<lv_erdat> is not initial.
* Calculate weekday of creation date
call function 'DATE_TO_DAY'
exporting
date = <lv_erdat>
importing
weekday = <lv_my_calc_field>.
endif.
catch cx_root.
endtry.
2) Field from a database table selected by default

In the optimized reports, the following database tables are already selected by default:
ADRC ("Addresses (Business Address Services)")
VBAK ("Sales Document: Header Data")
VBAP ("Sales Document: Item Data")
VEDA ("Contract Data")
VBKD ("Sales Document: Business Data")
VBPA ("Sales Document: Partner")
VBEP ("Sales Document: Schedule Line Data")
These table names are also contained in the interface IF_SDOC_SELECT (attribute
CO_TABLENAME).

You now want to display an additional field from one of these database tables. This field
can be a standard field as well as a customer-specific field (append structure).

For example, if you want to display the "Material Group" field (MATKL) from the database
table VBAP, you must implement the method ADAPT_RESULT_COMP as follows:

* Display VBAP-MATKL (material group)

insert value #( table = 'VBAP'
 field = 'MATKL'
name = 'VBAP_MATKL' ) into table ct_result_comp.

The method POST_PROCESSING must not be implemented in this case.

In another example, you can add a column for the customer expected price or the customer
value (condition category J, condition types EDI1 and EDI2 in the standard system) to the
display. To do this, you can use the following source code:

* Display of customer expected price

insert value #( target_field = 'KNUMV'
source_table = 'VBAK'
source_field = 'KNUMV' ) into table lt_join_metadata.
insert value #( target_field = 'KPOSN'
source_table = 'VBAP'
source_field = 'POSNR' ) into table lt_join_metadata.
insert value #( target_field = 'KNTYP'
constant_value = 'J' ) into table lt_join_metadata.

insert value #( target_table = 'KONV'

join_metadata = lt_join_metadata ) into table
ct_additional_table_metadata.

insert value #( table = 'KONV'

field = 'KBETR'
name = 'KONV_KBETR' ) into table ct_result_comp.

Addresses must be handled in a special way because a partner function is always required for
this. From a technical point of view, the field ADRC-ADDRNUMBER must be selected from the
field VBPA-ADRNR (refer to the attached graphic). For the partner function "SP" (sold-to
party), the two table aliases VBPA_AG and ADRC_AG exist. They can be uniquely determined
from the document header and used for user-defined fields; for example, to display the city
of the sold-to party:

* Display ADRC-CITY1 (city of sold-to-party)

insert value #( table = 'ADRC'
field = 'CITY1'
table_alias = 'ADRC_AG'
name = 'ADRC_CITY1' ) into table ct_result_comp.

In addition, the table alias VBPA_ZM exists, which is filled from the order header and the
partner function specified on the selection screen.
3) Field from a database table not selected by default
Based on example 2), you also want to display the valuation class of the material group
(BKLAS) from the database table T023. To do this, implement the method ADAPT_RESULT_COMP as
follows:

data:
lt_join_metadata type if_sdoc_select=>tct_join_metadata.

* Join T023-MATKL to VBAP-MATKL

insert value #( target_field = 'MATKL'
source_table = 'VBAP'
source_field = 'MATKL' ) into table lt_join_metadata.

insert value #( target_table = 'T023'

join_metadata = lt_join_metadata ) into table
ct_additional_table_metadata.

* Display VBAP-MATKL (material group)

insert value #( table = 'VBAP'
 field = 'MATKL'
name = 'VBAP_MATKL' ) into table ct_result_comp.

* Display T023-BKLAS (valuation class)

insert value #( table = 'T023'
field = 'BKLAS'
name = 'T023_BKLAS' ) into table ct_result_comp.

Since you now want to read a field from the table T023, meta data for accessing the table
T023 is required. This is achieved by linking the field T023-MATKL to the field VBAP-MATKL
using a JOIN condition.

For client-specific tables, the system automatically generates a JOIN condition on SY-MANDT.

The method POST_PROCESSING must not be implemented in this case.

4) as in 3) but via several steps
You can also use dynamic JOIN conditions to link any number of database tables that are not
selected by default. For example, if you want the system to display the industry sector and
its (language-dependent) description from the material master, you must implement the
following source code in ADAPT_RESULT_COMP:

data:
lt_join_metadata type if_sdoc_select=>tct_join_metadata.

* Join MARA-MATNR to VBAP-MATNR

insert value #( target_field = 'MATNR'
source_table = 'VBAP'
source_field = 'MATNR' ) into table lt_join_metadata.

insert value #( target_table = 'MARA'

join_metadata = lt_join_metadata ) into table
ct_additional_table_metadata.

* Display MARA-MBRSH (industry sector)

insert value #( table = 'MARA'
field = 'MBRSH'
name = 'MARA_MBRSH' ) into table ct_result_comp.

* Join T137T-MBRSH to MARA-MBRSH

clear lt_join_metadata.
insert value #( target_field = 'MBRSH'
source_table = 'MARA'
source_field = 'MBRSH' ) into table lt_join_metadata.

* Join T137T-SPRAS to SY-LANGU

insert value #( target_field = 'SPRAS'
constant_value = sy-langu ) into table lt_join_metadata.

insert value #( target_table = 'T137T'

join_metadata = lt_join_metadata ) into table
ct_additional_table_metadata.

* Display T137T-MBBEZ (industry sector text)

insert value #( table = 'T137T'
field = 'MBBEZ'
name = 'T137T_MBBEZ' ) into table ct_result_comp.

The JOIN condition can also be set up with a constant value (for example, of the logon
language) instead of another database field.

The method POST_PROCESSING must not be implemented in this case.

5) Using table aliases
You might want to include the same database table using several different JOIN conditions.
For example, the material master (table MARA) could be accessed from the material number
(VBAP-MATNR) as well as from the pricing reference material (VBAP-PMATN). For this, you can
define aliases to distinguish the different access paths:

data:
lt_join_metadata type if_sdoc_select=>tct_join_metadata.

* Display MARA-BRGEW (gross weight) of material number

insert value #( table = 'MARA'
field = 'BRGEW'
table_alias = 'MARA_VIA_MATNR'
name = 'MARA_VIA_MATNR_BRGEW' ) into table ct_result_comp.

* Join MARA-MATNR to VBAP-MATNR

insert value #( target_field = 'MATNR'
source_table = 'VBAP'
source_field = 'MATNR' ) into table lt_join_metadata.

insert value #( target_table = 'MARA'

table_alias = 'MARA_VIA_MATNR'
join_metadata = lt_join_metadata ) into table
ct_additional_table_metadata.

* Display MARA-BRGEW (gross weight) of pricing reference material

insert value #( table = 'MARA'
field = 'BRGEW'
table_alias = 'MARA_VIA_PMATN'
name = 'MARA_VIA_PMATN_BRGEW' ) into table ct_result_comp.

* Join MARA-MATNR to VBAP-PMATN

clear lt_join_metadata.
insert value #( target_field = 'MATNR'
source_table = 'VBAP'
source_field = 'PMATN' ) into table lt_join_metadata.

insert value #( target_table = 'MARA'

table_alias = 'MARA_VIA_PMATN'
join_metadata = lt_join_metadata ) into table
ct_additional_table_metadata.

In principle, specifying the alias is optional because it is used only in rare cases.
However, if it is used, it must be set consistently (in addition to the physical table name)
in CT_RESULT_COMP and in LT_JOIN_METADATA.

The method POST_PROCESSING must not be implemented in this case.

6) Postprocessing in mass mode
To optimize postprocessing performance, you can implement the method POST_PROCESSING_MASS
instead of the method POST_PROCESSING. The method POST_PROCESSING_MASS is called exactly
once and can manipulate the entire output list during this call.
This may be an advantage if certain preparations (such as authorization checks or other data
retrievals) that are needed for all lines are required before the iterative processing of
the output list.
7) Customer-defined column headers
You can use the field TEXT in the table CT_RESULT_COMP of the method ADAPT_RESULT_COMP to
specify your own text as a column header. If required, you can do this independently of the
logon language (SY-LANGU).
This is, for example, necessary if user-defined database tables are accessed whose fields
reference predefined types (and not data elements) so that the standard output list (ALV
Grid control) cannot determine any texts. In addition, it is useful to specify your own
texts when you use aliases to ensure that the user can distinguish the different access
paths.
New criteria on the selection screen
Enhancement points are available that you can use to create your own selection criteria.
They are contained in the following enhancement spots:
ES_SD_SALES_DOCUMENT_VA15
ES_SD_SALES_DOCUMENT_VA25
ES_SD_SALES_DOCUMENT_VA45
ES_SD_SALES_DOCUMENT_VIEW (for VA05)

Note that no enhancement points are available for the new transaction SDO1. If required, use
transaction VA05 together with its enhancement points.

Each of these enhancement spots contains the following two static enhancement points:
EXT1
Here, you can define your own selection fields. If you want to, for example,
provide a parameter for the user name, implement the following source code:
PARAMETERS: PUNAME type XUBNAME default SY-UNAME.
For a select option, you can implement the following lines:
DATA: GV_USERNAME type XUBNAME.
SELECT-OPTIONS: SUNAME for GV_USERNAME default SY-UNAME.
EXT2
At this point, transfer the user entries to an internal structure for further
processing. For this, two macro definitions are available. Proceed as follows to transfer a
parameter, for example, to filter according to the field USERNAME of the database table
ZMY_TABLE:
transfer_parameter 'ZMY_TABLE' 'USERNAME' PUNAME.
For a select option, implement the following source code:
transfer_select_option 'ZMY_TABLE' 'USERNAME' SUNAME.

Note that, at runtime, dynamic JOIN conditions must be set up between the database tables
selected by default (see above) and the database table referenced by you (in the example:
ZMY_TABLE). For this, you must implement the internal table CT_ADDITIONAL_TABLE_METADATA
from the method ADAPT_RESULT_COMP of the BAdI mentioned above (see the above examples).
Additional pushbuttons in the output list

If you require further pushbuttons in the output list, you can implement the BAdI
BADI_SDOC_VIEW_CUSTOM_FUNC. This is contained in the enhancement spot ES_SDOC_VIEW and
contains the following methods:
DEFINE_CUSTOM_FUNCTION
This method is called when the output list is set up to define additional
pushbuttons.
Refer to the above description for the parameter IV_APPLICATION_ID.
You can use the changing table CT_CUSTOM_FUNCTION to add any number
of additional pushbuttons. To do this, specify a (technical) name and a text for each
function. The text can be filled language-dependently, for example, using SY-LANGU. The name
corresponds to FCODE in the screen programming and it must be unique within
CT_CUSTOM_FUNCTION.
The sequence of the lines in CT_CUSTOM_FUNCTION corresponds to the
sequence of the buttons in the output list.
DO_CUSTOM_FUNCTION
 This method is called when the user chooses a function that has previously been
defined.
IV_NAME contains the technical name of the function that is triggered. In the
implementation, a check for IV_NAME should always take place (even if only one additional
function has been defined) because this source code is also processed for functions that are
offered by IBUs or SAP partners (using BADI_SDOC_VIEW_CUSTOM_FUNC_INT).
The importing table IT_RESULT contains all lines of the output list. The
importing table IT_SELECTED_ROWS contains only the selected lines (the entries contain the
line numbers referring to IT_RESULT).
You can use the changing table CT_MESSAGE to add T100 messages to the message
log.

The additional functions are displayed in the output list with up to six buttons. If more
than six functions are defined (the total number of defined functions in
BADI_SDOC_VIEW_CUSTOM_FUNC and BADI_SDOC_VIEW_CUSTOM_FUNC_INT), the system displays a dialog
box when you choose the sixth button. In this dialog box, the remaining functions are
available for selection.

Software Components
Software Component From To

SAP_APPL 616 616+

S4CORE 100 100+

This document refers to

SAP Note/KBA Component Title

3367135 SD-SLS-GF-RE

1793893 SD-SLS-GF-RE New BAdI for customer functions

1790841 SD-SLS-GF-RE Extending the selection fields

1782650 SD-SLS-GF-RE BAdI for mass postprocessing

1782126 SD-SLS-GF-RE Selection of additional tables in optimized reports

This document is referenced by

SAP Note/KBA Component Title

3296415 Open quantity and Net value in VA05 for partially processed sales documents

3105252 SD-SLS-GF-RE SDV1 & SDV2 transaction changed on S/4HANA

1600599 SD-SLS-GF-RE Creating a selection variant in transaction VA05 in ECC

3367135 SD-SLS-GF-RE SD Report Optimization: Access to further tables - Addendum

3320658 SD-SLS-GF-RE VA05 CX_SDOC_REPORTING when joining to custom tables or fields with special characters

2322762 SD-SLS-GF-RE SQL Exception when adding custom columns to report layout for HANA database in VA05

2192630 SD-SLS-GF-RE Incorrect SQL statement implementing report optimization described in note 1780163

2160169 SD-SLS-GF-RE SQL Exception when adding custom columns to report layout by means of SDOC classes

1793893 SD-SLS-GF-RE New BAdI for customer functions

1790841 SD-SLS-GF-RE Extending the selection fields

1782126 SD-SLS-GF-RE Selection of additional tables in optimized reports

1782650 SD-SLS-GF-RE BAdI for mass postprocessing

 Attachments
File Name File Size Mime Type

note1780163_db_tables.pdf 91 application/pdf