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.

 

[BurnsORegan]

This is very possible, however does involve some work.....

In your data selection you need to define a series of If..then..Else functions, 1 for each parameter:

If (?Parameter) = "" //or 0 if number
then true
else {Field} = (?Parameter)

Good Luck

BurnsORegan

 

[IdoMillet]

A simpler and more efficient approach (instead of nested IF statements) is to use AND/OR statements:

(
({?Parameter1} = "Default Value 1") OR 
({Field1} = {?Parameter1})
)
AND 
(
({?Parameter2} = "Default Value 2") OR 
({Field2} = {?Parameter2})
)

The approach above relies on you setting default values for some of the parameters but I believe multi-value parameters would force your users to actively select values.

For a really clean solution, you would need to develop your own front-end application using the Crystal runtime components.

hth,
- Ido

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

http://www.MilletSoftware.com

 

[PatrickIRL]

Used to do stuff like this all the time. Set up a stored procedure in SQL Server with all the parameters and do all the selects there.

Much quicker to do it this way ...

Regards,

Patrick

 

[synapsevampire]

Be cautious about the Record Selection examples provided as they probably WON'T pass the SQL to the database, which is critical to performance.

I have a FAQ on this topic:

faq767-3825

To test for SQL pass through use:

Database->Show SQL Query

To get better results, try:

(
if {?Parameter1} <> "Default Value" then
{Field1} = {?Parameter1}
else if {?Parameter1} = "Default Value" then
true
)
AND
(
if {?Parameter2} <> "Default Value" then
{Field2} = {?Parameter2}
else if {?Parameter2} = "Default Value" then
true
)
etc...

As for using a Stored Procedure, the downside is that you lose a good deal of control over them, such as default values, but the performance will be slightly to much better depending upon the amount of data being queried as it builds a precompiled execution plan.

I assume that by SQL datbase, you mean MS SQL Server database (Oracle, Sybase, Informix are all SQL databases).

-k

 

[IdoMillet]

K,

I'd be surprised if the if/elseif approach would be any better in terms of passing the WHERE clause to the DBMS than the simple approach I outlined above.

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[synapsevampire]

Check it, Ido, it makes a difference.

There are lots of posts around here that demonstrate this, and although your method may work when there's just one or 2 criteria, as it gets more complex, Crystal will hiccup.

The most important thing I emphasize in courses and during design work is to make sure that everything passes to the database, as often as possible.

Different versions of Crystal react differently, as do different drivers, the above demonstrates the most consistent approach I've found to date.

-k

 

[IdoMillet]

K,

Can you confirm seeing a case of a record selection expression containing only AND & OR clauses (no function calls) not being passed as SQL (WHERE clause) to the DBMS?

Do you happen to remember what version of Crystal? ODBC connection? I'd like to try and replicate this because in the past I had never suspected such cases of failing to "behave"... )

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[synapsevampire]

The example I posted is a good demonstration of this.

The following is using CR 8.5 against a SQL Server database:

If {?Company Name} <> "All" then
{Customers.CompanyName}={?Company Name}
else
//If {?Company Name} = "All" then
true

Note the commented line.

Run it commented, nothing is passed to the database, remove the slashes and it passes the SQL.

I always give demonstrations of this to developers and in classes as it's critical to designing reports properly.

The key of course is to assure that whatever exists in the record selection is reflected in the database->show sql query

CR 9 handles this slightly better, but it has some gotchas too. I end up using a fully qualified IF ELSE and sometimes using the Switch function, which does a good job as well.

-k

 

[IdoMillet]

K,

Yes, thanks to one of your many great messages about a year ago I became aware of this behavior. However, this is when the criterion includes an IF clause.

My original suggestion above simplifies the expression to the point where there are no IF clauses. That is why I believe it would be passed as a WHERE clause to the DBMS (even when the expression has more clauses).

By the way, the same simplification approach would apply to your example directly above. The simplified expression is:

{?Company Name} = "All" OR {Customers.CompanyName}={?Company Name}

Regards,
- Ido

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

http://www.MilletSoftware.com

 

[synapsevampire]

I just tried your suggestion, and although it does return the proper data, it does not pass the SQL in CR 8.5 against a SQL Server Database.

