Monday, October 6, 2008

COGNOS Report Studio 8.3 Filtering a TIME column and CASE WHEN

I had a report to complete, in which I had to filter in all appointments over 30 minutes. The metadata that I was using was a measure and it had both semi-aggregate and aggregate property as ‘SUM’.
Initially to filter the time column I used,
[time] > 30 , I got weird errors. I tries other functions like ‘contains’, ‘like’ and many more options. Later found out that the solution was very simple. The time should be filtered in the form of time only. I mean,
[time] > (000 00:30:00.000)
This took about 2 hours for me to figure it out. The other problem I had to face was using this [time] in a case when statement. Here the situation was that; I had to display in the rows as ‘8AM – 9 AM’ and so on. The format to filter here again changes, which took me about ½ an hour.
CASE WHEN
([Time] > 07:59:00.000 AND [Time] < 09:00:01.000) THEN '8AM - 9AM'
ELSE ‘FULL DAY'
END
So clearly, we don’t make use of that extra ‘000’ at the beginning of the time format. Crazy formats.