Enhancing Data Element Search in ABAP with AMDB Fuzzy Search
As ABAP developers, we often find ourselves in the situation of searching for an existing data element when working on new programs, tables, or structures. The standard search help in SE11 is somewhat limited in terms of the data it provides. For example, it doesn’t show the lower case flag or offer much flexibility in searching for various texts associated with a data element. The UI of the search results also feels outdated and lacks the features developers would expect in modern environments.
In this blog post, I will walk you through a solution I implemented to address these limitations, using ABAP and AMDB (ABAP Managed Database Procedures) to build a more powerful and flexible data element search tool.
🔍 The Problem with SE11 Search
The search functionality in SE11 is useful for finding data elements based on some basic criteria, such as the data type, length, and associated texts. However, it doesn’t display the lower case flag in the search results, which can be essential for certain use cases. Moreover, the search is case-sensitive, and there is no “fuzziness” to account for minor variations in text search.
Additionally, the UI for the search result is quite dated, making it hard to work with efficiently, especially when searching through a large number of data elements.
🛠️ The Solution: A Custom Report
To solve these issues, I built a custom ABAP report that queries the DDIC tables holding data element information. The solution leverages a DDIC View to fetch relevant information, including the lower case flag and various descriptive texts for each data element.
Here is the core part of the custom DDL View (ZBC_DTEL_SEARCH) that pulls data from the relevant tables:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
@AbapCatalog.sqlViewName: 'ZBC_DTEL_SEARCHV'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Dataelement Search'
define view ZBC_DTEL_SEARCH as select from dd04l
left outer join dd04t
on dd04t.rollname = dd04l.rollname
and dd04t.ddlanguage = $session.system_language
{
key dd04l.rollname,
dd04l.domname,
dd04l.datatype,
dd04l.leng,
dd04l.decimals,
dd04l.lowercase,
dd04t.ddtext,
dd04t.reptext,
dd04t.scrtext_s,
dd04t.scrtext_m,
dd04t.scrtext_l
}
This DDL View combines the DD04L and DD04T tables to get essential information like the data type, length, decimals, and the descriptive texts associated with a data element.
📋 The Select Query for Basic Search
Once we have the view, we can query it for the necessary results based on user input. Here is an example of the select query:
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT *
FROM zbc_dtel_searchv
INTO TABLE @lt_result
WHERE rollname IN @s_rolnam
AND datatype IN @s_type
AND leng IN @s_leng
AND decimals IN @s_deci
AND ( lowercase IN @s_text
OR ddtext IN @s_text
OR reptext IN @s_text
OR scrtext_s IN @s_text
OR scrtext_m IN @s_text
OR scrtext_l IN @s_text ).
While this works for simple cases, there are still a couple of limitations. One of the major problems is the case sensitivity in the search. Additionally, the search lacks fuzziness, making it hard to find matches that are similar but not exact.
🤖 Implementing Fuzzy Search with AMDB
Fortunately, SAP HANA provides us with the ability to implement a fuzzy search using AMDB (ABAP Managed Database Procedures). This allows us to run more flexible queries directly in the database and improve the search functionality significantly.
Here is an example of how we can implement the fuzzy search using an AMDP method:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
METHOD search_fuzzy BY DATABASE PROCEDURE
FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING zbc_dtel_searchv.
rt_result = SELECT * FROM zbc_dtel_searchv
WHERE CONTAINS (ddtext, :iv_text, FUZZY( 0.5 ))
OR CONTAINS (reptext, :iv_text, FUZZY( 0.9 ))
OR CONTAINS (scrtext_s, :iv_text, FUZZY( 0.9 ))
OR CONTAINS (scrtext_m, :iv_text, FUZZY( 0.9 ))
OR CONTAINS (scrtext_l, :iv_text, FUZZY( 0.9 ))
ORDER BY score() DESC;
ENDMETHOD.
In this method, I used the CONTAINS function with a fuzzy match score to allow for more flexible text matching. The FUZZY function is set with different thresholds depending on the text field’s importance, giving more flexibility in matching.
This method allows for a much better search experience by allowing partial matches and handling typos or variations in the search terms.
🔄 Integrating Fuzzy Search into the Report
Once the fuzzy search is implemented, we integrate it into the main report as follows:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
zcl_dtel_search_helper=>search_fuzzy(
EXPORTING
iv_text = p_text
IMPORTING
rt_result = lt_result
).
LOOP AT lt_result ASSIGNING FIELD-SYMBOL(<row>)
WHERE rollname IN s_rolnam
AND datatype IN s_type
AND leng IN s_leng
AND decimals IN s_deci
AND lowercase IN s_lower.
APPEND <row> TO gt_outtab.
ENDLOOP.
This code calls the search_fuzzy method, passes the user input as the search text (p_text), and receives the results in lt_result. The loop filters the results based on other criteria, such as data type, length, decimals, and lowercase flag.
🖥️ Displaying Results in an ALV Grid
To make the results easy to work with, we display them in an ALV Grid (ABAP List Viewer), which provides a user-friendly and interactive interface for the developers. Here’s a snapshot of the result displayed in the ALV Grid:
The ALV grid offers sorting, filtering, and additional features that make it much easier to work with the search results.
🧠 Final Thoughts
By using a combination of ABAP, HANA features like AMDB, and an ALV grid for displaying the results, we were able to significantly enhance the data element search functionality. The fuzzy search improves the flexibility of the search, making it possible to find relevant data elements even if the search term isn’t an exact match.
This solution not only improves the user experience but also saves valuable time for developers who often need to find existing data elements in the system. If you face similar challenges in your own projects, implementing a custom search solution like this one can help you streamline your development process and make working with SAP even more efficient.
If you have any questions or need further details about implementing a similar solution, feel free to reach out or leave a comment below!