Multi select list default parameters

[pmcevoy]

Using BI server 3.5 and Report designer 3.5. I have defined a Multi select parameter for a report, taking values from the DB. I'd like the Multi select to default to ALL parameters being selected in the UI so that when opening the report it's obvious what the query is doing. I'm unsure how it's possible?

To get around the issue, I have defined a "-" parameter and defaulted to this, but this is not an ideal value in a multi-select as my SQL then looks like:

AND (
    '-'  IN (${PRODUCT_TYPE})
    OR
    Product.ProductType IN (${PRODUCT_TYPE})
)

which seems "hacky"

[ivazquez]

why don't you try something easier like..
mysql example

and ( ifnull({Param},'SelectALL') = 'SelectALL' OR field in {Param} )

and in Param query

Select 'Select all' as thing
from table
union
Select field as thing
from table


hope it helps you

[pmcevoy]

Well, that's pretty much what I have - my param query unions "-" with the values in the DB, I thenk set the "Default value" property of the param to be "-".

However do you not consider this a hack? I mean it would be better if I could default the front end to selecting all the fields in the multi select, and then I won't need that extra OR in my query.

(Although I concede that may not make the SQL more efficient as the IN would be evaluated for all rows)

[abhishekshankar]

can directly use
and country.region_id in (${param})

Using BI server 3.5 and Report designer 3.5. I have defined a Multi select parameter for a report, taking values from the DB. I'd like the Multi select to default to ALL parameters being selected in the UI so that when opening the report it's obvious what the query is doing. I'm unsure how it's possible?

To get around the issue, I have defined a "-" parameter and defaulted to this, but this is not an ideal value in a multi-select as my SQL then looks like:

AND (
    '-'  IN (${PRODUCT_TYPE})
    OR
    Product.ProductType IN (${PRODUCT_TYPE})
)

which seems "hacky"