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

cognos cognos-8 cognos-bi cognos-10
Excited_to_learn picture Excited_to_learn · Jun 27, 2014 · Viewed 13.8k times · Source

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

Answer

Alexey Baturin picture Alexey Baturin · Jun 27, 2014

Another formula

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

Related questions

In cognos, could we avoid an optional prompt asking a value when its parameter is used in a data item in a query?

Actually, I'm working in Cognos 10.1 report studio. In my report, I have two prompts, one is optional and another prompt is required. I've used both the prompt parameters inside the data item for some condition. So, the optional prompt has …

Read More
What is the difference between Query Studio and Report Studio in Cognos?

What is the difference between Query Studio and Report Studio in Cognos? Could anyone explain me in detail? Thanks in advance.

Read More
How to hide a column in a list, dynamically in cognos?

Actually I'm working on Cognos 10.1 . I have three prompts (Value prompt) in my report. All the prompts are optional. Based on the selections I wanna show the results. Each prompt corresponds to a column in the list. If the value …

Read More