Null Parameters with Select Expert Question

[CUTiger1995]

Hello,
I'm writing a report in Crystal 7 trying get inventory information. I have a table listing equipment (by unique identifier), the storeroom in which it is located, and the shelf in that storeroom on which it is sitting. I am trying to use the Select Expert with Parameter fields to allow the user to specify the Storeroom and Shelf that he or she wishes to see. That part is working fine. Here's my question:
Is there any way to set up the select formula or the "shelf" parameter so that if a storeroom is input to the "storeroom" parameter, but the "shelf" parameter is left blank, the report will show equipment on ALL shelves in that storeroom?

Thank you for your help. I greatly appreciate it!

Steve

 

[synapsevampire]

Place an if in your record selection criteria:

(If not isnull({?Storeroom}) then
{MyTable.MyStoreroom} = {?Storeroom}
else
if isnull({?Storeroom}) then
true)

and

(If not isnull({?Shelf}) then
{MyTable.MyShelf} = {?Shelf}
else
if isnull({?Shelf}) then
true)

I'm going from memory here, and keep in mind that it's purposely constructed with the "else if" per parameter to get Crystal to pass through the SQL to the database.

This may not be applicable in your case if you're not using a SQL database (you didn't state what data source you're using).

Most users either neglect to think about passing through the SQL, or are unable to get it to work, and just let Crystal do all of the work, which is punishingly slow for large data sets.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[CUTiger1995]

Thanks for your response k. I really appreciate it. I am in fact using a SQL database--SQL Server 7.

I tried the statement as you suggested. Here is my selection formula:

(if not isnull({?Location(s)}) then
{EQUIPMENT.location}={?Location(s)}
else
if isnull({?Location(s)}) then
true) and
(if not isnull({?Shelf}) then
{EQUIPMENT.binnum}={?Shelf}
else
if isnull({?Shelf}) then
true)
Unfortunately, I get no results from this. Any ideas?

Thanks,

Steve

 

[Ngolem]

One way to get around the "NULL" parameter problem is to apply a default value. The word "ALL" can be used in both cases.

So set the default value of the Location and shelf to both be equal to ALL

What type of parameter are these BTW. {?Location(s)} implies to me that this is a multi-input parameter...is it?

Does the same hold true for {?Shelf}?

Use the following in your record select formula...I think it would apply to both single and multi-input parameters.

(if uppercase({?Location(s)}) = "ALL" then
{EQUIPMENT.location} like "*"
else
{EQUIPMENT.location} in {?Location(s)} ) and

(if uppercase({?Shelf}) = "ALL" then
{EQUIPMENT.binnum} like "*"
else
{EQUIPMENT.binnum} in {?Shelf} )


Hope this works for you

Jim Broadbent

 

[synapsevampire]

Ngolem brings up a good point, your original post didn't mention the Location(s) parameter, anything that is a multiple parameter uses *in*, not an "=".

I generally explicitly state that the location(s) is the first entry in the parameter list, and further qualify it in pick lists to allow for a real choice of the word "ALL" as in:

(if uppercase({?Location(s)}[1]) = "All Locations" then...

The most important point here is that I explicitly construct my if's to allow for pass through SQL, Ngolem's example uses a "like *", where I use true. Neither should pass SQL to the database at this point anyway (good thing, if it did it could only degrade performance).

Check your *show sql statement* to make sure that your passing SQL when you have an explicit where clause (parameters selected), and that it does not when you don't or choose "All Locations".

The example I gave is tested in both my Oracle and SQL Server environment, I tested Ngolems example and it didn't for me (have you seen a database where that example will pass the SQL, Ngolem?).

Recently, I spent all day exploring what does pass and what does not because my latest contract requires that I use only Views (as opposed to Stored Procedures), so I had to work out the kinks in Crystals pass through...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

SV - No you are correct that this will not pass down to the server and will be processed on the second pass through the records. You are kinda handicapped in CR 7.0 since you don't have tools such as IIF, Select Case and Switch which help out greatly in this respect.

As soon as you use an "IF" of any kind you are processing on Client side.

I don't see there being a great performance hit here though. Typically I have seen reports created in less that a minute (typically 30 secs or so) when as many as 20,000 records are processed. CUTiger will have to see if this is acceptable. In my experience, a report generated in less than 1 minute is acceptable to the client. Jim Broadbent

 

[synapsevampire]

I didn't know that 7 wouldn't pass SQL when an If was used...

I have an old version of Seagate Info 7 I'll try it on, if that's the case, that must be VERY annoying... I was told that it wouldn't with 8.5 too, but they were mistaken.

I saw posts about using IIF or Case to increase the likelihood of passing SQL, but they did me little good. I use IF and it passes up to 9 multi, including date range, parameters worth of SQL accurately to numerous reports. I even use fomula results to construct the pass through, the trick was to use the ELSE to counter every IF using TRUE, and to NOT use variables in the formulas. At least that's my current religious belief when dealing with the picky lil SQL statement generator in there...

I've been insulated from such nuisances for years by offloading the real SQL to Stored Procedures, mainly because the databases were too large to do otherwise.

But the nature of the parameters and use of the reports on this contract makes it impossible to use Stored Procedures now, it's a shame, but at least I learned the pass through stuff and other blights of View and table based report creation.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com