[SOLVED] Cognos Report Studio (case statement)

Cognos Report Studio (case statement) - Syntax error

I have a case statement in Cognos report studio in which if the date is 1st of the current month of previous year then it should fetch data for last whole month (1 to the last date) data of the previous year. I think it is a syntax error. Below is the code that I'm sharing.Thanks in advance! Please let me know in case of concerns.

case when 
[Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD] = _first_of_month(_add_years(current_date,-1))
then 
[Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD] 
  between
_first_of_month(_add_months(_add_years(current_date,-1),-1))
   and
_last_of_month (_add_months(_add_years(current_date,-1),-1))
end

Solution

Your logic is correct. However, it looks like you are using a CASE statement in a filter. Cognos is picky about CASE syntax in filters. It requires you to put both the condition and the result in parentheses. Try this:

case when 
([Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD] = _first_of_month(_add_years(current_date,-1)))
then 
([Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD]
  between
_first_of_month(_add_months(_add_years(current_date,-1),-1))
   and
_last_of_month (_add_months(_add_years(current_date,-1),-1)))
end

Here's an alternate syntax that should also work:

extract(year,current_date) - 1 = extract(year, [Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD])
AND 
extract(month,current_date) = extract(month, [Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD])

==CORRECTION==

It's come to my attention that my answer is not quite correct even though it was accepted. Please see below for clarification.

The CASE WHEN I provided will generate an error because the condition references fluctuating values that vary by source row. When I constructed the answer I focused on the syntactical problems without evaulating the logic. In order for this type of logic to work, the condition right after CASE WHEN should reference a single value for the entire query. This can be a user-supplied parameter or a function or constant returning a single value for the entire query, such as:

(year(current_date) = 2018)

(?param? = 'foo')

The following is a very simple example that works in 10.2 and can be used as a template:

CASE 
WHEN (?param? = 'foo') 
THEN (extract(year,[Date]) = 2018)
ELSE (extract(year,[Date]) = 2017)
END

Similarly, my alternative logic is correct in form but not in logic because I again lifted the logic straight from the question without examination. The logic as constructed will never return true. A modified corrected version is as follows:

_first_of_month(current_date) = current_date /* Today is the first day of the month */
AND month(_add_months(current_date,-1)) = month([Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD]) /* The month of the row matches the previous month */
AND year(_add_years(current_date,-1)) = year([Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD]) /* The year of the row matches the previous year */

This will give you all of the previous month's data for last year if ran on the first day of any month. If you ran it in November 1, 2018, you'd get data for the entire month of October 2017. If you ran it January 1, 2018, you'd get data for the entire month of December 2016 etc.