Good Morning All Syntax Experts,
1. I'm working to create a Report using Cognos v11.2.3.
2. I have a date/time combo data item named [Event Timestamp].
3. I need to include records with an Event Timestamp between "Yesterday from 7:00 PM through Today at
7:00 AM."
I know how to use the ( _add_days (current_date,-1)) expression to identify "yesterday"...and, now I'm stuck.
Anyone want to help me with the cheat code for the entire filter expression?
How about making it more flexible instead of hard coding it to work only for yesterday?
Add a DateTime prompt and use a simple filter in the query:
[Event Timestamp] in_range ?pDateTime?
You can also use two DateTime parameters to select the range
[Event Timestamp] >= ?pDateTimeFrom? and [Event Timestamp] <= ?pDateTimeTo?
OR
[Event Timestamp] between ?pDateTimeFrom? and ?pDateTimeTo?
The >=, <=, between will work depending on which type of database you have. 'between' usually works very well for SQL Server
Using only Cognos functions, this turned out to be much more difficult than I expected:
_add_hours(
_make_timestamp(
_year(_add_days(current_date, -1)),
_month(_add_days(current_date, -1)),
_day(_add_days(current_date, -1))
),
19
)
Maybe there's a more direct way, but I'm not seeing it quickly.
Thanks to both cognostechie and dougp for the inputs. I needed a calculated value - one that would not prompt the user to make choice. So, I ended up with the following design on 3/15 which seems to work:
1. Defined "Yesterday". cast_date(_add_days ((current_date), -1))
2. Defined "Today". cast_date (current_date)
3. Defined Event Date. cast_date ([Event Timestamp])
4. Defined Event Time. cast_time ([Event Timestamp])
5. Filtered on multi-part date and time comparisons within a single expression.
([Event Date] = [Yesterday]
AND
[Event Time] between 19:00:00 and 23:59:59)
OR
([Event Date] = [Today]
AND
[Event Time] between 00:00:00 and 07:30:00)
This worked in my instance. So I'll mark this post as "Resolved!"