Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: isnull use in parameters Post Reply Post New Topic
Author Message
cbaldwin
Groupie
Groupie


Joined: 09 Apr 2014
Online Status: Offline
Posts: 81
Quote cbaldwin Replybullet Topic: isnull use in parameters
    Posted: 09 Apr 2014 at 12:21pm
I am a Crystal novice.
 
I presently have 2 versions of the same report.  One displays all tables entries regardless of what the {novice_date} field contains the other diplays only the table entries with a null value for the {novice_date} field.
 
I would like to have the same report be able to display either "all" table entries for the {novice_date} field or only the table entries with a null value for the {novice_date} field. I have tried creating a parameter that i will call {?report_format}.  I defined the 2 choices for the parameter as "all" and "nullvalue". 
 
I have attempted to create an If Then Else statement for the {novice_date} field to accomplish my goal and base it off of the answer in the {?report_format} parameter. I did not succeed.  Unfortunately im not at my workcomputer so i cannot share the exact syntax of my if then statement. 
 
Any guidance in how i should approach this would be greatly appreciated.
IP IP Logged
nix1016
Groupie
Groupie


Joined: 07 Jun 2010
Online Status: Offline
Posts: 40
Quote nix1016 Replybullet Posted: 09 Apr 2014 at 1:46pm
You can add the following to the suppression of the section which you're using to display the entries:
{?report_format} = 'nullvalue' and not isnull({novice_date})
This suppresses any entries that do not have null dates if the user has selected 'nullvalue'
IP IP Logged
cbaldwin
Groupie
Groupie


Joined: 09 Apr 2014
Online Status: Offline
Posts: 81
Quote cbaldwin Replybullet Posted: 10 Apr 2014 at 3:22am
Thanks I will give this a try.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Apr 2014 at 6:12am
I would approach this a little differently and use the criteria.
Set the select formula editor to use 'default values for nulls'
and use 
 
((isnull({table.novice_date}) and {?report_format} = 'nullvalue')
or
{?report_format} = 'All')
IP IP Logged
cbaldwin
Groupie
Groupie


Joined: 09 Apr 2014
Online Status: Offline
Posts: 81
Quote cbaldwin Replybullet Posted: 10 Apr 2014 at 9:31am
This worked well..
ty
IP IP Logged
cbaldwin
Groupie
Groupie


Joined: 09 Apr 2014
Online Status: Offline
Posts: 81
Quote cbaldwin Replybullet Posted: 10 Apr 2014 at 11:01am
Adding your suggestion to my report worked well in crystal. When uploaded the report from crystal into my system I get the following Exception Error the first time I run the report. The report appears to run fine and this error only appears to show the first time you run the report when you log in. Any thoughts?

System.NullReferenceException: Object reference not set to an instance of an object.

I set the formula editor to "default values for nulls" as directed and my formula syntax is below.

{DONOR_DRAW.DRAW_START_DATETIME} = {?QUERY DATE RANGE} and
((isnull({DONOR_COMMENT.EDATE}) and {?REPORT FORMAT} = 'Incomplete')
or {?REPORT FORMAT} = 'All') and
{DONOR_COMMENT.DONOR_COMMENT_CD} = "24_HR_PEND" and
{DONOR_PHONE.PHONE_TYPE_CD} = "H"


Would the previous commenters suggestion of setting section suppression help me avoid this System Null Exception?

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Apr 2014 at 3:40am
i would not assume that the select statement is the issue.
If you take that portion out of the select statement and load it into the software again, do you still get the same error?
IP IP Logged
cbaldwin
Groupie
Groupie


Joined: 09 Apr 2014
Online Status: Offline
Posts: 81
Quote cbaldwin Replybullet Posted: 11 Apr 2014 at 3:51am
I have a prior version of the crystal report that was uploaded and does not get the system.nullreference exception.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Apr 2014 at 4:04am
but is the only difference the select statement?
I find it best to try and not make assumptions and do a real life test. I have spent many hours chasing the wrong 'problem'.
IP IP Logged
cbaldwin
Groupie
Groupie


Joined: 09 Apr 2014
Online Status: Offline
Posts: 81
Quote cbaldwin Replybullet Posted: 11 Apr 2014 at 5:07am
I think that is the ONLY change. I will investigate with my software rep. He has crystal experience.

thanks for the help.

To me it appears that any reference to querying by the null value results in that error in the system I uploaded to.
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.