Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
JWiseman
Active Contributor
0 Kudos

Scenario:

 

You have a report that has 2 multiple value parameters. You want the end user to be able to enter different values that will return similar customers using a parameter called CustomerLike. You also want the end user to eliminate certain customers using a parameter called CustomerNotLike.

 

As an example, the end user may enter "am" and "an" in a CustomerLike parameter which is supposed to return all customers that contain the string "am" and all customers that contain the string "an".

 

A record selection filter of {table.field} LIKE {?MyParameter} doesn't work as there are no wildcards added to the prompt values. You end up with a WHERE clause similar to

WHERE  (`table`.`field` LIKE 'am' OR `table`.`field` LIKE 'an')

 

You also want to ensure that this record selection is done at the database level for performance reasons.

 

Example solution:

 

1) Create 2 New Parameters, one named CustomerLike and the other named CustomerNotLike. These 2 new parameters should allow Multiple Values and also be Optional Prompts.

 

2) Create a new formula called CustomerLike that will be used to manipulate all parameter entries for the CustomerLike parameter. The new formula will then be consumed by the record selection filter.

The syntax for this formula will be similar to the syntax below.

 

numbervar counter;
stringvar likevalues;
if hasvalue({?CustomerLike}) then
(
for counter:= 1 to count({?CustomerLike}) step 1 do
(likevalues:= likevalues +  '*' + {?CustomerLike}[counter] + '*' + '|');
numbervar lengthlikevalues:= length(likevalues);
if lengthlikevalues > 1 then likevalues:= likevalues[1 to length(likevalues) - 1];
)
else
likevalues:= '*' // if the end user does not select a value, then this line ensures that LIKE does not affect the filter
;

likevalues

 

3) Create a new formula called CustomerNotLike that will be used to manipulate all parameter entries for the CustomerNotLike parameter. The new formula will then be consumed by the record selection filter.

The syntax for this formula will be similar to the syntax below.

 

numbervar counter;
stringvar notlikevalues;
if hasvalue({?CustomerNotLike}) then
(
for counter:= 1 to count({?CustomerNotLike}) step 1 do
(notlikevalues:= notlikevalues +  '*' + {?CustomerNotLike}[counter] + '*' + '|');
numbervar lengthnotlikevalues:= length(notlikevalues);
if lengthnotlikevalues > 1 then notlikevalues:= notlikevalues[1 to length(notlikevalues) - 1];
)
else
notlikevalues:= 'zzzzzz' // if the end user does not select a value, then this line ensures that NOT LIKE does not affect the filter
;

notlikevalues

 

4) go to the Report > Selection Formulas > Record and put in syntax similar to the syntax below.

 

{Customer.Customer Name} like split({@CustomerLike},'|')
and
not({Customer.Customer Name} like split({@CustomerNotLike},'|'))

 

Now the WHERE clause generated by the report will be similar to the syntax below.

WHERE  (`table`.`field` LIKE '*am*' OR `table`.`field` LIKE '*an*')

1 Comment