Skip to Content

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*’)

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Grace Yu
    Hi, Jamie, is that you? haha, good pic. Yes, we were friends many years ago but I lost your contact. Good to find you here. You cannot believe that I am with IT industry now. I work for Oracle in Beijing. Email me if you like: grace.yu@oracle.com. Hope to hear from you!
    Grace
    (0) 

Leave a Reply