SSRS 两个parameter- 问题已经解决
1
Data Source: AdventureWorks2012
Create StateYeardetail report, design Dataset Details query use following Tables:
Person.Address, Person.StateProvince, Sales.SalesOrderHeader
Dataset Details fields:
CountryRegionCode, StateProvinceCode AS State, TotalDue , AddressLine1, City, PostalCode, year of DueDate
Dataset Details filter:
only display records for CountryRegionCode =US,
TotalDue>3000,
parameters: State,Year
报表显示效果图：
按照要求，我写了三个不同的query:
哪里出错了？
--问题找到了，应该query里面把两个parameter的条件加一下：
SELECT p.CountryRegionCode,p.StateProvinceCode AS State, h.TotalDue
,a.AddressLine1,a.City,a.PostalCode,year(h.DueDate) as DueYear
FROM Person.Address AS a
INNER JOIN Person.StateProvince p ON a.StateProvinceID = p.StateProvinceID
INNER JOIN Sales.SalesOrderHeader h ON a.AddressID = h.BillToAddressID
WHERE p.CountryRegionCode = 'US'
and h.TotalDue>3000
and p.StateProvinceCode= @State
and year(h.DueDate)=@year
Create StateYeardetail report, design Dataset Details query use following Tables:
Person.Address, Person.StateProvince, Sales.SalesOrderHeader
Dataset Details fields:
CountryRegionCode, StateProvinceCode AS State, TotalDue , AddressLine1, City, PostalCode, year of DueDate
Dataset Details filter:
only display records for CountryRegionCode =US,
TotalDue>3000,
parameters: State,Year
报表显示效果图：
按照要求，我写了三个不同的query:
--StateYearDetail--parameter @State @Year我出来的显示2008年WA的数据超过5行：
SELECT p.CountryRegionCode,p.StateProvinceCode AS State,YEAR(h.DueDate) AS [Due Year], h.TotalDue,a.AddressLine1,a.City,
a.Postalcode
FROM Person.Address AS a
INNER JOIN Person.StateProvince p ON a.StateProvinceID = p.StateProvinceID
INNER JOIN Sales.SalesOrderHeader h ON a.AddressID = h.BillToAddressID
WHERE p.CountryRegionCode = 'US' AND h.TotalDue>3000
ORDER BY 1,2,3
--DY--parameter@State
SELECT DISTINCT p.StateProvinceCode AS State
FROM Person.Address AS a
INNER JOIN Person.StateProvince p ON a.StateProvinceID = p.StateProvinceID
INNER JOIN Sales.SalesOrderHeader h ON a.AddressID = h.BillToAddressID
WHERE p.CountryRegionCode = 'US' AND h.TotalDue>3000
ORDER BY 1
--Year--parameter @Year
SELECT DISTINCT YEAR(h.DueDate) AS [Due Year]
FROM Person.Address AS a
INNER JOIN Person.StateProvince p ON a.StateProvinceID = p.StateProvinceID
INNER JOIN Sales.SalesOrderHeader h ON a.AddressID = h.BillToAddressID
WHERE p.CountryRegionCode = 'US' AND h.TotalDue>3000
ORDER BY 1
哪里出错了？
--问题找到了，应该query里面把两个parameter的条件加一下：
SELECT p.CountryRegionCode,p.StateProvinceCode AS State, h.TotalDue
,a.AddressLine1,a.City,a.PostalCode,year(h.DueDate) as DueYear
FROM Person.Address AS a
INNER JOIN Person.StateProvince p ON a.StateProvinceID = p.StateProvinceID
INNER JOIN Sales.SalesOrderHeader h ON a.AddressID = h.BillToAddressID
WHERE p.CountryRegionCode = 'US'
and h.TotalDue>3000
and p.StateProvinceCode= @State
and year(h.DueDate)=@year
没有找到相关结果
重要提示：提问者不能发表回复，可以通过评论与回答者沟通，沟通后可以通过编辑功能完善问题描述，以便后续其他人能够更容易理解问题.
3 个回复
天善_小龙 2016-04-29 回答
赞同来自:
漂在华盛顿WA 2016-04-30 回答
赞同来自:
姜不辣同学 - 90后IT男。。。。 2016-05-03 回答
赞同来自: