Use a List of Values in a Filter inside an Aggregate

Use a List of Values in a Filter inside an Aggregate

Hello I do have an aggregate and as one of the filters i would like to do something like string variable in (list of values (Record List.string field). Is that possible?


Hi Thiago,

Do you want add a filter which will take string value from a Record List and then search for the same?

Suraj Borade
Hi Thiago,

Unfortunately, there's no equivalent to SQL "IN" in an aggregate. It's a real miss, imho. If the list is limited, you'll get by with OR's, if its longer, you need to handcraft an Advanced Query.


Instead of using an aggregate, you can use an SQL block and pass the comma separated values to it as an input parameter and use this list for filtering the data. Just remember to mark the Expand Inline property of the query parameter to Yes. Refer the image below

For example, you want to filter the data for the selected products, you can create a comma separated string with all the selected values as "'Product A', 'Product B', 'Product C'". Please note that each value is enclosed in single quotes as the parameter type is text (you'll not need it for integers or decimals). Once you have this string, you can assign it to a variable and that variable could be passed as a query parameter to the SQL block. Once you have passed the value to the query parameter, set its Expand Inline property to yes and use this query parameter for filtering the data as below

SELECT * FROM {TableName} WHERE {TableName}.[ProductName] IN (@QueryParameter)

This should do the trick. Let me know if this solves your problem.