Hi Cognos community,
I have a question regarding Detail Filters in Report Studio. We have a reports which directly calls underlying database. I found out that a lot of these reports are using case or if statements in Detail Filters. It works fine, but today I found a one report which gives me an error: 

QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.UDA-SQL-0358 Line n: Syntax error near "=".

So, I found some recommendations on the internet which declare, that case or if statements should not be part of Detail Filters. But these recommendation are quite old. Most of them are related to Cognos 10 version. So my question is - Is this still relevant to Cognos Analytics 11.1.7 and later? Are there any whitepapers how the developers should write detail filters? Or do you have any suggestions? 

Thank you for your answers!

------------------------------
Jiri Smrz
------------------------------

#CognosAnalyticswithWatson

Re your error, suggest you open a support call, they can help you re SQL statement that failed to prepare due to a syntax issue etc.

Re your second comment about using conditional expressions, you can use them in filters. In terms of predicates (filters), there are general best practices re SQL, some of which are outlined in section 6 http://www.redbooks.ibm.com/abstracts/sg248121.html?Open.

------------------------------
NIGEL CAMPBELL
------------------------------

Original Message:
Sent: Wed November 03, 2021 04:17 AM
From: Jiri Smrz
Subject: Report Studio Detail Filters structure

Hi Cognos community,
I have a question regarding Detail Filters in Report Studio. We have a reports which directly calls underlying database. I found out that a lot of these reports are using case or if statements in Detail Filters. It works fine, but today I found a one report which gives me an error: 

QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.UDA-SQL-0358 Line n: Syntax error near "=".

So, I found some recommendations on the internet which declare, that case or if statements should not be part of Detail Filters. But these recommendation are quite old. Most of them are related to Cognos 10 version. So my question is - Is this still relevant to Cognos Analytics 11.1.7 and later? Are there any whitepapers how the developers should write detail filters? Or do you have any suggestions? 

Thank you for your answers!

------------------------------
Jiri Smrz
------------------------------

#CognosAnalyticswithWatson