Regarding choosing an All option in a parameter in crystal reports

[Former Member]

Hi Guys,

I have two selection criteria.The first carries two string values and the second carries two values 10 and 20.I am adding the third value for my second parameter "All" and trying to select one value for my first selection criteria and "All" as my value for second selection criteria and i need all data corresponding to 10 and 20 for one string value selected in my first selection criteria

The code I have used in my Record selection formula is

If {?Parameter} <> "All" Then

    ToText({tablename.fieldname}) = {?Paramter}

Else ToText({tablename.fieldname}) <>  {?Parameter}

I am able to retrieve all records when I choose "All" as my second criteria and a single value for my first selection criteria.However if I do not choose all and just select 10 or 20, I receive a blank report.Any help regarding this would be much appreciated guys.Thanks

[DellSC · Accepted solution]

When you use "If" statements in the record selection formula, Crystal is generally unable to pass that criteria down to the database for processing, so it will process the filter in memory which will slow down your report. 

The ToText() will cause this to happen as well, but you can get around that by using a SQL Expression to convert the number to text.  You have to use the syntax for your database when creating a SQL Expression.  So, in SQL Server it might look like this:

Cast("tablename"."fieldname" as char)

And in Oracle it would look like this:

To_Text("tablename"."fieldname")

If we call the SQL Expression "FieldToText", here is how I would rewrite this so that 1) it works and 2) it gets passed to the database for processing:

(

  {?Parameter} = 'All' or

  {%FieldToText} = ?Parameter

)

Note the surrounding parentheses - if you have any other selection criteria, you MUST have them for this to work correctly.

-Dell

[Former Member · Accepted solution]

Hey Christy,

I tried my code without ToText for testing purposes with test data and I get the same kind of output.

My first selection criteria is a String.It is called "Location". The second selection criteria is Location Number.They are stored as  numbers in the database but I have the stored the parameter as a string as I cant add an "All" option without keeping it as a string.

What I am noticing is when I choose "all" ,I get all locations. However when I choose one of the locations I get a blank report.

I tried the above code that you said but I am receiving a field not known error. The thing that puzzles me is why is the all option working but when I select location 10 or location 20,it does not work.When I removed my code, I am able to see 10 or 20 but quite naturally my all option does not work.

[DellSC · Accepted solution]

Please post your full formula from the Select Expert and I'll see what might be happening.

-Dell

[Former Member · Accepted solution]

Hi Christie,

I have typed the code that I have used in my report. Please help me regarding this and tell me what I have gotten wrong

{Tablename.field1}={?Parameter1} AND

{Tablename.field2}={?Parameter2} AND

(

If {?Parameter3} <> "All" Then

    ToText({tablename.field3}) = {?Paramter3}

Else ToText({tablename.field3}) <>  {?Parameter3}

AND

({tablename.field4} >={?StartDate}

AND {tablename.field5} <={?EndDate})

[abhilash_kumar · Accepted solution]

Hi Ashwin,

Change the code to:

{Tablename.field1}={?Parameter1} AND

{Tablename.field2}={?Parameter2} AND

(

    ({?Parameter3} = 'All' OR {?Paramter3} = ToText({tablename.field3},'#'))

     AND

     {tablename.field4} >={?StartDate} AND {tablename.field5} <={?EndDate}

)

I also recommend adding a SQL Expression to convert the tablename.field3 to a string as suggested by Dell.

Once a SQL Expression is created, the code would look like this:

{Tablename.field1}={?Parameter1} AND

{Tablename.field2}={?Parameter2} AND

(

    ({?Parameter3} = 'All' OR {?Paramter3} = {%SQL Expr Name})

     AND

     {tablename.field4} >={?StartDate} AND {tablename.field5} <={?EndDate}

)

-Abhilash

[Former Member · Accepted solution]

Hey Abhilash,

Your code worked. Thanks so much for your help. You are the best man. I would not have figured it out.The mistake I made was using an "If" statement instead of using an "or" condition. I still wonder why the if condition does not work. It did not work even when my field was already a string and I did not use the ToText option.