The report is filtering on below prompt:
[XYZ].[ABC].[Yearmonth] = TO_NUMBER (TO_CHAR (TRUNC ({SYSDATE}, 'MM')-1, 'YYYYMM'))
I want to change report with two results – if run date = first of the month then last month sales else current month sales.
here I dont want to use year Prompt. Emp number Promt is already there. If I give EMP number I should get data based on the present date.
Can any one help me in implementing the filter.
I tried below query but throwing errors.
IF ( _first_of_month ({sysdate})) THEN
( [YEARMONTH]= TO_NUMBER (TO_CHAR (TRUNC ({SYSDATE}, 'MM')-1, 'YYYYMM')))
ELSE
([YEARMONTH] = TO_NUMBER (TO_CHAR (TRUNC ({SYSDATE}, 'MM'), 'YYYYMM')) )
Error:RQP-DEF-0177
An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
Thanks in advance!
Hello Priya,
You filter definition should be:
[YEARMONTH]= IF ({sysdate}= _first_of_month ({sysdate})) THEN
( TO_NUMBER (TO_CHAR (TRUNC ({SYSDATE}, 'MM')-1, 'YYYYMM')))
ELSE
( TO_NUMBER (TO_CHAR (TRUNC ({SYSDATE}, 'MM'), 'YYYYMM')) )
Hope it helps,
Cognos810