CODEWITHSUNDEEP
reportcognos-8
beebris
beebris

Reputation: 55

Cognos Report studio handling null value

I am using cognos report studio 8.2. The requirement for one report is to filter a user specified date within the start and end date of an project. But the end date can be null value as it can be an project not have a finishing date. Here is my scenario:

Event || Start-date || End-date

Project1 || 02-feb-2013 || 10-feb-2013

Project2 || 11-feb-2013 || NULL

Here is what I need my output to look like:

Filter: 03-feb-2013 Output: Project 1

Filter: 12-feb-2013 Output: Project1 & Project 2

I tried this filter:

Filter1:

([END_DATE] is not NULL) AND ([END_DATE] > ?asAtDate?)

Filter2:

([START_DATE] <= ?atDate?)

But that didn't work, it just gave me a blank report. What am I doing wrong? If I don't add the [END_DATE] is not null, then it gives me a sql exception error.

Thanks for all your help.

Upvotes: 1

Views: 6312

Answers (1)

toddsonofodin
toddsonofodin

Reputation: 513

I would take care of this at the model level. In the SQL statement for the table, coalesce the null values with a date far in the future (1-1-2100 or similar). Then a standard between filter would work. I would always do this at the model level, rather than the report. Then reports can be quickly created or the package queried ad-hoc with no difficulty.

Upvotes: 1

Related Questions