Case Statement in Filter doesn't work?

[PRIT AMRIT]

The below filter condition has been working fine and the report was running fine in PROD as well.

case when (?AcctMonth? = '12')
then ([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] = ?AcctYear? and [DBLayer].[RPT_QS_GPW_SUMMARY].[acct_month] = '12')
else (([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] = ?AcctYear? and
       cast_integer([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_month]) <= cast_integer(?AcctMonth?))
       or
      ([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] = cast_char(cast_integer(?AcctYear?) - 1) and
       [DBLayer].[RPT_QS_GPW_SUMMARY].[acct_month] = '12')
     )
end

Now, as per the CR, i have to Replace the ?AcctMonth? by [Month] and ?AcctYear? by [Year] DATA ITEM. which is not working at all.

when i put case when [Month] = '12' ....... it is not validating at all.... could you please show me a way to overcome the same....

This is very very urgent...

Thanks
P

[MFGF]

What error message do you get?  Or do you get no error but the wrong filtered results?

MF.

[blom0344]

If you replace the parameter value by a dataitem then you are effectively changing the nature of the entire expression. Instead of evaluating   (?AcctMonth? = '12') as true or false for the entire following expression, changing this to a dataitem would require a row by row evaluation. I wonder whether SQL will permit such an approach..

[PRIT AMRIT]

hi MF,

i am getting the below error msg


RQP-DEF-0177

       An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.

UDA-SQL-0358 Line 8: Syntax error near "=". RSV-SRV-0042 Trace back: RSReportService.cpp(760): QFException: CCL_CAUGHT: RSReportService::process() RSReportServiceMethod.cpp(241): QFException: CCL_RETHROW: RSReportServiceMethod::process(): promptPagingForward_Request RSASyncExecutionThread.cpp(766): QFException: RSASyncExecutionThread::checkException RSASyncExecutionThread.cpp(211): QFException: CCL_CAUGHT: RSASyncExecutionThread::run(): promptPagingForward_Request RSASyncExecutionThread.cpp(816): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): promptPagingForward_Request Execution/RSRenderExecution.cpp(615): QFException: CCL_RETHROW: RSRenderExecution::execute Assembly/RSDocAssemblyDispatch.cpp(263): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSLayoutAssembly.cpp(64): QFException: CCL_RETHROW: RSLayoutAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(330): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForward Assembly/RSReportPagesAssembly.cpp(163): QFException: CCL_RETHROW: RSReportPagesAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(280): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSPageAssembly.cpp(287): QFException: CCL_RETHROW: RSPageAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(280): QFException


Can i do like below

Data Item 2:
-------------------
[Last Month] = '12'
and [DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] = [Last Acct Year]
and [DBLayer].[RPT_QS_GPW_SUMMARY].[acct_month] = '12'

Data Item 1:
---------------

[Last Month]<>'12' and
[DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] = [Last Acct Year] and
       cast_integer([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_month]) <= cast_integer([Last Month])
       or
[DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] = cast_char(cast_integer([Last Acct Year]) - 1) and
       [DBLayer].[RPT_QS_GPW_SUMMARY].[acct_month] = '12'


In the Filter i will Put:
-------------
Data Item 1
or
Data Item 2

Will it show the desired result?

Would really appreciate your help on this.

Thanks
P

[PRIT AMRIT]

Made it work with the below CODE... Had to spend hell out of time with   in and, or , ().... 



[Last Month]='03'

         and (

         cast_integer([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] )= cast_integer(substring([DBLayer].[RPT_QS_GPW_SUMMARY].[last_acct_period],1,4))-1

         and [acct_month] = '03' )



or

[Last Month]='04'

         and (

         cast_integer([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] )= cast_integer(substring([DBLayer].[RPT_QS_GPW_SUMMARY].[last_acct_period],1,4))-1

         and [acct_month] in ('03' ,'04') )

or

[Last Month]='05'

         and (

         cast_integer([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] )= cast_integer(substring([DBLayer].[RPT_QS_GPW_SUMMARY].[last_acct_period],1,4))-1

         and [acct_month] in ('03' ,'04','05') )

or



[Last Month]='06'

         and (

         cast_integer([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] )= cast_integer(substring([DBLayer].[RPT_QS_GPW_SUMMARY].[last_acct_period],1,4))-1

         and [acct_month] = '06' )



or

[Last Month]='07'

         and (

         cast_integer([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] )= cast_integer(substring([DBLayer].[RPT_QS_GPW_SUMMARY].[last_acct_period],1,4))-1

         and [acct_month] in ('06' ,'07') )

or

[Last Month]='08'

         and (

         cast_integer([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] )= cast_integer(substring([DBLayer].[RPT_QS_GPW_SUMMARY].[last_acct_period],1,4))-1

         and [acct_month] in ('06' ,'07','08') )

or



[Last Month]='09'

         and (

         cast_integer([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] )= cast_integer(substring([DBLayer].[RPT_QS_GPW_SUMMARY].[last_acct_period],1,4))-1

         and [acct_month] = '09' )



or

[Last Month]='10'

         and (

         cast_integer([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] )= cast_integer(substring([DBLayer].[RPT_QS_GPW_SUMMARY].[last_acct_period],1,4))-1

         and [acct_month] in ('09' ,'10') )

or

[Last Month]='11'

         and (

         cast_integer([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] )= cast_integer(substring([DBLayer].[RPT_QS_GPW_SUMMARY].[last_acct_period],1,4))-1

         and [acct_month] in ('09' ,'10','11') )



OR

([Last Month]='12'

and (

cast_integer([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year])=cast_integer(substring([DBLayer].[RPT_QS_GPW_SUMMARY].[last_acct_period],1,4))

and

[acct_month]=[Last Month])

)



OR

[Last Month]='01'

and ( (

cast_integer([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year])=cast_integer(substring([DBLayer].[RPT_QS_GPW_SUMMARY].[last_acct_period],1,4))-1

and

[DBLayer].[RPT_QS_GPW_SUMMARY].[acct_month]=[Last Month])

or

[DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] = cast_char(cast_integer([Last Acct Year]) - 2) and

       [DBLayer].[RPT_QS_GPW_SUMMARY].[acct_month] = '12')



OR

[Last Month]='02'

and ((

cast_integer([DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year]) =cast_integer(substring([DBLayer].[RPT_QS_GPW_SUMMARY].[last_acct_period],1,4))-1

and

[acct_month] in ('01','02')

)

or

[DBLayer].[RPT_QS_GPW_SUMMARY].[acct_year] = cast_char(cast_integer([Last Acct Year]) - 2) and

       [DBLayer].[RPT_QS_GPW_SUMMARY].[acct_month] = '12')




Thank u all...
p