ssrs iif语法

Question

在我的查询设计器中，我尝试使用以下查询：

SELECT        Site, LegalConfigNumber, COUNT(*) AS Total
FROM            EPSInfoLive
 iif(@LegalConfigNum  =  0,
WHERE        (InputDate >= @rundate) AND (InputDate <= DATEADD(day, 1, @rundate)),
WHERE        (InputDate >= @rundate) AND (InputDate <= DATEADD(day, 1, @rundate)) AND LegalConfigNumber = @LegalConfigNum)
GROUP BY LegalConfigNumber, Site
ORDER BY Site, LegalConfigNumber

当我尝试测试它时，@LegalConfigNum收到一条关于语法不正确的消息。不过，我似乎找不到它会在哪里。

Answer 1

SSRS查询需要用SQL编写。您在使用“@iif”时混合了SQL和Visual Basic。SQL支持CASE语句，而不是IIF语句。但是你不需要它来展示你所展示的东西。下面的语句在逻辑上应该是等价的：

SELECT
   Site,
   LegalConfigNumber,
   COUNT(*) AS Total
FROM
   EPSInfoLive
WHERE
   (InputDate >= @rundate) AND (InputDate <= DATEADD(day, 1, @rundate))
   AND (LegalConfigNumber = @LegalConfigNum OR @LegalConfigNum  =  0)
GROUP BY LegalConfigNumber, Site
ORDER BY Site, LegalConfigNumber