Print Page | Close Window

Select all when parameter is blank.

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20613
Printed Date: 03 May 2024 at 3:15pm


Topic: Select all when parameter is blank.
Posted By: DrPepperholik
Subject: Select all when parameter is blank.
Date Posted: 04 Apr 2014 at 2:37am
Hi all, I need some help with record selection. I need Crystal to select all records if this one parameter is blank, otherwise select where the record is equal to the parameter. It sounds simple but I've tried suggestions I've searched for but they didn't work.

What keeps happening is the SQL string gets appended with this if I send in a blank:

"Table"."Column"=''

It works fine if I send in a parameter.

Right now I have this:

(if (NOT isNull({?Parm})) then {Table.Column} = {?Parm})

Which gives me the SQL above.


Replies:
Posted By: DBlank
Date Posted: 04 Apr 2014 at 4:00am
I assume you have a version of Crystal that allows for null parameters...
 
 
(NOT(hasvalue({?Parm})) or {?Parm}={Table.Column})

Posted By: DrPepperholik
Date Posted: 04 Apr 2014 at 4:06am
I'm using version 14.

That didn't work. I still have the "Table"."Column"='' showing up in my SQL Query and no data.

Posted By: DBlank
Date Posted: 04 Apr 2014 at 4:36am
what exactly are you putting in the select statement?

Posted By: DrPepperholik
Date Posted: 04 Apr 2014 at 4:58am
This is the record selection: 


(if {?Parm1?} = 'Y' then {SomeTable&Col} ='Y' else 1=1) and

(if {?Parm2}='N' then {SomeTable&Col}='P' else 1=1) and

{SomeTable&Col} = {?Parm3} and

{SomeTable&Col}>={?Parm4} and

{SomeTable&Col}<={?Parm5} and

{SomeTable&Col} in {?Parm6} to {?Parm7} and

(NOT(hasvalue({?Parm})) or {?Parm}={SomeTable&Col})


This is the SQL it generates: 

SELECT Stuff

 FROM  Location

 WHERE  "SomeTable&Col"='P' AND "SomeTable&Col"=44 AND ("SomeTable&Col">={ts '1950-01-01 00:00:00'} AND "SomeTable&Col"<{ts '2050-12-02 00:00:00'}) AND "SomeTable&Col"=6093 AND "SomeTable&Col"=''

 ORDER BY "SomeTable&Col", "SomeTable&Col", "SomeTable&Col", "SomeTable&Col"


Where Parm = blank

Posted By: DBlank
Date Posted: 04 Apr 2014 at 5:20am

are param 1 and 2 set up to not allow nulls and what is the input type? a string, a drop down,...? I do not recomment uinsg the if-then process in the select criteria but am not clear on the set up so am not sure how to recommend to change it.

 
did you remove all of your other criteria to see if the
(NOT(hasvalue({?Parm})) or {?Parm}={SomeTable&Col}) 
works as desired by itself?
 
When running the report are you selecting the null check box for that parameter?

Posted By: DBlank
Date Posted: 04 Apr 2014 at 5:22am
I see you revised your post.
Are you selecting a 'blank' ("") value from a parameter list or are you setting the param value to NULL using the null check box in the parameter pop up?

Posted By: DrPepperholik
Date Posted: 04 Apr 2014 at 5:27am
Parm 1 is not allowed to be null. Parm2 can be. The input type is number for Parms 1, 2 and 3.

Removing all other selection criteria still yields the same SQL with the WHERE SomeTable&Col = ''.

There is no option for a null check box. Should there be one?

EDIT to answer last question: There is no null option, I'm just leaving the field blank, there aren't any options. It's just a text field that I leave blank.

Posted By: DBlank
Date Posted: 04 Apr 2014 at 6:05am
I am not using version 14 but I believe it has the option for each of the parameter set ups to allow you to set to null or not ... equates to the hasvalue() in the formula).
you can try this... 
({?Parm}="" or {?Parm}={SomeTable&Col})
I am not sure if it wil work or not as the hasvalue is where I have seen this done in versions passed XI.
 

Posted By: DrPepperholik
Date Posted: 04 Apr 2014 at 6:55am
The only problem with {?Parm} = "" is that the DB value I'm checking for will never be blank.

I've seen the check box for null before in some reports. I'm not sure why this one doesn't have it.

The report I've seen that has the check box for null values uses a stored procedure instead of linking to the views directly. Could that be why it allows for a null but this one doesn't give me the option? There isn't any settings in the parameter that indicate to allow null value.

Posted By: DBlank
Date Posted: 04 Apr 2014 at 7:03am
In the parameter properties I believe you have to set Optional=True
 
That said, if you looka the below it is not checking the DB for the field to be ="", it is checking the parameter value to be "" which is similar to your "else 1=1".
({?Parm}="" or {?Parm}={SomeTable&Col})
 

Posted By: DrPepperholik
Date Posted: 04 Apr 2014 at 8:10am
That got it to work in Crystal but when I move the Crystal file over to the reports directory for the vendor software it doesn't work. Is that something that will only work in the Crystal environment?

Posted By: DBlank
Date Posted: 04 Apr 2014 at 8:43am

Not sure I can answer that.

I assume the software has its own custom interface to run the reports and input the parameters so I don't know how that was written or if the custom interface accepts the blanks/nulls in the params.

Posted By: DrPepperholik
Date Posted: 04 Apr 2014 at 8:48am
Your guess is as good as mine. I wish I could access the code for the vendor software but of course I can't.

Posted By: DBlank
Date Posted: 04 Apr 2014 at 9:27am
another solution is to use stored procedure as a source or to add an 'All' to the lists and use it instead of a blank/null.

Posted By: DrPepperholik
Date Posted: 04 Apr 2014 at 9:47am
There is no list, it's user entered data. It's not a drop down.

Posted By: DBlank
Date Posted: 04 Apr 2014 at 10:01am

So can you train the users to use/type in 'All'

(trim({?Parm})="All" or {?Parm}={SomeTable&Col})
 
 

Posted By: DrPepperholik
Date Posted: 04 Apr 2014 at 10:38am
I didn't think of that. The problem is solved by them having a standard report that already did the "all" part so I made this parm field required so they can't get back an empty report.


Print Page | Close Window