Parameters - single or multiple user selection (i.e.optional) 5

[IceRuby]

Hi there
Using Crystal Version 9.0 onto a SQL database.
Users would like to be able select single or multiple parameters from below list:-
* Product Name (string) - discrete
* Product ID (number) - range
* Suburb/City (string) - range
* Product Category (String) - range
* Status (string) - discrete
* Expiry Date (date) - range
* Last edited (date) - range
Can I achieve a selection criteria like this in Crystal?
Must be able to accept 1 or multiple selections from list.
Thanks in advance.

 

[IdoMillet]

K,
I sent you the 2 sample reports I had used here (1 for Access and 1 for SQL Server).

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

OK, I figured it out, the usual subtle sort of oddity that makes Crystal the product we Love:

This works:

{?Last_Name} = {employee.lname} OR {?Last_Name} = "ALL"

The does not:

{?Last_Name} = "ALL" OR {?Last_Name} = {employee.lname}

Purdy slick way to handle this, thanks*!

-k

 

[IdoMillet]

K,

A * to you too for isolating the "order of clauses" issue.

If anyone reading this is heading to CDUGNA 2004 this year, please communicate this to the tech support guys there. That's one of the nice things about CDUGNA, you get to have free gripe sessions with the techies; very therapeutic... )

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[tektipdjango]

Maybe it's an operator precedence issue

Have you try with parenthesis
(
({?Last_Name} = "ALL")
OR
({?Last_Name} = {employee.lname})
)
...

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[IdoMillet]

Django,

I just tried it and it makes no difference.

The test for "All" in the parameter clearly must come AFTER the test for a database value.

What is happening is that Crystal evaluates the expression and, unfortunately, if it sees a 1st clause that is "local" it "gives up" on the idea of passing the condition to the DBMS.

Cheers,
- Ido



CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IceRuby]

Hi there
Thanks very much to everyone. I have parameters working!! (couldn't have done it without you)

One last question still in relation to these parameters, how can a default date be set? Or do I have to set up another formula to cast it to a string?

Thanks once again in advance.

 

[IdoMillet]

IR,

Please start a new thread. Also, it would help if you provide a bit more information. Parameter data type? What are you trying to do? Did you use the default values in the parameter dialog?...

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Digitalcandy]

{@FilterCustomerName}

IF {?CustomerName} = ""
THEN {table.CustomerID} > 0
ELSE {table.CustomerName} = {?CustomerName}

 

[synapsevampire]

DigitalCandy: I tested your formula for either condition and neither passed the SQL to the database. Did you find that it passed it?

-k

 

[Digitalcandy]

Synap,

Basically the way I use parameters is if someone doesn't click on it the default is blank. So if they don't click the parameter then it shows all records but if they do put something into the parameter it will evaluate it.

I thought that's what the original poster was trying to do.

 

[synapsevampire]

Right, I understand your technique, I was just curious if you had seen that this actually gets the database to perform the SQL.

It didn't appear to, and although your logic is sound, performance would suffer.

Since Ido had just demonstrated a new trick to me, I was expecting to be further enlightened.

-k

 

[tektipdjango]

the simplest way is

IF {?CustomerName} <> ""
THEN {table.CustomerName} = {?CustomerName}

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[tektipdjango]

my preceding suggection made the right SQL in both cases but I didn't get the records for defaults value due to wrong filtering by CR when {?CustomerName} ""
.
I've found this syntax that works right (SQL and CR)

{table.CustomerName}= (
IF {?CustomerName} = "" then {table.CustomerName}
else {?CustomerName}
)

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[synapsevampire]

Django: Yeah, I've had lots of SQL coders use that method is SQL (not Crystal), but you can trip up the optimizer on some databases with that.

-k