Passing * as a parameter 4

[fwatanabe]

Hello all. I have the following Selection Formula for a report I'm designing in Crystal 8.0, against a SQLServer 7 database:

{valuelist.listname} = "WOSTATUS" and
not ({workorder.status} like ["CAN", "CLOSE", "COMP"]) and
{workorder.supervisor} like {?Supervisor} and
{workorder.glaccount} like {?FMCC} and
{workorder.leadcraft} like {?Craft} and
{wplabor.laborcode} like {?Labor}

I would like the user to be able to pass wildcards into the parameter, and when they enter '*' for each parameter, the report should return all records. However, it only returns those where the field is not null. I have done this before on an Oracle database, and I seem to recall that '{fieldname} like * ' returned everything including NULL records.

I have tried using IF statements, but I guess I don't have the syntax right, because nothing seems to work for me. I would really appreciate some insight on this because it is driving me absolutely crazy! Thanks in advance.

 

[dgillz]

I think its your like statements. Is {?Supervisor} a paramter field? In Crystal, parameters fields start with "{?"

The following I pasted from CR help files to show the useages of like:

{customer.FIRST NAME} like "D?n"
TRUE, where {customer.FIRST NAME} = Dan or Don.

{customer.FIRST NAME} like "D?n"
FALSE, where {customer.FIRST NAME} = Doug or Rob.

{customer.LAST NAME} like "*s?n*"
TRUE, where {customer.LAST NAME} = Johnson or Olson or Olsen.

{customer.LAST NAME} like "*s?n*"
FALSE, where {customer.LAST NAME} = Johnston or Smith.

I would change your first 'like' to 'in'. If lines 3 thru 6 are referring to parameter fields, change the 'like' to equals '='.

If they are not referring to parameter fields, make use of the asterisk '*' and try it again.
Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[fwatanabe]

Hi there,

Lines 3 through 6 are indeed referring to parameter fields. The reason I am using LIKE instead of = is because I want to be able to use wildcards in the parameter.

Everything works fine except when asterisk (*) is passed in for any of the parameter fields. I want the report to return ALL records, but it only returns records where that field is not NULL.

example:

Supervisor          GLAccount
Bob the Builder     123
Wendy               123
Muck                NULL

So, let's simplify and say we are only applying one piece of the selection formula to the recordset - {workorder.glaccount} like {?FMCC} - and {?FMCC} is set to *. It will only return the first 2 records (because they have a value for glaccount) and I need to see all 3 records.

 

[dgillz]

Ok then, use this type of logic: If {?Supervisor}="ALL" then true else {workorder.supervisor} = {?Supervisor}.

That will return all records is the Parameter is answered "ALL".


Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[fwatanabe]

This is much closer to the answer I seek! I have tried that, but I can't seem to get the syntax right with the 4 different IF statements.

{valuelist.listname} = "WOSTATUS" and
not ({workorder.status} in ["CAN", "CLOSE", "COMP"]) and
if {?Supervisor} = "ALL" then true
else   {workorder.supervisor} like {?Supervisor} and

if {?FMCC} = "ALL" then true
else    {workorder.glaccount} like {?FMCC} and

if {?Craft} = "ALL" then true
else   {workorder.leadcraft} like {?Craft} and

if {?Labor} = "ALL" then true
else   {wplabor.laborcode} like {?Labor}

This is what I tried, and if I pass "ALL" for every parameter, I get all of my records. However, when I don't pass "ALL", and I pass a value I want to restrict the report by, only the first IF statement gets processed. The rest are ignored. In VB I would have placed END IF statements to indicate the closure of each IF, but in Crystal syntax, I don't know how to do this.

 

[Ngolem]

Another structure that I have found useful in Record select formulas is the Select Case

eg.

(Select {?Supervisor}
case "*":
true
default:
{workorder.supervisor} = {?Supervisor};
) and

it is passed down to the server too

Hope this helps.....Jim

 

[fwatanabe]

Yeah, that's exactly what I needed. I knew the logic I wanted, but not the syntax. Thanks a bunch!

 

[dgillz]

Ngolem,

Please explain to me how this works if its not too much trouble. I have never used select or case or default in a crystal formula, and the trailing "and" has got me confused.
Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[Ngolem]

Dgillz -

the trailing "and" is just part of the record selection formula

the select case is keyed to {?Supervisor}

if it is equal to "*" then the case evaluates to true and all records are selected.

Default is a "catch all" for other cases that are no traped earlier in the Select

This does work well but can be tricky sometimes if the select case is nested with other structures.

Jim

 

[kenhamady]

Fwatanabe,

