I'm trying to do the following for certain pay period combinations, I need the fiscal year prompt (?FY?) to be the input minus one, but I can tell cognos doesn't like my syntax. Does anyone know of a way I can accomplish this?
CASE
WHEN [PICT_PAY] = 'MN10'
THEN [PHRCMNT_YEAR] = ?FY?-1
WHEN [PICT_PAY] = 'MN11'
THEN [PHRCMNT_YEAR] = ?FY?-1
WHEN [PICT_PAY] = 'MN12'
THEN [PHRCMNT_YEAR] = ?FY?-1
WHEN [PICT_PAY] = 'MN01'
THEN [PHRCMNT_YEAR] = ?FY?-1
WHEN [PICT_PAY] = 'SM18'
THEN [PHRCMNT_YEAR] = ?FY?-1
WHEN [PICT_PAY] = 'SM19'
THEN [PHRCMNT_YEAR] = ?FY?-1
WHEN [PICT_PAY] = 'SM20'
THEN [PHRCMNT_YEAR] = ?FY?-1
WHEN [PICT_PAY] = 'SM21'
THEN [PHRCMNT_YEAR] = ?FY?-1
WHEN [PICT_PAY] = 'SM22'
THEN [PHRCMNT_YEAR] = ?FY?-1
WHEN [PICT_PAY] = 'SM23'
THEN [PHRCMNT_YEAR] = ?FY?-1
WHEN [PICT_PAY] = 'SM24'
THEN [PHRCMNT_YEAR] = ?FY?-1
WHEN [PICT_PAY] = 'SM01'
THEN [PHRCMNT_YEAR] = ?FY?-1
ELSE [PHRCMNT_YEAR] = ?FY?
END
The predicate condition can't be contained inside the case statement. Case statements can be on either side of the operator.
Try instead,
[PHRCMNT_YEAR] = case when [PICT_PAY] in ( 'MN10', 'MN11', 'MN12', 'MN01', 'SM18', 'SM19', 'SM20', 'SM21', 'SM22', 'SM23', 'SM24', 'SM01') then ?FY?-1 else ?FY? end
Quote from: CognosPaul on 29 Jan 2014 12:50:27 PM
The predicate condition can't be contained inside the case statement. Case statements can be on either side of the operator.
Try instead,
[PHRCMNT_YEAR] = case when [PICT_PAY] in ( 'MN10', 'MN11', 'MN12', 'MN01', 'SM18', 'SM19', 'SM20', 'SM21', 'SM22', 'SM23', 'SM24', 'SM01') then ?FY?-1 else ?FY? end
You sir, are my hero. I got so wrapped up in the parm -1 business that I missed what was right in front of me.