SSRS Parameter Setup

Question

I am new to SSRS and would like some help setting up a parameter

select cast(i.invoicedatetime as date) as Date, oi.DepartmentID, departmentname, SubDepartmentName, sum(Quantity*each*UnitPrice-o.DiscountAmount-i.DiscountAmount) as Sales
from InvoiceInfo i, 
    orderinfo o, 
    OrderItemInfo oi, 
    DepartmentInfo d, 
    SubDepartmentInfo s
where i.InvoiceID = o.InvoiceID
and o.orderid = oi.OrderID
and oi.DepartmentID = d.DepartmentID
and oi.SubDepartmentID = s.SubDepartmentID
group by DepartmentName, SubDepartmentName, InvoiceDateTime, oi.DepartmentID
order by DepartmentName

I would like to have a parameter called: Departments with a available value of the following departments: 1,2,3

My query looks like this to get the data:

where oi.departmentid in (1,2,3)

I am jus not sure how to setup the parameter to give the same results.

Answer 1

Like the above poster, I found this:

http://dataqueen.unlimitedviz.com/2013/10/how-to-use-a-delimited-string-in-a-multi-valued-parameter/

Answer 2

This is basically what you do, go to parameters and add parameter, Choose type Number, the select, allow multiple. Under availble values, choose specify values, and list each one. note Label is what the user will see and Value is what will get past to SQL. In your code change it too

select cast(i.invoicedatetime as date) as Date, oi.DepartmentID, departmentname, SubDepartmentName, sum(Quantity*each*UnitPrice-o.DiscountAmount-i.DiscountAmount) as Sales
from InvoiceInfo i, 
    orderinfo o, 
    OrderItemInfo oi, 
    DepartmentInfo d, 
    SubDepartmentInfo s
where i.InvoiceID = o.InvoiceID
and o.orderid = oi.OrderID
and oi.DepartmentID in (@Departments)
and oi.SubDepartmentID = s.SubDepartmentID
group by DepartmentName, SubDepartmentName, InvoiceDateTime, oi.DepartmentID
order by DepartmentName

Note a few things, your parameter name does not have @ but your code must. Also SSRS is case sensitive so be sure to match.