Ok, so I managed to pull it off.

Basically you were on the right track, but there is some extra magic to it.

So instead of entering your query in the Query field (in Dataset Properties), you use the formula builder (click on the fx button next to the query field).

In the formula builder you enter your query as you already mentioned:

= "SELECT *  

FROM tmp_result 

WHERE (profileArea IN ('" & Join(Parameters!ProfileArea.Value, "', '") & "'))"

The trick is to put the whole query into double quotes and using the formula builder.

When you exit the formula builder, the query field will show "<<Expr>>", so you can't see the actual query.

But this approach works for me.