Have parameter default to null

Question

My SSRS report has a dataset using this query

select c1, c2 
from t1 
where c3 = isnull(@parameter1, c3)

In the report, I set the parameter (@parameter1) to 'Allow null value' and default value to null. The parameter will from another data set.

I want the report to run automatically with the default null value parameter. But now the report is still waiting for the parameter to be selected to run. Why? Or how to achieve what I want?

P.S

Also please find attached screen shots for the parameter and the query. This is not exactly the same as in the description because I was just trying to demonstrate the issue. But you get the idea.

enter image description here

The query:

    select
    t.a, 
    t.b

    from
    (
    select 1 as a, 2 as b
    union all

    select 3 as a, 4 as b
    ) as t
    where t.a =isnull( @p, t.a)

Perhaps you are experiencing [this issue](http://stackoverflow.com/q/14701233/419956)? — Jeroen, Oct 08 '14 at 12:33
A screenshot of how you are setting the default value to NULL would be helpful. — Bryan, Oct 08 '14 at 13:22
@Jeroen I am working in BIDS, not in the stage of deploying to report server. — thotwielder, Oct 08 '14 at 13:29
Looks like you've got it right, I'm unable to reproduce in BIDS 10.50.4297.0 with the parameter options and dataset you specified. — Bryan, Oct 08 '14 at 13:45

score 1 · Accepted Answer · answered Oct 09 '14 at 05:19

The query for the Available Values of your parameter needs to have a Null option for the default value of Null to be selected.

So your query that populates the parameter needs to something like

SELECT A, B
FROM MyTable
UNION ALL
SELECT Null AS A, '<All Values>' AS B
ORDER BY 2

where A is the Value and B is the Label.

score 0 · Answer 2 · answered Jul 22 '16 at 15:54

0

If Visual Studio shows default parameters well but SSRS site does not - DELETE the SSRS site report & deploy

answered Jul 22 '16 at 15:54

Jairo

1

Have parameter default to null

2 Answers2