Wild card search on report definition where condition
We need to build a advanced search functionality with more than one search criteria. Facing difficulties in implementing wild card kind of where condition in report definition. See if you could share some ideas for below issue.
Consider a table like below,
Frank Silver Wright
ABC Corp Ltd
Frank Gold Wright
ABC Corp International
Frank Silver Cor
Search Section having two fields, like below,
Employee Name :
Company Name :
1.) When the search is done with following values,
Employee Name : Frank
Company Name : ABC
Result should contain all the 3 rows from the above table.
2.) When search is done with following values,
Employee Name: Frank Wright
Company Name: ABC Ltd
Result shall contain only Row 1.
The search should match all the words in search string against corresponding column value, even if the words in search string are not in same sequence as the column value.
When search is done with following values,
Employee Name: Frank Silver
Company Name: ABC
Result shall contain only Row 1 and Row 3.
We were trying to build a report definition with where condition type of "Contains" on each column, however this works for scenario 1 & scenario 3. But not for scenario 2.
Any pointers on how to achieve this with a report definition is much appreciated.
This report definition is called from a Data page.
Using Pega 7.2
***Edited by Moderator Marissa to update categories***
The only way I can think of to do this is to have multiple search values for each property. Your condition that the search values be in any order means you can't do this with a single wildcarded search value.
This may be easier to do if you define SQL function aliases to use in your filter criteria. You can use any SQL functions available in your database to manipulate the values and form the combinations of criteria you need.