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
What error message do you get? Or do you get no error but the wrong filtered results?
MF.
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..
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
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