Filter Condition

[diasbrad]

Hi Friends,

I am trying to create a conditional Filter.
Following is the expression:

CASE WHEN ([Relational Model].[Date].[Full Date]>[Relational Model].[Date].[Current Date])
THEN
([Relational Model].[FX Rates].[FX_Rate_Key]=[Relational Model].[Quote Line].[Primary_Rate_Key])
ELSE
([Relational Model].[FX Rates].[Rate Year] = Left([Relational Model].[Date].[Calendar Year/Month],4)
AND
[Relational Model].[FX Rates].[Rate Month]=Right([Relational Model].[Date].[Calendar Year/Month],2) )
END

When I apply this filter I get the following error. I am unable to understand what is this error. Can anyone help me fix this issue

RQP-DEF-0177

An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'. 
UDA-SQL-0358 Line 59: Syntax error near "=".RSV-SRV-0042 Trace back:RSReportService.cpp(717): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(258): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(848): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(305): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(904): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestExecution/RSRenderExecution.cpp(587): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(323): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(417): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(178): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(367): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(314): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(367): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(367): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(151): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(417): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSAssembly.cpp(677): QFException: CCL_RETHROW: RSAssembly::createListIteratorAssembly/RSAssembly.cpp(732): QFException: CCL_RETHROW: RSAssembly::createListIteratorRSQueryMgr.cpp(519): QFException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(586): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(678): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgrBasic.cpp(279): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrBasic.cpp(269): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(170): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(162): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1153): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1151): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1108): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1084): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(788): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4473): QFException: CCL_THROW: CoordinationPlanner

[Francis aka khayman]

your syntax is wrong. it should be something like:

Case when (condition = condition)
then (value) else (value)
end

[Lynn]

The message indicates a syntax error on line 59 near and equal sign. I don't exactly see which part of your statement is incorrect, but I would avoid using if/else or case logic in a filter expression.

Try to code an expression similar to below where only one block OR the other will evaluate to true and thus be enforced.

As a side note, the expression seems a lot like a join conditions which is surprising, but I assume your model is solid and you know what you're doing there.....

Code Select Expand


(
  [Relational Model].[Date].[Full Date] > [Relational Model].[Date].[Current Date]
  AND
  [Relational Model].[FX Rates].[FX_Rate_Key] = [Relational Model].[Quote Line].[Primary_Rate_Key]
)
OR
(
  [Relational Model].[Date].[Full Date] <= [Relational Model].[Date].[Current Date]
  AND
  [Relational Model].[FX Rates].[Rate Year] = Left([Relational Model].[Date].[Calendar Year/Month],4)
  AND
  [Relational Model].[FX Rates].[Rate Month] = Right([Relational Model].[Date].[Calendar Year/Month],2)
)


[diasbrad]

Thanks Khay/ Lynn for your reply.

Lynn I am only a developer . I don't have access to the FM and the database so I am not aware of how the FM model is structured. All the relationship is taken care at the FM level.

We have used Case statement and IF else condition in the filter and it worked. For some reason this condition is giving an error.

But I think your code will work here.

Thanks for your help. I will reply back with the out come after implementing this code

[diasbrad]

Hi Lynn,

Thanks for the help previously it did work awesomely well. I require you help for a similar query. This time the filter works but I want to see if I can re-structure the filter as block filter. i dont want to use the case syntax in the filter . the criteria here is if none of the condition are met all records should be populated.

Please find following code. Thanks for your help in advance.



