SSRS - 包含/排除某些记录的参数
问题描述:
我们有一个数据集,其中包含一个名为“取消”的字段,它是一个布尔字段。我希望报告具有一个名为@includecancelled作为布尔值的参数。用户可以将其标记为True或False来控制是否在报告中包含取消的记录。到目前为止,我还没有找到办法做到这一点。这是我目前拥有的基本轮廓:SSRS - 包含/排除某些记录的参数
SELECT TOP 10 COUNT (table1.field1) as Count_field1
,table2.fielda
,table2.fieldb
FROM
table1
INNER JOIN table2
ON table1.field1 = table2.fieldc
WHERE
table1.field2 in (@param1)
GROUP BY
table2.fielda
,table2.fieldb
ORDER BY COUNT (table1.field1) DESC
我想有WHERE子句中的另一语句来排除或包括基于“取消”字段和用户选择的参数记录。所以,这样的事情:
SELECT TOP 10 COUNT (table1.field1) as Count_field1
,table2.fielda
,table2.fieldb
FROM
table1
INNER JOIN table2
ON table1.field1 = table2.fieldc
WHERE
table1.field2 in (@param1)
IF (@includecancelled = TRUE, '', ',AND table1.cancelled = FALSE')
GROUP BY
table2.fielda
,table2.fieldb
ORDER BY COUNT (table1.field1) DESC
这显然不工作,但是我想要的一般想法。
答
尝试使用CASE语句。 修订
SELECT TOP 10 COUNT (table1.field1) as Count_field1
,table2.fielda
,table2.fieldb
FROM
table1
INNER JOIN table2
ON table1.field1 = table2.fieldc
WHERE
table1.field2 in (@param1)
AND table1.cancelled = CASE WHEN @includecancelled = 1 THEN table1.cancelled ELSE 'FALSE' END
GROUP BY
table2.fielda
,table2.fieldb
ORDER BY COUNT (table1.field1) DESC
答
SELECT TOP 10 COUNT (table1.field1) as Count_field1
,table2.fielda
,table2.fieldb
FROM table1
INNER JOIN table2
ON table1.field1 = table2.fieldc
WHERE
table1.field2 in (@param1)
and (( @includecancelled = TRUE )
OR (@includecancelled = FALSE AND table1.cancelled = FALSE))
GROUP BY table2.fielda,table2.fieldb
ORDER BY COUNT (table1.field1) DESC
(upvoted)正确的目标。 –
这几乎就在那里,但有一个问题。当他们将“includecancelled”标记为true时,我想返回所有值(而不仅仅是取消的值)。你的CASE的FALSE部分是现货。但TRUE部分只返回取消的值。 – MSCF
已更新上面。将table1.cancelled设置为自己,而不是“TRUE”。 –