Paramenter use one selection or all selection 2

[lumier]

Crystal 8.5, SQL,

I have 2 parameters. I need to be able to do the following.

If the paramter comes up, I need to be able to due the following:
1. select either one departement, several or All department or
2. the select parameter to select everyone in a certain company.

I can do one are the other but not both. Once I all my paramteter to select only a certain company, I get everyone from all companies not just the one I need.

This is what is in my select expert:

{?p1} = {field1}
or

(if {?p2} <> "" then(
if {field2} <> "" then
instr(UpperCase({?p2}), UpperCase({field2})) > 0)
else
true )

 

[lbass]

I would try:

{?company} = {table.company} and
(
{?dept} = "All" or
{table.dept} = {?dept}
)

-LB

 

[synapsevampire]

I tend to load the parm with a default value of "All", as in the following:

(
If {?p1} <> "All" then
{table.dept} = {?p1}
else
If {?p1} = "All" then
true
)
and
(
If {?p2} <> "All" then
{table.company} = {?p1}
else
If {?p1} = "All" then
true
)

I have a FAQ on generating record selections here:

faq767-3825

-k

 

[synapsevampire]

If you're using CR 8.5, sometimes LB's syntax won't pass the SQL correctly, so make sure that you check it closely.

-k

 

[rhinok]

LB's syntax is simpler and will generally pass the SQL correctly, but there is a huge caveat.

Crystal Reports will filter out all records with NULL values, using this Record Selection Criteria. If you have any records where company or department are NULL, then they will be filtered out unless you check the 'Convert NULL Values to default...' option in File|Report Options.

To prove this, I created a simple report against the Orders table in the Xtreme Sample Database (2003), which returned only three fields: Order ID, Order Date, PO#. I then created a string parameter to be used against the PO# field, which is NULL for a large number of records. I then applied the following criteria:

({Orders.PO#} = {?PO} OR {?PO} = 'All)

By toggling the 'Convert NULL...' option, you can clearly see how Crystal Reports filters these records. Additionally, this is done on the client side. If you were to review the SQL (Database|Show SQL Query), you wouldn't see any difference. This has the potential to create disastrous consequences for critical reports.

For this reason, I generally recommend using the syntax posted by SV.

~Kurt

 

[lbass]

rhinok,

You didn't use my formula. If you try:

{?dept} = "All" or
{table.dept} = {?dept}

...you will see that all records are returned if "All" is chosen. The order of the clauses matters.

-LB

 

[lbass]

But, I should have added, SV's is the better formula, since it passes the SQL.

-LB

 

[rhinok]

You're absolutely right LB. If you use the order in your formula, then Crystal won't filter the records out on the client side. Unfortunately, your statement won't pass the SQL. It will pass the SQL if you reverse the order, but then you have to worry about converting NULLS.

I was just trying to make the point that sometimes its better to do things the long way in order to avoid certain pitfalls.

~Kurt

 

[lbass]

Another variation: Using the Xtreme database, the following passes the SQL and returns all records (including nulls) when "All" is chosen:

(
{?po#} <> "All" and
{Orders.PO#} = {?po#}
) or
{?po#} = "All"

-LB

 

[synapsevampire]

LB and Rhinok: Unfortunately whether it passes the SQL is based on Crystal version and the database being used...

I spent a looooooong time trying to uncover the rules of this a couple of years ago and finally figured otu that it's pure voodoo, so the formula I suggest is best practice I've been able to uncover, but test (for those unaccostomed, check the Database->Show SQL Query).

The method that LB describes was shared by Ido and it proves effective at passing SQL in many instances, but not all.

-k

 

[rosemaryl]

Dear All,

I agree with SV ... I have noticed that when an expression is simple either method works, but as it becomes more complex, I have found, as SV states, that providing a test for both sides of the if is what works best:

(IF {?Parameter} <> 'ALL'
then {Table.Field} = {?Parameter}
else if
{?Parameter} = 'ALL'
then true
)

When combining with other parameters that must be tested for all, by doing as shown above and enclosing each statement in it's own set of parenthesis, I can guarantee passing all my criteria (in Oracle and in SQL Server). I teach this to all of my students as the most reliable method of passing to the database.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[rhinok]

Ro and SV -

I agree with both of you, I always use the method described by SV and its what I recommend in my classes and to my clients. I was simply trying to point out the filtering issue with LB's formula. If you can get it to pass the SQL, there is still the issue with Convert NULL, which is major, as far as I'm concerned.

~Kurt

 

[synapsevampire]

I know what you mean, LB, but Access is pretty forgiving though, try it with the big kid database drivers and it's not so straightforward, especially if you've numerous clauses you're attempting to pass.

Ido had brought the method you reference to light quite some time ago, but when I tested it against complex record selection formulas against various databases drivers, it ended up getting confused and not passing.

Rather than demonstrating complex examples, I simply inherit the KISS principle, the fully qualified If and ELSE seems to always help Crystal pass SQL.

I work with VERY large scale databases as a rule, rarely with Access. Performance is everything, so the occasional hiccup by Crystal will mean the difference between a < 1 minute report and one that takes 15 minutes plus.

If you've the opportunity to test against various large scale databases, I'd appreciate your insights, you're a very clever lad, but in general consider best practices.

-k

 

[lbass]

Didn't mean to reopen a can of worms. I agree with all that SV's method is best practice, too.

-LB

 

[randycarpet]

Hi everyone

I've been reading this post with interest as it touches on an issue that I've had with one of my reports.

Unfortunately the whole thread didn't answer my question but I'm sure this group of experts could put my quandry to rest:

When constructing selection criteria like that discussed, why is the placement of the conditions important? Namely, why does:

if isnull(field) or field = 'xyz' show both nulls and 'xyz'

when

if field = 'xyz' or isnull(field)) doesn't?

I hope you can help and I apologise for any breach in ettiquette if I've inappropriately posted this question.


Thanks - Andy

 

[Madawc]

randycarpet, you have a new topic and you should have started a new thread. But to save time, I'll answer it here.

When writing formulas, any test for null must always come first. Without isnull, Crystal will stop processing the formula when it hits a null value; the default assumption being that null means show nothing. I made this mistake several times while I was learning Crystal, because it's not obvious to someone who's used to other programming languages.

Madawc Williams (East Anglia, UK)

 

[synapsevampire]

Andy: Read my aforementioned FAQ on passwing SQL, it touches on your concern as well as many other oddities.

-k