Solved

Crystall_WildcardSearches

Posted on 2011-02-18
7
472 Views
Last Modified: 2012-05-11

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.
0
Comment
Question by:sam15
  • 4
  • 2
7 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 34931417
{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
 

Author Comment

by:sam15
ID: 34933057
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34933535
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 26

Assisted Solution

by:Kurt Reinhardt
Kurt Reinhardt earned 250 total points
ID: 34933575
"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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34933744
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
 

Author Comment

by:sam15
ID: 34958034
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 34958103
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now