How to add parameters for multiple agents to exclude from the SSRS Stats Report

Question

Scenario: I have a report that pulls up stats for agents for a certain date range (usually the same day, yesterday) which I've set as parameters. I've built the report in Visual Studio Report builder and the report works great.

Challenge: Some agents are taken off calls and work on emails instead that day. So, though they are logged in, they can't be added to the call queue stats.

Need: I need to add parameters which can select agents from the drop down (Ctrl+select) and have the report run where employeeID NOT IN the ones that were selected. Then I need to add those parameters in the report builder to show as a drop-down so the user can ctrl+select the agents to exclude.

Question: I know how to create 1 parameter to exclude 1 agent. How do I create parameter(s) to exclude more than 1 agent? Sometimes, it can be 0 agents, sometimes they are 10.

I'm using MS SQL Server 2014.

Answer 1

You need to add a parameter to your dataset query e.g. Assuming some very simple tables for illustration with AgentID being a unique key of Agents.

SELECT {your list of columns} FROM AgentCalls 
WHERE 
(AgentID NOT IN (@myParamName) OR ISNULL(AgentID,''))

This will show all Agents if nothing is selected or it will exclude agents if any are selected.

You then need to populate the list of available items for your parameter, in this case it will be a list of Agents. So create another dataset called, say dsAgents With a query something like

SELECT AgentID, AgentName FROM Agents ORDER BY AgentName

In the parameter settings, make the parameter multi-value and also allow blank & null, in available values, choose dsAgents as the query, use the AgentID field as the Value and the AgentName field as the Label.

SSRS will automatically convert the list of selected parameter values into a comma separated list and use in the IN clause, there is nothing more you need to do.

P.S. this is from memory at 1am so it might not be spot on but it should be good enough to follow.

Note: there is no need to hold control the parameter list will show checkboxes.