I tried:

{?Company Name} = "All"
or
{Customers.CompanyName} = {?Company Name}

-k

 

[IdoMillet]

K,

What you are seeing is simply an artifact of Crystal being very intelligent for a change... )

I bet you were running the report with the "All" value specified for the parameter. Crystal looks at this and says to itself that the expression evaluates to TRUE locally and hence doesn't bother the DBMS with the WHERE clause.

If you enter something other than "All" in the parameter and then display the SQL, you should see the expression.

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[IdoMillet]

K,

This is getting interesting!

I just realized this explanation (if true) also impacts the "extra IF" technique you've been using. You may have been seeing or not seeing the WHERE clause simply due to the local Crystal intelligence. Hence, if adding the extra IF clause made Crystal fail to apply the local test (for the "All" value) then adding the extra IF may actually slow things down (in a very minor way) rather than speed them up (because of sending a WHERE clause when it's not needed)...

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[synapsevampire]

I tested both conditions, and I've done so on many backends, many times.

The technique I'm demostrating tends to do the best job of passing SQL, hence faster processing.

If in doubt. load CR 8.5 and try it. Or read some of the numerous posts here wherein this technique has sped up reports.

If you don't have a SQL database, you can test using Access, just make it an ODBC connection.

I'm not really sure what your last post meant.

-k

 

[IdoMillet]

K,

I just finished testing this against SQL Server (PUBS database).

Here's the record selection formula in the report:

{?Last_Name} = {Employee.Last Name} OR {?Last_Name} = "ALL"

Here's the SQL when the parameter value is "ALL":

SELECT
    Employee.`Last Name`,
    Orders_Detail.`Order ID`
FROM
    (`Employee` Employee INNER JOIN `Orders` Orders ON
        Employee.`Employee ID` = Orders.`Employee ID`)
     INNER JOIN `Orders Detail` Orders_Detail ON
        Orders.`Order ID` = Orders_Detail.`Order ID`

Note that the WHERE clause is missing. This is because Crystal recognizes there is no need to apply a WHERE clause.

Here's the SQL when the parameter value is not "ALL"
("Buchanan" in this case):

SELECT
    Employee.`Last Name`,
    Orders_Detail.`Order ID`
FROM
    (`Employee` Employee INNER JOIN `Orders` Orders ON
        Employee.`Employee ID` = Orders.`Employee ID`)
     INNER JOIN `Orders Detail` Orders_Detail ON
        Orders.`Order ID` = Orders_Detail.`Order ID`
WHERE
    Employee.`Last Name` = 'Buchanan'

Note that the WHERE clause is now provided by Crystal.

This seems to confirm my points above.

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[synapsevampire]

That's bizarre:

{?Company Name} = "All"
or
{Customers.CompanyName} = {?Company Name}

Results in:

SELECT
Customers."CompanyName", Customers."Address", Customers."City"
FROM
"Northwind"."dbo"."Customers" Customers

For parm = "all" or not.

If I use my technique then it works.

Must be a driver or service pack issue.

-k

 

[IdoMillet]

The behavior I described is consistent in my tests of both MS Access as well as under SQL Server. The code blocks above were for Access. Here are the code blocks for SQL Server (ODBC driver):

Record selection formula in the report:

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

SQL in Crystal when entering "ALL" in the parameter:

SELECT
    employee."fname", employee."lname"
FROM
    "pubs"."dbo"."employee" employee

SQL in Crystal when entering "Accorti":

SELECT
    employee."fname", employee."lname"
FROM
    "pubs"."dbo"."employee" employee
WHERE
    employee."lname" = 'Accorti'

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[synapsevampire]

I've no idea why it doesn't work here, what's your version of CR 8.5?

I'll try applying the Service Pack.

-k

 

[synapsevampire]

SP didn't help, please email the report to me (don't use Save Data With Report as this account doesn't have much room).

crystalreports AT Hotmail.

-k

 

[IdoMillet]

CR 8.5.3.397 Developer.

Very strange indeed. I don't expect this is a version issue.

Are you sure you actually refreshed the report with a value other than "All" and after that looked at the SQL?

Cheers,
- Ido

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

http://www.MilletSoftware.com