SSRS参数设置

Question

我是SSRS的新手，想要一些帮助来设置一个参数。

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

我希望有一个名为:具有下列部门可用值的部门: 1,2,3

我的查询如下所示：

(1,2,3)中的oi.departmentid

我不知道如何设置参数以获得相同的结果。

Answer 1

这基本上就是您所做的，转到参数并添加参数，选择类型编号，选择，允许多个。在“可用值”下，选择“指定值”，并列出每个值。注标签是用户将看到的内容，而值则是将传递给SQL的内容。在您的代码中也要更改它。

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

注意，您的参数名称没有@，但是您的代码必须。另外，SSRS是区分大小写的，所以一定要匹配。

Answer 2

和上面的海报一样，我发现了这个：

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