Jump to content
Changes to the Jaspersoft community edition download ×

Keystone Ad hoc view - problem with custom filters


Recommended Posts

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?

Link to comment
Share on other sites

  • Replies 3
  • Created
  • Last Reply

Top Posters In This Topic

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?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...