CASE ?Date Type? WHEN 'Custom'
             THEN ([Relational Model].[Quote Line].[Forecast Calendar Date] in_range ?Dates?)
            
             WHEN 'MTD'
             THEN ([Relational Model].[Quote Line].[Forecast Calendar Date] BETWEEN _first_of_month(current_date) AND current_date)

             WHEN 'N'
             THEN  ([Relational Model].[Quote Line].[Forecast Calendar Date] BETWEEN current_date AND _add_days(current_date,?Next N Days?))

             WHEN 'QTD'
             THEN    (CASE ?Calendar Type? WHEN 'CY'
                                                     THEN 
               ([Relational Model].[Quote Line].[Forecast Calendar Date]
                                BETWEEN
                                 [Relational Model].[Quote Line].[Quote Line Current Quarter Start]
                                 AND current_date)

                                                    WHEN 'FY'
                                                  THEN
                                ([Relational Model].[Quote Line].[Forecast Calendar Date]
                                BETWEEN
                                [Relational Model].[Quote Line].[Quote Line Current Fiscal Quarter Start]
                                AND
                              current_date)
                                ELSE
                               (1=1)
                               END)
                 WHEN 'YTD'   
                 THEN    (CASE ?Calendar Type? WHEN 'CY'
                              THEN
                             ([Relational Model].[Quote Line].[Forecast Calendar Date]
                              BETWEEN
                              [Relational Model].[Date].[Current Year Start]
                               AND
                              current_date)

                             WHEN 'FY'
                             THEN
                             ([Relational Model].[Quote Line].[Forecast Calendar Date]
                              BETWEEN
                              [Relational Model].[Quote Line].[Quote Line Current Fiscal Year Start Date]
                              AND
                              current_date)
                              ELSE
                               (1=1)
                               END)
                
                                     WHEN 'Q'
                  THEN    (CASE ?Calendar Type? WHEN 'CY'
                               THEN
                               ([Relational Model].[Date].[Calendar Quarter Key]=?CQ?)
                  
                                WHEN 'FY'
                                THEN
               ([Relational Model].[Date].[Fiscal Quarter Key]=?FQ?)
               ELSE
               (1=1)
               END)
                  WHEN 'M'
                  THEN ([Relational Model].[Date].[Calendar Year/Month] = CASE WHEN ?CM? = '1'
                     THEN
                     (cast(current_date,varchar(7)))
                      ELSE
                      (?CM?)
                      END)

                 WHEN 'YTDP'
                 THEN ((?Calendar Type?='CY'
                           AND   
        (substring(cast([Relational Model].[Quote Line].[Forecast Calendar Date],varchar(10)),1,7)
        BETWEEN
        CAST(substring(?CM?,1,4), varchar(4)) || '-01'
        AND
       CAST(substring(?CM?,1,4), varchar(4)) || substring(?CM?,5,3)))
      
                                            OR
       
                                           (?Calendar Type?='FY'
       AND   
      (substring(cast([Relational Model].[Quote Line].[Forecast Calendar Date],varchar(10)),1,7)
      BETWEEN
      cast(substring(?FM?,1,4), varchar(4)) || '-02'
       AND
      cast(substring(?FM?,1,4), varchar(4)) || substring(?FM?,5,3))))
      
                WHEN 'SM'
                THEN ((?Calendar Type?='CY'   
           AND
         (substring(cast([Relational Model].[Quote Line].[Forecast Calendar Date],varchar(10)),1,7) in ( ?CM?)))
       
                                              OR
         (?Calendar Type?='FY'
         AND   
         (substring(cast([Relational].[Quote Line].[Forecast Calendar Date],varchar(10)),1,7)  in (?FM?))))

                  
              ELSE
              (1=1)
              END

Regards
Bradley

[BigChris]

Are you getting a problem with your code Bradley?

[diasbrad]

No Chris I am not getting a code error. I just got to know that it is not a best practice to use . IF THEN ELSE or CASE THEN ELSE END statements in Filter Condition.

I wanted to know if I can re-code the given filter condition using combination of 'OR' and 'AND' Logical Function.

Regards
Brad

[Lynn]

No Chris I am not getting a code error. I just got to know that it is not a best practice to use . IF THEN ELSE or CASE THEN ELSE END statements in Filter Condition.

I wanted to know if I can re-code the given filter condition using combination of 'OR' and 'AND' Logical Function.

Regards
Brad

It certainly should be possible to re-code that expression as distinct blocks of code to apply the desired filter criteria. If you are asking me to do that for you then I'll have to point out that it is a rather large and messy bunch of code with nested case statements that would take me quite some time to figure out and untangle since you don't give details about what the different prompt options are and how they relate to one another. Instead of explaining the business requirement it sounds like you want me to reverse engineer and re-write your code for you. I'm afraid I fall more into the "teach a person to fish" rather than the "give a person a fish" category.

You know what your logic is supposed to do, so my advice is to find each of the places where the filter is applied and work backwards through the nested conditions to figure out what makes it true. Armed with that information you can make it a separate block that will apply the filter condition only when the various prompt options are selected to dictate that it should be enforced. Use an OR operator between each of the blocks. When you finish, review to ensure that there is only one block of truth possible given all the different possible combinations of prompt options. This syntax will NOT stop evaluating once a true situation is found whereas a case statement will cease once a truth is found.

Code Select Expand


(
  ?DateType? = 'Custom'
  and
  [Relational Model].[Quote Line].[Forecast Calendar Date] in_range ?Dates?
)
OR
(
  ?DateType? = 'MTD'
  and
  [Relational Model].[Quote Line].[Forecast Calendar Date]
      between _first_of_month(current_date) AND current_date
)
OR
...
etc
etc
etc


[Michael75]

Lynn's reply is excellent, and I in no way wish to cast aspersions on diasbrad, but you should both bear in mind the following truism:

Give a man a fish and he will eat for a day. Teach a man to fish and he will sit in a boat and drink beer all day.

[Lynn]

 ;D ;D ;D ;D

[diasbrad]

 ;)