Crystall_WildcardSearches


1) If I have a simple crystal report based on one oracle table with static parameter (p_stock_no)
and i want to give user an option to use wild card searches.

* = multiple characters
? = Single character


Do i just simply add a selection criteria like this. I tried this and it seems to work. I have not tested it enough though.

{table.stock_no} like {?p_stock_no}

Will this will work for all cases
a) when user select single/multiple values from static list
b) when user enters a value only with wild card "?" or "*"
c) when user enters a combination criteria: values from static list and wild card search.



 I was not sure why i was told before to do something like this. I think this will always do wildcard searches so user cant do exact search if he wants.

{field1| like {?stock_no} + "*"

2) Is it better to have static list display stock number (stock no) only or both (stock no - description)?
which is a common practice.
sam15Asked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
It should for a single value with a wildcard.

Mine will also suport wildcards with a single value.

If {?Param}= '*' then                               ALL VALUES
   TRUE
Else If InStr({?Param}, '*') > 0 then    Single parameter value with a wildcard
   {Table.Field} LIKE {?Param}
Else                                                         Multiple values no wildcards
    {Table.Field} = {?Param}

mlmcc
0
 
mlmccCommented:
{table.stock_no} like {?p_stock_no}

That should work for all your cases.

2) - I think it really depends on the situation and the data.  If the stock number readily identifies the item then a description isn't really needed or if the users all know the stock numbers but I always like to see the description with it.

mlmcc
0
 
sam15Author Commented:
I think i do not even need to add "ALL" option to the list. If they enter * in the value it will so the whole list.

Also, if i want to sort the list i would need to create a separate view in the DB and import the list from there or import via text file?

It does not sound also you have any control over the colors or fonts that show up in the static list. is there?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mlmccCommented:
If you allow wildcards then the * should get all the records.

I don't know about the sorting issue.  WHen I add a static parameter and then append all values they get sorted by default.

If you are referring to the fonts/colors on the default parameter screen, then you are correct there is no way that I have found to make changes to it.

mlmcc
0
 
Kurt ReinhardtConnect With a Mentor Sr. Business Intelligence Consultant/ArchitectCommented:
"I think i do not even need to add "ALL" option to the list. If they enter * in the value it will so the whole list."

There are two problems with this approach:

1)  When you use LIKE, it only searches for literal values.  So, technically, if there are any records in your table that have a NULL value for the field you're querying then they won't be included in the results.  In the following sample report (Xtreme Sample Database),  which is parameterized against the PO Number field from the Orders table, you can see how using the "Convert NULL values to default" option dramatically affects the results of the report.  So, if you want to use * to select ALL records AND there's any possibility of there being NULLs in the field you're querying  then you need to set this option, at minimum.

2)  Using LIKE forces a full table scan and is inherently inefficient because the query has to read through every record in the table to see if the condition is met.  For that reason, it's much more efficient to use an explicit ALL value instead of *.  That has it's own set of issues, though.  If you use the syntax like this ({?Param = 'All' or {table.field} = {?Param}) then you'll run into the same limitation as demonstrated in the report above. To get around this, try the following syntax instead:

(
If
  {?Param} <> 'All'
Then
  {table.field} = {?Param}
Else If
  {?Param} = 'All'
Then
  True
)

Please note, you might be able to simplify the syntax - more recent versions of Crystal Reports are more generous with how they use If Thens in the record selection criteria, but that Syntax works in every version I've tested from at least 8

Please reference this presentation for further information: https://www.box.net/shared/ysqmn7eov5

~Kurt
0
 
mlmccCommented:
You could use a similar formula

If {?Param}= '*' then
   TRUE
Else If InStr({?Param}, '*') > 0 then
   {Table.Field} LIKE {?Param}
Else
    {Table.Field} = {?Param}

That won't work if you allow multiple values

mlmcc
0
 
sam15Author Commented:
yes, i do support multiple values.

I do not think this formula support wildcard characters if i am correct
(
If
  {?Param} <> 'All'
Then
  {table.field} = {?Param}
Else If
  {?Param} = 'All'
Then
  True
)


I need to test if the *LIKE* will always use a full scan. I think if you search
where stock_no like 'ABC*" it might use an index to find any entries that start with ABC. I do not see why it need to do a full scan.

Right now the report runs ina couple of seconds. I think anything under 5 seconds is considered good performance.
0
All Courses

From novice to tech pro — start learning today.