Can I make (ALL) as a Parameter Option in a Report

Hi Ashley! No, there is not currently a way to create a "select all" option in those dropdowns, but users can use Ctrl+Shift+Click and it will select everything in a multi-select dropdown. Coming in v16, there will be a "Select All" option in multi-select report parameters, so that will be something to look forward to! 

Thank you!

Ashley

Isn't that functionally the same as selecting "None" for an optional parameter?

For all standard reports, yes.  For custom ones, it depends on how you write your SQL.  And I can certainly understand why someone could want "none", "all" and multi-selected other options for reports.

Ashley,

As John says, it's about how you write your stored procedure. For example, I have written reports for our Development team which have Funds as a parameter. This parameter is allowed to pass a NULL value and the SQL in the stored procedure handles this. Happy to show you how this works - feel free to email me at martin.keen@nida.edu.au if you would like an example.

Martin

Thank you! Email sent.

Ashley

When writing a report driven by a stored procedure or table function you will always want to do something like this to set the default values in case they are not supplied by the end user.

    Set @StartDate = isnull(@StartDate, dateadd(Day,-1, Getdate()))
    Set @EndDate = DBO.LFN_MBA_EOD(isnull(@EndDate,@StartDate ))
    Set @OriginIDs = isnull(@OriginIDs,'')
    Set @PriceTypeIds = isnull(@PriceTypeIds,'')
    Set @OrderIDs = isnull(@OrderIds,'')
    Set @PerfIDs = isnull(@PerfIDs,'')
    Set @OnlyZeroDollar = isnull(@onlyZeroDollar,0)

When writing a report where the query is embedded in the report you will want to do something like this:

Declare
@rStartDate                date,
@rEndDate                datetime,
@rTransactionID            Varchar(32),
@rAuthCode                varchar(30),
@rLast4                varchar(4),
@rAmount                    money


Set @rTransactionID = isnull(@TransactionID,'')
Set @rAmount = abs( ISNULL (@Amount,0))
Set @rAuthCode = isnull(@AuthCode,'')
Set @rLast4 = isnull(@Last4,'')
set @rStartDate = isnull(@StartDate,dateadd(Day,-7,getdate()))
Set @rEndDate = dbo.LFN_MBA_EOD (@EndDate)

The reason why the parameters should be transferred to local variables in the query is because SSRS does not pass the parameter data type to SQL which can cause the query to perform slower as it translates the value into the correct data type.

There are two ways you can handle a "select all" without requiring the end user to select all values.

Example 1:

Declare
@TranTypeIds            varchar(max) = '31,32,33'


Declare @tblTypeID table(
TranTypeID            int
primary key(tranTypeID))

Set @TranTypeIds = isnull(@tranTypeIds,'')

Insert into @tblTypeID
Select ID from TR_TRANSACTION_TYPE
where id in (Select Element  from DBO.FT_SPLIT_LIST (@tranTypeIds,','))
or len(@TranTypeIds) = 0

Select trn_type , Count(*) as RecCount from T_TRANSACTION as T
inner join @tblTypeID as TR on t.trn_type = tr.TranTypeID
group by trn_type

Results with values:

Results without:

Example 2:

Declare
@TranTypeIds            varchar(max) --= '31,32,33'


Declare @tblTypeID table(
TranTypeID            int
primary key(tranTypeID))

Set @TranTypeIds = isnull(@tranTypeIds,'')

Insert into @tblTypeID
Select ID from TR_TRANSACTION_TYPE
where id in (Select Element  from DBO.FT_SPLIT_LIST (@tranTypeIds,','))

Select trn_type , Count(*) as RecCount from T_TRANSACTION as T
where exists (Select 1 from  @tblTypeID as TR where t.trn_type = tr.TranTypeID)
or len(@TranTypeIds) = 0
group by trn_type

I prefer to use method two if the option list could be a very long list of records.

The other thing to keep in mind is that while the split function can be used directly in the query, it can result in poor query performance so it is better to place the data into a temp or variable table. My preference is variable tables they generally perform better for the majority of our query needs and don't require clean up.

Wow! Thank you.