1

I am creating a stored procedure with a corresponding default value. It is using a default value of 2018 below.

create procedure rpt.FinanceReport
    @AccountYearParam int = 2018
as
select * from FinanceTable where AccountYear = @AccountYearParam

How do I pass the default Value into an SSRS report? So when the user selects nothing for parameter, it will display 2018.

"Using Null values", does not work, since Null will product a nothing. I want the SSRS report to display all values for 2018, if user selects nothing for parameter.

1

If you don't mind building the query as an expression (set the Query type to text and use the expression button to define the query), you can do something like this:

="EXEC MYPROC @PARAM=" &
 iif(isnothing(Parameters!ReportParameter1.Value),
     "DEFAULT",
     Parameters!ReportParameter1.Value)

This just says if the parameter is Nothing (null) use the keyword DEFAULT, otherwise use the parameter value.

| improve this answer | |
1

You could handle nulls inside the stored procedure with an isnull() like this:

create procedure rpt.FinanceReport
    @AccountYearParam int 
as

set @AccountYearParam = isnull(@AccountYearParam, 2018)
--
select * from FinanceTable where AccountYear = @AccountYearParam

and if you want to get really fancy, you can use this (it will return the current year):

create procedure rpt.FinanceReport
    @AccountYearParam int 
as

set @AccountYearParam = isnull(@AccountYearParam, datepart(year, getdate()))
--
select * from FinanceTable where AccountYear = @AccountYearParam  

You can also handle it in the where clause, although this method might be considerably slower:

create procedure rpt.FinanceReport
    @AccountYearParam int 
as
--
select * from FinanceTable where AccountYear = isnull(@AccountYearParam, datepart(year, getdate()))
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy