Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: handling null values in date parameters Post Reply Post New Topic
Page  of 2 Next >>
Author Message
pshilpain2000
Newbie
Newbie


Joined: 10 Mar 2009
Location: United States
Online Status: Offline
Posts: 10
Quote pshilpain2000 Replybullet Topic: handling null values in date parameters
     Posted: 10 Mar 2009 at 5:13pm
Hello Everyone,
 
I am new to Crystal Reports. I did build couple of crystal reports and they all worked fine. One issue right now I am having is, I have a date parameter in a report which maps to a date field in database. Everything works fine, but it doesnt bring the records with null date values. So there is no use to have a formula in the report to handle Null values with some text or anything else when it doesnt bring in null date values. I actually have a formula in the report which says,
 
IF ISNULL(Date_Parameter)  Then "No End Date"
                else cstr(Date_Parameter)
 
I am using Crystal Reports XI R2 (11.5) with Database SQL Server 2005.
 
Please advise. Your help is greatly appreciated. Thanks in Advance.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Mar 2009 at 5:29pm
Do you want to always incude the null date values in your report regardless of the parameter entered by the user?
If so then change your select statement with an or statement to incldue these:
isnull(table.datefield) or {?date parameter}=(table.datefield)
 
If this is not what you are looking for can you post more details?


Edited by DBlank - 10 Mar 2009 at 5:30pm
IP IP Logged
pshilpain2000
Newbie
Newbie


Joined: 10 Mar 2009
Location: United States
Online Status: Offline
Posts: 10
Quote pshilpain2000 Replybullet Posted: 11 Mar 2009 at 11:24am

Thank you DBlank for your quick response.

Well, I am not using any SQL Statement to change. Let me elaborate more on what I am trying to look for.

I have two date parameters ... StartDate and EndDate ... StartDate is never null, but in some cases EndDate can be null ...

I built parameters for both. The user wanted to see the phrase "No End Date" where EndDate is null. For that I created the formula as i posted in my earlier post. But the parameter doesnt bring in the records where End Date is null, so that formula is not being used also. I have something like this in the record selection ...


if {?Parameter1}=0
then (
(DBcolumn2 = {?Parameter2} OR {?Parameter2} = 'ALL') and
({DBcolumn3} = {?Parameter3} OR {?Parameter3} = 'ALL') and
({DBcolumn4}={?Parameter4} OR {?Parameter4}='ALL') and
({DBcolumn5}={?Parameter5} OR {?Parameter5}='ALL') and
({DBcolumn1}in [val1,val2,val3,val4,val5,val6,val7]) AND
({DBcolumn6}={?Parameter6} OR {?Parameter6}='ALL')
AND ({?StartDate}<={DBcolumn7} and {?EndDate}>={DBcolumn8})
)
else
(
(DBcolumn2 = {?Parameter2} OR {?Parameter2} = 'ALL') and
({DBcolumn3} = {?Parameter3} OR {?Parameter3} = 'ALL') and
({DBcolumn4}={?Parameter4} OR {?Parameter4}='ALL') and
({DBcolumn5}={?Parameter5} OR {?Parameter5}='ALL') and
({DBcolumn1}= {?Parameter1}) and
({DBcolumn6}={?Parameter6} OR {?Parameter6}='ALL')
AND ({?StartDate}<={DBcolumn7} and {?EndDate}>={DBcolumn8})
)
 
I hope I am much clear now. I can explain it again if still not clear.
 
Thanks again,
Sil.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Mar 2009 at 11:36am
Not sure about the "ALL" items since I cannot see the items it is pointing to but try to handle the no enddate issue by altering portions like this:
 
if {?Parameter1}=0
then (
(DBcolumn2 = {?Parameter2} OR {?Parameter2} = 'ALL') and
({DBcolumn3} = {?Parameter3} OR {?Parameter3} = 'ALL') and
({DBcolumn4}={?Parameter4} OR {?Parameter4}='ALL') and
({DBcolumn5}={?Parameter5} OR {?Parameter5}='ALL') and
({DBcolumn1}in [val1,val2,val3,val4,val5,val6,val7]) AND
({DBcolumn6}={?Parameter6} OR {?Parameter6}='ALL')
AND {?StartDate}<={DBcolumn7} and
({?EndDate}>={DBcolumn8} or isnull({DBcolumn8}))
)
else
(
(DBcolumn2 = {?Parameter2} OR {?Parameter2} = 'ALL') and
({DBcolumn3} = {?Parameter3} OR {?Parameter3} = 'ALL') and
({DBcolumn4}={?Parameter4} OR {?Parameter4}='ALL') and
({DBcolumn5}={?Parameter5} OR {?Parameter5}='ALL') and
({DBcolumn1}= {?Parameter1}) and
({DBcolumn6}={?Parameter6} OR {?Parameter6}='ALL')
AND {?StartDate}<={DBcolumn7} and
({?EndDate}>={DBcolumn8} or isnull({DBcolumn8}))
)
 
 
 
IP IP Logged
pshilpain2000
Newbie
Newbie


Joined: 10 Mar 2009
Location: United States
Online Status: Offline
Posts: 10
Quote pshilpain2000 Replybullet Posted: 11 Mar 2009 at 11:50am

i tried that just now but it didnt work

Well i tried this also based on your suggestion(something similar) instead of having a formula.
 
AND ({?Start Date}<={DBcolumn7} and (cstr({?End Date})>= (if isnull({DBcolumn8}) then "No End Date" else cstr({DBcolumn8}))))
 
but it is further reducing my number of records ... Angry
 
please help
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Mar 2009 at 12:10pm
I think the issue is in the rest of your select statement...
these "ALL" options are not making sense to me.
((DBcolumn2 = {?Parameter2} OR {?Parameter2} = 'ALL'))...
What are you trying to get from this statement?
Do they have a selection of "ALL" as an option here and if so you want that to not filter correct (include everything from that column)?
If this is the what you are trying to do you want something like:
 
(if {?Parameter1}>0 then {?Parameter1}={DBcolumn1} else {DBcolumn1}={DBcolumn1})
and
(if {?Parameter2} <> 'ALL' then {?Parameter2})=DBcolumn2 else {DBcolumn2}={DBcolumn2})
and
(if {?Parameter3} <> 'ALL' then {?Parameter3})=DBcolumn3 else {DBcolumn3}={DBcolumn3})
and
....repeat for all of your variables.


Edited by DBlank - 11 Mar 2009 at 12:12pm
IP IP Logged
pshilpain2000
Newbie
Newbie


Joined: 10 Mar 2009
Location: United States
Online Status: Offline
Posts: 10
Quote pshilpain2000 Replybullet Posted: 11 Mar 2009 at 12:23pm

regd the ALL option :

 

When we click on edit paramater and we want to give user the ability to select more than one value, then we click on 'append all database values' to make the values available to the user. Now, we can just type ALL at the top of all values there. When a user selects ALL, then by default he selects all the database values, instead of selecting all values one by one. Thats the main use of ALL. I dont think it is resctricting the records.

 
I am still just playing with the report. Thanks for all your help. Please let me know, if I am missing anything.
 
Thanks,
Sil.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Mar 2009 at 12:35pm
I could be wrong because I am not understanding your full set up but I don't think the statement of
"(DBcolumn2 = {?Parameter2} OR {?Parameter2} = 'ALL') " does what you are trying to do. Have you tested it out by itself?
It really is saying that dbclumn2 = your second paramater or your second parameter = the text 'All' which makes no selection sense at all. Usually you have to handle this in the select statement as I indicated above.
IP IP Logged
pshilpain2000
Newbie
Newbie


Joined: 10 Mar 2009
Location: United States
Online Status: Offline
Posts: 10
Quote pshilpain2000 Replybullet Posted: 11 Mar 2009 at 12:49pm
let me try your suggestion DBlank. Thanks a lot. Will keep updated.
IP IP Logged
jbleep
Newbie
Newbie


Joined: 11 Mar 2009
Location: United States
Online Status: Offline
Posts: 4
Quote jbleep Replybullet Posted: 11 Mar 2009 at 12:54pm
This may work.  In some reports, if you are getting a NULL, it will error out in the formula and omit the record.  You have to go through hoops in order to handle the NULL.  In File->Report options (at least in 8.5) if you check Convert NULL to default, it will convert that NULL to something you can handle.  In the case of strings, it will be a blank.  Then you can handle it a lot easier, and it won't error out when it hits the NULL.  Maybe that will help.
JB Leep
IP IP Logged
Page  of 2 Next >>
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.