CODEWITHSUNDEEP
cognoscognos-8cognos-bicognos-10
Excited_to_learn
Excited_to_learn

Reputation: 359

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

Upvotes: 2

Views: 15139

Answers (3)

Johnsonium
Johnsonium

Reputation: 2005

The simplest solution is to create what I call a 'month key' which is an integer representation of a year and month. You can then convert this to a char for comparison. It's actually quite simple:

extract(year, current_date) * 100 + extract(month, current_date)

Multiplying any number by 100 results in adding two zeroes to the end. The current year is 2014 so 2014 times 100 equals 201400. You are then free to add the month to get a 'month key'. 201400 + 6 = 201406. You can then convert this integer to a char and make your comparison. The final filter expression becomes:

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

Note: The technique of making integer 'keys' for dates can be extended to days as well and has many applications, namely sorting. The following expression will give you an integer 'day key' which retains the numerical order and hierarchy of the original date:

extract(year, current_date) * 10000 + extract(month, current_date) * 100 + extract(day, current_date)

Upvotes: 2

Alexey Baturin
Alexey Baturin

Reputation: 1173

Another formula

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

Upvotes: 3

Andrew
Andrew

Reputation: 8703

Two problems. The data type for your DateCol is character. The extract function is returning integers. Secondly, you aren't building your YYYYMM correctly with your extracts. The way you have it, you extract the year as a number, and add the month to it. You are ending up with something like '201406' = 2020, which isn't going to work. So you'll need to cast both extracts and concatenate those:

[DateCol] = cast(extract(year, current_date) as varchar(4)

+ cast (extract(month, current_date) as varchar(2))

EDIT: Actually, it'll probably be more complex than that. Extract won't return 06 for June, just 6. So you'll need to determine if that extract is one character or two. If it's one, you'll need to tack a '0' to the front after you cast it. The exact syntax will depend on your database, but something like this:

[DateCol] = cast(extract(year, current_date) as varchar(4)
+ case when cast (extract(month, current_date) as varchar(2) = 1
    then '0' + cast (extract(month, current_date) as varchar(2)
  else cast (extract(month, current_date) as varchar(2)
end

Upvotes: 1

Related Questions