Jump to content

Recommended Posts

Posted

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?

  • Replies 3
  • Created
  • Last Reply

Top Posters In This Topic

Posted

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.

Posted

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?

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...