Department Parameter SSRS

0
votes

In a department table I have 3 options. Sales, Marketing and HR.

I have a data set in SSRS as follows

select isnull(Name,'Not Applicable') as [Department]
From TableA
where Name in (@Departmentname)

Now I want a SSRS REPORT parameter that gives me 4 options when I run the report ,Sales, Marketing, HR and no Department Linked so set this dataset to link to parameter.

select 'No Department Link' as [Name]
union
select [Name] from TableA

Now when I select the 'No Department Link' option on the list for the Department Report Parameter it gives me no results, as in the dataset there is no columns with 'No Department Link' on them , they are null. So how do I change my dataset so this works with all 4 options on the report parameter and not just the first 3 , Sales , Marketing and HR. Change the nulls in the dataset to 'No Department link' before I query it somehow.

Is a change to the actual clause in the data set ? I think the report parameter is fine.

Is there a really easy way to do this I am not seeing ?

2
sql-serverreporting-services

2 Answers

1
votes
select isnull(Name,'Not Applicable') as [Department]
From TableA
WHERE Name IN (@Departmentname)
 OR NULLIF(@Departmentname, 'No Department Link') IS NULL
1
votes

Use this query for your dataset :

select isnull(Name,'Not Applicable') as [Department]
From TableA
where Name in (@Departmentname)
or (@DepartmentName like 'No Department Link' and Name is null)