Keystone Ad hoc view - problem with custom filters

[grobinson]

I’m adding multiple filters to an ad hoc view and using “is one of” in order to select multiple values from a list.  When I created a custom filter expression that says: (A or B) and C and D, the filters that use “is one of” (A or B) automatically force you to select a value for filters A and B; when deselecting all values, the error message “Select one or more items” appears.

Does anyone know a way around this other that creating the same report in Jaspersoft Studio and controlling the input controls and parameters in SQL?

[anish.rai]

Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.

[Raphaël Peguet]

This is not an issue specific to the filter expression but rather to the nature of the multi-select filters in AdHoc.
When you use multi-select, the Jaspersoft Domains generates a SQL query behind the scenes that uses an IN statement in the WHERE clause.

I tested something similar on my side and I got this query:

SELECT Sum("expense_fact3"."amount") AS "Sum_expense_fact3_amount", "expense_fact3"."exp_date" AS "expense_fact3_exp_date"FROM "public"."expense_fact" "expense_fact3"INNER JOIN "public"."store" "store3" ON (("store3"."store_id" = "expense_fact3"."store_id"))WHERE ((("store3"."coffee_bar" in ('False')) OR ("store3"."store_type" IN ('HeadQuarters', 'Gourmet Supermarket', 'Mid-Size Grocery', 'Small Grocery'))) AND ("store3"."store_country" IN ('Canada', 'USA')))GROUP BY "expense_fact3"."exp_date"ORDER BY "expense_fact3_exp_date" nulls first[/code]

AFAIK most if not all relational DB will not accept an IN NULL in the  WHERE clause.
I looked for a way to do a Coalesce but that would probably require to have the modification done in the Domain.

Alternatively, you can put the filter twice for the same field with the first one a 'is one of' and the second a 'not is one of'. Selecting all the values in the 'not is one of' will be the same as selecting nothing in the 'is one of', won't it?

[Raphaël Peguet]

I refer to https://stackoverflow.com/questions/6362112/in-clause-with-null-or-is-null in my previous comment.