Is this a Maximo report? If you are curious, your nested IF would have worked if you enclosed each if-then in parentheses. That prevents the next IF from being considered part of the prior ELSE. It would have been:

{valuelist.listname} = "WOSTATUS" and

not ({workorder.status} in ["CAN", "CLOSE", "COMP"]) and

(if {?Supervisor} = "ALL" then true
else {workorder.supervisor} like {?Supervisor} ) and

(if {?FMCC} = "ALL" then true
else {workorder.glaccount} like {?FMCC}) and

(if {?Craft} = "ALL" then true
else {workorder.leadcraft} like {?Craft}) and

(if {?Labor} = "ALL" then true
else {wplabor.laborcode} like {?Labor})
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[fwatanabe]

Hi Ken!

Yes, this is a Maximo report - how did you guess! The Case statement worked just fine, but it'll be handy to know how the syntax for nested IF statements as well.

Thanks a bunch. I appreciate all the help I received in this forum!

 

[kenhamady]

I recognized the field LeadCraft. I have a couple of customers using Maximo, so I have worked with it. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[fwatanabe]

Here is another question based on the same formula...

I've only pasted in the section I changed, as you can view the above messages to see the rest. What I've done is created 2 expressions that use the same parameter {?Craft} and I want to return records where either expression is TRUE. So I used the OR operator between them, but it doesn't work. Only returns records that match the part before the OR and not the expression after the OR.

(Select {?Craft}
   case "*":
      true
   default:
      (({labor.craft} like {?Craft}) OR ({wplabor.laborcode} like {?Craft}));
 )

Does Crystal not recognize the OR, or do I have the syntax wrong again?

 

[Ngolem]

I don't think you can use "like" with Select expressions .

the way you have expressed it here it might as well be "=" anyway since there is no wildcard character in the expression...eg: {?Craft} + "*"

Also you are defeating the purpose of the Default case anyway...these should be "cases" in the select structure

(Select {?Craft}
case "*":
true
case {labor.craft}:
true
case {wplabor.laborcode}:
true
default:
false;
)

This how it should look....from what you have described

 

[fwatanabe]

You can use LIKE, I know because a single LIKE expression works. The parameter's value would include the wildcard (e.g. "AB*&quot. It is the OR that is giving me grief.

I think you missed the point of my question. Here's my scenario (in a nutshell) - For the {?Craft} parameter, if I enter "PLUMB*", I want all records where {labor.craft} like "PLUMB*" OR {wplabor.laborcode} like "PLUMB*"

"PLUMB*" should include "PLUMBER" and "PLUMBASST" - and this value could exist in 2 different fields (craft and laborcode) and I want the expression to return TRUE if it's in either one.

I hope this makes my problem clearer!

 

[Ngolem]

well...in a test of my own "like" did not work...but if you say it works...sobeit

Select is a comparison of something to somethingelse...in this case {?Craft} to a {field}

NOT

somethingelse to something ie: {field} to {?Craft}

this is why I don't think "Like" works in this structure...

but if you can do it....fine

 

[Hm786]

Hi,
I used to write the formula as below for your above problem and it works fine for me.

{valuelist.listname} = "WOSTATUS" and

not ({workorder.status} in ["CAN", "CLOSE", "COMP"]) and

(if {?Supervisor} = "ALL" then
{workorder.supervisor}={workorder.supervisor}
else {workorder.supervisor} like {?Supervisor} ) and

(if {?FMCC} = "ALL" then
{workorder.glaccount}={workorder.glaccount}
else {workorder.glaccount} like {?FMCC}) and

(if {?Craft} = "ALL" then
{workorder.leadcraft}={workorder.leadcraft}
else {workorder.leadcraft} like {?Craft}) and

(if {?Labor} = "ALL" then
{wplabor.laborcode}={wplabor.laborcode}
else {wplabor.laborcode} like {?Labor})


You can change OR instead of AND also.

Thanks,
Hamida

 

[kenhamady]

fwatanabe,

The OR looks fine, although I don't use the CASE very often.
Have you tried it without the CASE?
Are either of theses fields NULL?
Nulls will stop the formula form processing. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[fwatanabe]

Ken, I worship you. Not only are you an absolute encyclopedia of CR knowledge, but you always understand what I'm asking!

Yes, the NULL fields were messing me up. As soon as I checked for NULL first, it worked fine. I'll paste it in for anyone else who may appreciate the code example:

<...snipped code>
and
(if {?Craft} = &quot;*&quot; then
true
else
(if not isnull({labor.craft}) then
{labor.craft} like {?Craft})
or
(if not isnull ({wplabor.laborcode}) then
{wplabor.laborcode} like {?Craft})
) and

<...snipped code>

Thank you so much KEN!!!