cognoscognos-8cognos-bicognos-10

Automatic filter for current month and year in Cognos Report Studio ver 10

I have a query item which filters records by a date column which is in format 'YYYYMM'. As of now I have hard coded the value [DateCol] = '201406' in Detail filters to get the current month and year records. But I want to dynamically have this value change. For next month it should filter records by '201407'. How do I achieve this? I tried this by dragging the [DateCol] in a Detail filters:

[DateCol] = extract(year, current_date) + extract(month, current_date)

But it returned an error:

UDA-SQL-0460 The operation "equal" is invalid for the following combination of data types: Character and Integer

Please guide

Solution

  • Another formula

    [DateCol] = cast(extract(year, current_date) * 100 + extract(month, current_date) as varchar(6))