Parameter default to all every time

Question

I created a drop down menu by getting values from a query. I created so that every time a the user can select one department name or all option. Also, i havent added any dafault values.

select distinct departmentid, name from department
union
select Null, 'ALL'
order by department

Now when I tried to preview the report. It by default selects "ALL" option and runs the report. How do I make it let me choose the option. I am fairly new to SSRS and I am not sure what went wrong.

edited: I reason I use NUll is because department id is uniqueidentifer and I wouldn't let me use int values. Also, when I created the parameter i set the parameter to allow null values to let me see if the 'ALL' option in the parameter. Now, when I take out the allow null values, it doesn't default.

One side note : distinct is unnecessary whenever you are using union as because union will remove duplicate for you. — Yogesh Sharma
@hiten004 I already looked at this site. i dont want to use select all option. — M.Doe
in ssrs parameter make default '9999' for this select distinct departmentid, name from department union select 9999, 'ALL' order by department — Hiten004
@Hiten004 - I usually use 0 for an ID integer since there's rarely an ID of 0. — Hannover Fist

Hiten004 Hiten004 · Accepted Answer · 2019-03-14T19:41:00

in SSRS, Please select report parameter default 0 so when the report will render default "all" will be selected.

select distinct departmentid, name 
from department 
union 
select 0, 'ALL' 
order by department

Source website: https://www.mssqltips.com/sqlservertip/3506/set-select-all-as-default-for-multivalue-report-parameters-in-sql-server-reporting-services/

Parameter default to all every time

1 Answers