RUN SAP BETTER

New field "Database Connection"

​

• In the "Database Connection" field, you can display and select the available database connections using the input help.

• The accesses are then executed using the relevant database connection selected.

• If a database connection was selected, the input help for the "Table" field displays the tables and views that are available in SAP HANA.

• The number of rows is determined for the tables available. This is not possible for views. Note that the number of rows is also determined across all clients. The number of rows in your current system/client may therefore be lower than the number displayed.

• Note that transaction SE16H is able to check only the physical connection to the database. The table created in SAP HANA must correspond exactly to the definition in the SAP ERP Dictionary. If there are deviations (for example, different field names), transaction SE16H triggers a short dump upon access. Accesses to SAP HANA using transaction SE16H are useful only for tables that are replicated one-to-one. All other tables (for example, created in SAP HANA only) must be evaluated using the SAP HANA studio.

• If you leave the "Database Connection" field empty, SE16H works with all functions on the primary database of the ERP system. If this is not an SAP HANA database, extremely long runtimes can occur in this case.

​

New field 'Formula'

 

• You can define a formula on the rows of the output table in order to perform simple calculations.

​

New field 'Grouping Minimum'

 

• You can define the minimum number of hits for grouped fields. For example, if you enter 10, only rows into which at least 10 hits have been grouped are displayed.

​

New column "Group"

 

• In this column, you can enter any group or set that corresponds to the relevant field (selection using F4 help is possible).

• This is particularly useful if groups are used, for example, account groups, order groups, and so on.

• Note that SE16H cannot provide any application-specific partitioning of the selection criteria as is the case, for example, in application reporting. This means that if the group has a lot of entries, the corresponding select may terminate. In this case, you must reduce the size of the group.

 

New column "Total"

​

• If you select this field, all rows found that correspond to the grouping rule (see the new column "Group") are cumulated. As a result, you can generate aggregated views for a large amount of data that, for example, displays a value for each account for each period.

• A field overflow may occur if the total value is too high. This cannot be avoided because SE16H can only be used with the field definitions of the relevant table.
   

New column "Group"

 

• If you select this field, all unselected fields are aggregated and the total for all grouping fields selected in "Total" is output.

• The more fields you group, the longer the runtime.
   

New column "Sort"

 

• If you select this column, the system sorts the number of hits using this field.

• You can also do this using a default layout in the SAP List Viewer. However, the data is subsequently sorted, whereas the "Sort" column sorts the data directly from the database.

​

New column "Highest/Lowest"

​

• Together with the 'Sort' column, you can define whether to sort in ascending or descending order: In practice, this means that you can use this function to determine, for example, the "5 highest postings in a cost center". The maximum number of hits determines the number of results.

• Example 1:

  • You specify the table COEP, maximum number of hits 5, object number KSXXXXyyyyyyyyyy, WKGBTR sorting= X, highest/lowest = DES-Highest Value.

  • After executing this, the system returns the five largest postings in the object together with their frequency.

• Example 2:

  • Table COEP, maximum number of hits 5, grouping according to functional area and totaling using WKGBTR with sorting and "DES-Highest Value" results in the display of the five functional areas with the highest total costs.

• There are now various options for using these functions and also mixing them. The results are not always useful, even if the database can perform the access technically. You must think carefully what you want to achieve, and then define the relevant access. Always validate the results and check that they are correct.

• Transaction SE16H does not have an option to issue a message whether the access is "useful" or not.

New column "Sequence"

​

• If you use the functions mentioned above (sorting with highest/lowest), the sequence in which the fields are sorted is relevant. If you enter something in this column (values from 01 to 99), the fields are sorted in the sequence from 1 to 99. If you do not enter anything in this column, the fields are sorted according to their occurrence in the table.

• If you mix fields that have explicit values from 01 to 99 with fields that do not have a value in this column, the fields without values are added according to their occurrence after the fields with a value.

 

New column "Aggregate"

 

• In this column, you can choose between the aggregations "Maximum", "Minimum" and "Average". Depending on the data type, not all aggregation functions are available. You can either aggregate or group/total a field.

• New columns 'Having-Option' and 'Having-Value' (see SAP Note 2802623):

• You can define conditions for the total for fields that can be totaled up. For example, you can define that you only want to see results where the sum of all rows in a field is greater than 1,000. This option corresponds to the HAVING clause on an aggregate in SQL syntax.

 

New field "Outer Join Definition"

 

• This function corresponds to a basic view definition for tables that contain only additional information about the selected table (for example, to read texts).

• You cannot map any inner joins - that is, you cannot define restrictions that must be met for more than one table. The outer join definition is used only to read additional attributes from a user-selected database connection for the selected table. This means that, for example, postings can be read from a SAP HANA database and the texts for the resulting quantity can subsequently be read from the normal ERP database (Since SAP Note 2188410, selection is possible between an inner join and outer join).

• This approach makes sense only if a small number of hits is expected. If the number of hits is large, the runtime is considerably longer because no actual database join is executed, but separate accesses are generated for each database connection (Since SAP Note 2794526, a complete join statement is generated from all tables. This normally improves the runtime considerably).

• The authorization concept for the outer join definitions is identical to that in SE16N. In other words, for each table used, the system checks whether the user has display authorization (S_TABU_DIS or S_TABU_NAM). If the user does not have authorization, the table is removed from processing.

• The maintenance of the outer join definitions is checked using the new authorization object S_BRWS_CUS. This object contains the following three fields:

  • BRWS_KEY: You must assign the value 'JOIN' here.

  • BRWS_NAME: This is the name of the join definition to be maintained. For example, you can introduce a customer-specific naming convention and check certain namespaces here.

  • ACTVT: Assign the activities to be permitted.

• To maintain the outer join relationships, call transaction SE16H with the required primary table. Then choose the pushbutton beside the "Outer Join Definition" field.

• If no definition exists, enter a new name and choose "Create". If a definition exists, choose "Change" or "Display".

• SAP and customers have their own namespaces. The SAP namespace starts with a number (0-9), whereas the customer namespace contains all letters and '/'.

• The definitions are treated as workbench objects, which means that they are cross-client and user-independent.

• The name of a join definition is cross-client and must be unique for each table in the entire system. The unique name is linked with the described primary table one-to-one. If the primary tables are similar, the join definitions can also be copied to another primary table.

• Join definitions can be maintained in each system, but can also be transported if required.

• If you change an existing definition, you must call transaction SE16H again after saving your changes in order to delete the buffer. If you do not do this, the system may not display newly defined fields or may display them incorrectly. In some cases, a short dump may also occur.

• Note that there is no logical check of the definition. Any unwanted results may be due to an incorrect definition.

• On the first tab page, define the sequence in which the secondary tables are to be accessed. The sequence is relevant only if there are dependencies for the secondary tables.

  • Example: Table C requires the field XXX from table B. For this, table B must be determined first before the field XXX for C is available. Note that the field XXX is available for C only if you define it as an output field for table B.

  • In the "Database Connection" field, you can define the database from which the table is to be read. This means that, for example, you do not have to replicate master data texts in SAP HANA to be able to execute an actual join. Note that the input help determines the database connections of the current system only. If you want to transport the definition, make sure that you enter the database connection of the target system. The system checks whether the database connection exists (which consists of an information message only) so that you can also transport database connection names that do not exist in the source system.

  • You can choose "Output" to define which fields of the secondary table are added to the field catalog and are finally output.

• The relationship of the primary table to each secondary table may have the cardinality 1:1 or 1:N. This means that if several entries in the secondary table correspond to the conditions defined by you, these are aggregated in the determined field structure.

• If you double-click a secondary table (or choose "Maintain details"), you can maintain the technical relationships of the table. Here, you must define how the table is accessed.

• For the definition, you must consider which fields you import from the primary table. If, for example, you have not defined the controlling area as a grouping field, you cannot use it for the access to text tables.

• In this case, you must either select it as a grouping field for the primary table or determine it from the object number using the 'complex variable' described below.

• For each table field required, you can select the method as the field is filled. The following methods can be selected:

  • Reference:

    • Here, an equivalent field from one of the previous tables is assigned to a field. You must enter both the field and the source table.

  • Constant:

    • Here, you can enter a fixed value. Make sure that the entry is not formatted. In other words, you must ensure that the entry corresponds to the internal technical format (Note that you can enter only one constant value for each field).

  • Complex Variable:

    • This corresponds to an exit in which a function module that has specific input fields is called. The required field is then determined from this.

    • Example: In Controlling, the posting object is always posted as the object number. However, this is not transparent for the user. To convert an object number into a cost center name, for example, you can use the variable SE16N_OJ_GET_OBJECT_FROM_OBJNR.

    • If, for example, you want to determine from the line items in CO (table COEP) the texts for cost centers, orders (and so on) to which postings are made, enter the relevant required field (KOSTL for access to CSKT, AUFNR for access to the table AUFK) and the 'complex variable' SE16N_OJ_GET_OBJECT_FROM_OBJNR. The module then determines the required object from the relevant object number generically.

  • String Operation:

    • A string access may also make sense for simple character-type fields. This means that, for example, you can determine the order from an order object number by accessing the object number with an offset of 3 and a length of 12.

    • SAP Note 2795426: This option is not compatible with SAP Note 2795426 and causes the old sequential access logic to be executed.

    • SAP Note 2886898: Following the implementation of SAP Note 2886898, the substring function can be used together with the join logic.

  • System Field:

    • Here, you can generally use all fields of the structure SYST that are available at ABAP runtime. Useful applications are, for example, SY-LANGU as the language of the user for access to text tables.

• For each method, you can also change the selection option by choosing "Option". This is useful, for example, if you want to read the time-dependent text of an object. In this case, you can define, for example, the date of the object later than or equal to SY-DATLO. This ensures that the most recent text is read. By default, the condition '=' is always used.

​

Drilldown options in the results list

 

• By right-clicking in the results list, you can use a line for a drilldown.

  • Example:

    • You select the line items of CO (table COEP) and you group according to controlling area and total the controlling area currency. On the result screen, you then right-click a line and choose "Drilldown without value entry". The system displays a dialog box in which you can select additional grouping and totaling fields. You select the cost element, for example; for the selected controlling area line, the system then performs a drilldown in all existing cost elements. A new window opens during each drilldown, which means you can use both results next to each other.

    • When you choose the "Drilldown with value entry" function, a new screen for SE16H is called in which you can also enter additional restrictions. All values of the current list are filled in the relevant fields as default values.

    • When you choose the "Drilldown for complete list" function, a copy of the current selection is entered in a new screen for SE16H and you can change or enhance the selection.

    • You can also specify the drilldown for the "double-click" function.

    • To do this, call transaction SE16H and choose "Change Settings" (Ctrl+F12) on the initial screen. Under "Action with Double-Click in List", select the required function (if this is not available in the result, the system automatically switches to the standard (= detailed display)). Save your setting.

 

Display of SQL query as ABAP program

 

• Following the implementation of SAP Note 2880334, you can display the issued SQL query as a generated ABAP report. To do this, choose F9 in the results list. The generated ABAP can be copied to a customer-specific program and enhanced there.

• SAP Note 2886898 develops this option further.

Application examples

 

• You can map a consistency check between the SAP HANA database and the normal ERP database. (The table COEP is used as an example here.)

  • Enter the primary table COEP in transaction SE16H.

  • Switch to the outer join definition.

  • Also enter the secondary table COEP. Select the SAP HANA connection as the database connection. Select the "No. of Rows" checkbox and the output field 'WKGBTR'.

  • On the detail screen, define the following access:

  • Table Field Method Reference fld Reference table

  • KOKRS Reference KOKRS COEP

  • BELNR Reference BELNR COEP

  • Save the outer join definition and return to transaction SE16H.

  • Group the fields KOKRS and BELNR and total them using WKGBTR. If you then choose 'Execute', COEP is read from the ERP database and the

  • line items are read from the SAP HANA DB. On the result screen, the system then displays the total of the costs for each document from the ERP database and the SAP HANA database. In addition, the system also displays the number of rows for each document for the ERP database and the SAP HANA database.

  • For the consistency checks, note which table is the primary table.

  • The runtimes for consistency checks may be very long because the data volume must be read twice (from SAP HANA and also from the ERP database).

  • For this reason, we recommend that you execute rough checks first, for example, at controlling area level. Preferably with a restriction to a specific posting period (time stamp). Perform an advanced analysis only if deviations occur.

• Cross-controlling-area reporting (example in CO):

  • The postings in Controlling are all mapped with a technical object number. A cost center, order, WBS element, and so on, are converted into an object number that is used to execute the posting.

  • You can create an object number group using transaction GS01 (or using customer ABAP). You can add objects of different controlling areas to this group.

  • Note 1666911 delivers the report RKSE16H_CREATE_SET, which you can use to create the required object number groups. You can use cost center groups and order groups, and write the objects together into an object number group. You can also add groups of two controlling areas into an object number group (by executing twice one after the other).
    You then enter the group in transaction SE16H, for example, in the selection for the table COEP, and can compare several objects of different controlling areas with each other.