Counting Null 1

[ekta22]

Hi,

I have a formula in my detail report called LateComplete. I use this formula to insert a summary in my Group header. Report is based on two tables - LOG table and COMMENTS table. Each record can have no comments, one or more comments associated with it. What is happening right now is that if a record has 2 comments associated with it, that record is counted twice. This is my original formula

If ({LOG.COMPLETION_STATUS}) = "X" AND Date({LOG.END_DATE_TIME}) > {LOG.LATEST_DATE} AND 
({LOG.MAINT_ACTION_CODE}) = "P" 
 then
    ({LOG.NO_TASKS})

To fix this problem I modified the formula by using COMMENT_SEQUENCE. Each time A COMMENT is added for a record in a table it is assigned sequence 1 and so on. As I have a left outer join on comments table, if there are no comments for a record it displays null under comments for that record.

If ({LOG.COMPLETION_STATUS}) = "X" AND Date({LOG.END_DATE_TIME}) > {LOG.LATEST_DATE} AND 
({LOG.MAINT_ACTION_CODE}) = "P" 
[b]AND  (isNull({COMMENTS.COMMENT_SEQUENCE}) OR {COMMENTS.COMMENT_SEQUENCE} = 1)[/b]
 then
    ({LOG.NO_TASKS})

Now it does not count the record twice but it is skipping all those records where comment is null. How should I make it count the records that have no comments associated with it.

Thanks,

Ekta

 

[dgillz]

Make the null test the very first part of your code. I don't know why but this is a requirement when working with nulls.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[lbass]

I think you should verify that the {comments.comment_sequence} is null versus equal to 0, by adding a formula to your detail section. You could also try:

if
(
isNull({COMMENTS.COMMENT_SEQUENCE}) OR
{COMMENTS.COMMENT_SEQUENCE} in [0,1]
) and
{LOG.COMPLETION_STATUS} = "X" AND
Date({LOG.END_DATE_TIME}) > {LOG.LATEST_DATE} AND
{LOG.MAINT_ACTION_CODE} = "P" then
then {LOG.NO_TASKS}

-LB







-LB

 

[ekta22]

Putting it at the beginning didn't do anything. I get the same results. I verified and comment_sequence is null not 0.

 

[lbass]

If you place the following in the detail section, what results do you get?

if isNull({COMMENTS.COMMENT_SEQUENCE}) then {LOG.NO_TASKS}

Can {Log.No_tasks} also be null?

-LB

 

[ekta22]

Hi,

I am sorry. My bad. I checked the values for comment_sequence in the database where is displays it as null but in the report it displays 0. It seems to be working just fine now.. Thanks so much.