How to… Search non-case sensitive

How to…
Search non-case
sensitive
BUSINESS INFORMATION WAREHOUSE
Applicable Releases: SAP BW 3.x
June 2003
SAP (SAP America, Inc. and SAP AG) assumes no responsibility for errors or omissions in these materials.
These materials are provided “as is” without a warranty of any kind, either express or implied, including but not limited to, the
implied warranties of merchantability, fitness for a particular purpose, or non-infringement.
SAP shall not be liable for damages of any kind including without limitation direct, special, indirect, or consequential damages
that may result from the use of these materials.
SAP does not warrant the accuracy or completeness of the information, text, graphics, links or other items contained within
these materials. SAP has no control over the information that you may access through the use of hot links contained in these
materials and does not endorse your use of third party web pages nor provide any warranty whatsoever relating to third party
web pages.
mySAP BI “How-To” papers are intended to simplify the product implementation. While specific product features and
procedures typically are explained in a practical business context, it is not implied that those features and procedures are the
only approach in solving a specific business problem using mySAP BI. Should you wish to receive additional information,
clarification or support, please refer to SAP Professional Services (Consulting/Remote Consulting
HOW TO … SEARCH NON-CASE SENSITIVE
1 Business Scenario
The search for characteristics (e.g. 0VENDOR) within the F4 (value) help is case sensitive,
i.e. the vendor ‘WACKER’ is only found if the search pattern contains only upper case
characters (other spellings do not lead to the desired result). If you have multiple source
systems, which provide the data to the BW system in different spelling, it is not possible to
perform a global analysis for the vendor ‘Wacker’.
There are two possible solutions:
1. Creation of an additional column in the text table of the characteristics
The text table is generated depending on the settings in the InfoObject maintenance.
Thereafter, it is possible to change the text table manually via the DDIC maintenance
(transaction SE11), and add additional columns.
The drawback of this solution is the missing ability to populate the additional columns
during the data loading process. In addition, the re-activation of the InfoObject does
lead to the deletion and recreation of the text table.
2. Using of a free text field in the InfoObject maintenance
If the base characteristic is not using all text fields, it is possible to use one of them
with a sufficient length. It should be noted that in general the shortest text field in the
text table is used when a search is restricted on the ‘Description’. Therefore, it is
necessary to create a control table, which specifies the field for the value help.
The advantage of this solution is that the population of the search text can occur
during the data loading process. In addition, changes to the InfoObject do not affect
the text table, and the modification can be transported to another system.
2003 SAP AMERICA, INC. AND SAP AG
1
HOW TO … SEARCH NON-CASE SENSITIVE
2 The Step By Step Solution
The screen shots are taken from a BW 3.0B, Support Package 13, system.
1. Create the control table
Use transaction SE11 to create the
control table ZRSUPPERCASE.
Allow the maintenance of the table.
Specify the technical settings of
the table and activate it with the
icon.
Structure of the table:
Field name
Key
Data
element
Description
CHABASNM
Yes
RSCHANM
Base characteristic
SOURCE_FIELDNM
No
RSFIELDNM
Source field in text table
FIELDNM
No
RSFIELDNM
Field name of search field in text table
2. Population of the table
a). To create the initial entry in
the control table use the menu
‘Utilities Æ Table Contents Æ
Create Entries’
b). Specify the base
characteristic, the field that
contains the case sensitive
form of the search field, and
the field that should hold the
upper case value of the search
field.
For the example used in this
document, we have specified
0VENDOR, TXTMD, and
TXTLG.
2003 SAP AMERICA, INC. AND SAP AG
2
HOW TO … SEARCH NON-CASE SENSITIVE
3. Add the additional text column
to text table via InfoObject
maintenance
Enter the InfoObject
mainteanance, switch on the
desired text field (for the example
used the long text is switched on).
Activate the InfoObject ( ) in order
to add the additional column to the
text table.
Switch on long
text.
4. Population of the upper case
search field
There are two options to populate
the search field
a). Define an ABAP/4 routine in
the transfer rules of the
InfoSource for the text upload,
which translates the source
text field to upper case, and
populates the additional text
field in the text table.
Note: Alternatively, you can
also use a Formula to
implement the translation to
upper case.
b). Use an ABAP/4 program to
tanslate all fields value of the
regular search field to upper
case and populate the
additional column in the text
table. For a sample program,
please refer to Appendix A.
Note: You need the form
routines of the program also for
the modification of the standard
value (F4) help.
2003 SAP AMERICA, INC. AND SAP AG
3
HOW TO … SEARCH NON-CASE SENSITIVE
5. ABAP/4 routine in the transfer
rules
The ABAP/4 routine only assigns
the source field to the result, and
translates the result to upper case.
Inserted ABAP/4 statements:
RESULT = TRAN_STRUCUTRETXTMD.
TRANSLATE RESULT TO UPPER
CASE.
6. Contents of the master data
after the population of the
additional field
The right hand side shows the
master data after the population of
the additional text field. In the
example used for this document
the additional field is the field ‘Long
Description’.
2003 SAP AMERICA, INC. AND SAP AG
4
HOW TO … SEARCH NON-CASE SENSITIVE
7. Value (F4) help before the
modification
Before you implement the
modification for the value (F4)
help, the search for a certain
vendor, e.g. ‘WACKER’ would only
provide one result record, i.e. the
vendor with the case-sensitive
spelling. In the example shown it
would derive the vendor
‘VENDOR1’.
8. Modify the standard search
process
In order to deviate the standard
value (F4) help to the new, added
field in the text table, you have to
perform a modification to the
function module
RSD_CHA_BUILD_QUERY. Use
transaction SE37 to modify the
function module.
Insert the following FORM routine
call as the last statement before
the ‘ENDFUNCTION’ statement:
perform where_upper_case_field in
program zrs_copy_to_upper_case
using
i_infocube
i_chanm
changing
e_sx_query-t_where.
Note: If you have named your
program different than described in
this document, you have to adapt
the program name for the FORM
routine call.
2003 SAP AMERICA, INC. AND SAP AG
5
HOW TO … SEARCH NON-CASE SENSITIVE
9. Value (F4) help after the
modification
After the modification, the value
help does provide the result shown
on the right hand side (for the
same search criteria ‘WACKER’)
3 Appendix A
Sample code for an ABAP/4 program to tanslate the field values for a specified InfoObject
text field to upper case and populate another text field. The control table ZRSUPPERCASE
defines the InfoObject and the text fields to be used. If you have created the control table
with a different name, you have to adapt the program accordingly. The program runs for one
or multiple InfoObjects, which can be specified on the selection screen.
*&---------------------------------------------------------------------*
*& Report ZRS_COPY_TO_UPPER_CASE
*
*&
*
*&---------------------------------------------------------------------*
*&
*
*&
*
*&---------------------------------------------------------------------*
REPORT
zrs_copy_to_upper_case.
TABLES: zrsuppercase.
TYPE-POOLS: rs, rsd, rsdm, rsdrs.
DATA: g_s_uppercase
g_t_uppercase
g_txttab
g_r_txttab
FIELD-SYMBOLS:
<g_s_txttab>
<g_source>
<g_target>
TYPE
TYPE
TYPE
TYPE
zrsuppercase,
zrsuppercase OCCURS 0,
rsd_tablnm,
REF TO data.
TYPE ANY,
TYPE c,
TYPE c.
SELECT-OPTIONS: chabas FOR zrsuppercase-chabasnm.
START-OF-SELECTION.
SELECT * FROM zrsuppercase INTO TABLE g_t_uppercase
WHERE chabasnm IN chabas.
LOOP AT g_t_uppercase INTO g_s_uppercase.
WRITE: / 'Charact.', g_s_uppercase-chabasnm.
UNASSIGN: <g_s_txttab>, <g_source>, <g_target>.
2003 SAP AMERICA, INC. AND SAP AG
6
HOW TO … SEARCH NON-CASE SENSITIVE
CALL FUNCTION 'RSD_TXTTAB_GET_FOR_CHA_BAS'
EXPORTING
i_chabasnm = g_s_uppercase-chabasnm
IMPORTING
e_txttab
= g_txttab
EXCEPTIONS
OTHERS
= 1.
IF sy-subrc <> 0.
WRITE: / 'Charact. not found'.
CONTINUE.
ENDIF.
CREATE DATA g_r_txttab TYPE (g_txttab).
ASSIGN g_r_txttab->* TO <g_s_txttab>.
IF <g_s_txttab> IS NOT ASSIGNED.
WRITE: / 'Text table not found'.
CONTINUE.
ENDIF.
ASSIGN COMPONENT g_s_uppercase-source_fieldnm
OF STRUCTURE <g_s_txttab>
TO <g_source>.
IF <g_source> IS NOT ASSIGNED.
WRITE: / 'Source field not found'.
CONTINUE.
ENDIF.
ASSIGN COMPONENT g_s_uppercase-fieldnm
OF STRUCTURE <g_s_txttab>
TO <g_target>.
IF <g_target> IS NOT ASSIGNED.
WRITE: / 'Target field not found'.
CONTINUE.
ENDIF.
SELECT * FROM (g_txttab) INTO <g_s_txttab>.
<g_target> = <g_source>.
PERFORM translate
CHANGING <g_target>.
UPDATE (g_txttab) FROM <g_s_txttab>.
ENDSELECT.
WRITE: / sy-dbcnt, 'records copied.'.
ENDLOOP.
*&--------------------------------------------------------------------*
*&
Form where_upper_case_field
*&--------------------------------------------------------------------*
FORM where_upper_case_field USING i_infocube TYPE rsd_infocube
i_chanm
TYPE rsd_chanm
CHANGING c_t_where TYPE rsdrs_t_where.
DATA: l_s_uppercase
l_s_cob_pro
l_subrc
FIELD-SYMBOLS:
<l_s_where>
LOOP AT c_t_where
TRANSPORTING NO
WHERE fieldnm =
OR fieldnm =
OR fieldnm =
EXIT.
ENDLOOP.
CHECK sy-subrc = 0.
TYPE zrsuppercase,
TYPE rsd_s_cob_pro,
TYPE sy-subrc.
TYPE rsdrs_s_where.
FIELDS
rsd_c_fieldnm_txtsh
rsd_c_fieldnm_txtmd
rsd_c_fieldnm_txtlg.
PERFORM cob_pro_get_always(saplrsdm_f4)
USING
i_infocube
i_chanm
2003 SAP AMERICA, INC. AND SAP AG
7
HOW TO … SEARCH NON-CASE SENSITIVE
CHANGING
l_s_cob_pro
l_subrc.
CHECK l_subrc = 0.
SELECT SINGLE * FROM zrsuppercase INTO l_s_uppercase
WHERE chabasnm = l_s_cob_pro-chabasnm.
CHECK sy-subrc = 0.
LOOP AT c_t_where ASSIGNING <l_s_where>
WHERE fieldnm = rsd_c_fieldnm_txtsh
OR fieldnm = rsd_c_fieldnm_txtmd
OR fieldnm = rsd_c_fieldnm_txtlg.
<l_s_where>-fieldnm = l_s_uppercase-fieldnm.
PERFORM translate CHANGING <l_s_where>-low.
PERFORM translate CHANGING <l_s_where>-high.
ENDLOOP.
ENDFORM.
"where_upper_case_field
*&--------------------------------------------------------------------*
*&
Form translate
*&--------------------------------------------------------------------*
FORM translate CHANGING c_value TYPE c.
TRANSLATE c_value TO UPPER CASE.
* do other replacements here...
ENDFORM.
"translate
2003 SAP AMERICA, INC. AND SAP AG
8