如何让SRSS中用于双日期区间判断的Iif表达式正常运行

错误原因 #

• 核心问题是类型不匹配：Fields!From.Value为日期类型，直接和字符串格式的日期做比较，SSRS无法完成隐式类型转换时就会抛出#Error。
• 日期解析存在歧义：你使用的dd/MM/yyyy格式的日期字符串，会受报表/服务器的区域设置影响，若区域默认是MM/dd/yyyy格式，会将01/09/2021解析为2021年1月9日，完全不符合你预期的9月1日，比较逻辑错误。
• 未处理空值：如果Fields!From.Value存在空值，直接参与比较也会触发报错。

修复方案 #

建议用SSRS原生的日期字面量（用#包裹，固定为MM/dd/yyyy格式，不受区域设置影响）做比较，同时用SWITCH函数替代多层嵌套IIF提升可读性，另外增加空值判断避免空值报错。

修复后表达式如下：

=IIF(IsNothing(Fields!From.Value), "0",
SWITCH(
    Fields!From.Value >= #09/01/2021# And Fields!From.Value <= #08/31/2022#, "Sept 2021",
    Fields!From.Value >= #09/01/2020# And Fields!From.Value <= #08/31/2021#, "Sept 2020",
    Fields!From.Value >= #09/01/2019# And Fields!From.Value <= #08/31/2020#, "Sept 2019",
    Fields!From.Value >= #09/01/2018# And Fields!From.Value <= #08/31/2019#, "Sept 2018",
    Fields!From.Value >= #09/01/2017# And Fields!From.Value <= #08/31/2018#, "Sept 2017",
    Fields!From.Value >= #09/01/2016# And Fields!From.Value <= #08/31/2017#, "Sept 2016",
    True, "0"
))

如果你的Fields!From.Value本身是字符串类型而非日期类型，需要先将其转换为日期再比较，示例如下：

=IIF(IsNothing(Fields!From.Value), "0",
SWITCH(
    DateTime.ParseExact(Fields!From.Value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) >= #09/01/2021# And DateTime.ParseExact(Fields!From.Value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) <= #08/31/2022#, "Sept 2021",
    DateTime.ParseExact(Fields!From.Value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) >= #09/01/2020# And DateTime.ParseExact(Fields!From.Value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) <= #08/31/2021#, "Sept 2020",
    DateTime.ParseExact(Fields!From.Value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) >= #09/01/2019# And DateTime.ParseExact(Fields!From.Value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) <= #08/31/2020#, "Sept 2019",
    DateTime.ParseExact(Fields!From.Value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) >= #09/01/2018# And DateTime.ParseExact(Fields!From.Value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) <= #08/31/2019#, "Sept 2018",
    DateTime.ParseExact(Fields!From.Value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) >= #09/01/2017# And DateTime.ParseExact(Fields!From.Value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) <= #08/31/2018#, "Sept 2017",
    DateTime.ParseExact(Fields!From.Value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) >= #09/01/2016# And DateTime.ParseExact(Fields!From.Value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) <= #08/31/2017#, "Sept 2016",
    True, "0"
))

内容的提问来源于stack exchange，提问作者EducationSRSS