SSRS-复杂的日期范围字段逻辑不适用于嵌套的IIF

Gavin 发表于 Dev

加文

要求根据以下逻辑在Reporting Services报表中显示日期范围，如下所示：

如果只有开始日期时间，则显示如下：

于2014年12月12日

如果同一天有开始和结束日期时间，则显示如下：

2014年12月12日上午11:20至下午1:10

如果在不同的日期都有开始和结束日期时间，则显示如下：

2014年12月12日上午11:20至2014年12月13日下午1:10

我知道这很丑陋，但是我有下面的表达式可以显示该字段：

=IIf(IsNothing(First(Fields!Finished.Value, "InspectionAdvice")), "on " & Day(First(Fields!Started.Value, "InspectionAdvice")) & " " & MonthName(Month(First(Fields!Started.Value, "InspectionAdvice"))) & " " & Year(First(Fields!Started.Value, "InspectionAdvice")) & " " & 
    IIf(
        Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice")) - 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice"))
    ) 
    & ":" & Minute(First(Fields!Started.Value, "InspectionAdvice")) & " " & 
    IIf(Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, "pm", "am")
,
IIf(First(Fields!Started.Value.Date, "InspectionAdvice") = First(Fields!Finished.Value.Date, "InspectionAdvice")
    , "on " & Day(First(Fields!Started.Value, "InspectionAdvice")) & " " & MonthName(Month(First(Fields!Started.Value, "InspectionAdvice"))) & " " & Year(First(Fields!Started.Value, "InspectionAdvice")) & " between " & 
    IIf(
        Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice")) - 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice"))
    ) 
    & ":" & Minute(First(Fields!Started.Value, "InspectionAdvice")) & " " & 
    IIf(Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, "pm", "am")
    & " and " & 
    IIf(
        Hour(First(Fields!Finished.Value, "InspectionAdvice")) > 12, 
        Hour(First(Fields!Finished.Value, "InspectionAdvice")) - 12, 
        Hour(First(Fields!Finished.Value, "InspectionAdvice"))
    ) 
    & ":" & Minute(First(Fields!Finished.Value, "InspectionAdvice")) & " " & 
    IIf(Hour(First(Fields!Finished.Value, "InspectionAdvice")) > 12, "pm", "am")

    , "between " & Day(First(Fields!Started.Value, "InspectionAdvice")) & " " & MonthName(Month(First(Fields!Started.Value, "InspectionAdvice"))) & " " & Year(First(Fields!Started.Value, "InspectionAdvice")) & " " & 
    IIf(
        Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice")) - 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice"))
    ) 
    & ":" & Minute(First(Fields!Started.Value, "InspectionAdvice")) & " " & 
    IIf(Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, "pm", "am") 
    & " and " & Day(First(Fields!Finished.Value, "InspectionAdvice")) & " " & MonthName(Month(First(Fields!Finished.Value, "InspectionAdvice"))) & " " & Year(First(Fields!Finished.Value, "InspectionAdvice")) & " " & 
    IIf(
        Hour(First(Fields!Finished.Value, "InspectionAdvice")) > 12, 
        Hour(First(Fields!Finished.Value, "InspectionAdvice")) - 12, 
        Hour(First(Fields!Finished.Value, "InspectionAdvice"))
    ) 
    & ":" & Minute(First(Fields!Finished.Value, "InspectionAdvice")) & " " & 
    IIf(Hour(First(Fields!Finished.Value, "InspectionAdvice")) > 12, "pm", "am")
)
)

如果Started和Finished都不为null，则一切正常。但是，如果Finished为null，则我总是会收到#Error。

现在，如果我删除包含嵌套IIf逻辑的IIF的第二部分，例如

=IIf(IsNothing(First(Fields!Finished.Value, "InspectionAdvice")), "on " & Day(First(Fields!Started.Value, "InspectionAdvice")) & " " & MonthName(Month(First(Fields!Started.Value, "InspectionAdvice"))) & " " & Year(First(Fields!Started.Value, "InspectionAdvice")) & " " & 
    IIf(
        Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice")) - 12, 
        Hour(First(Fields!Started.Value, "InspectionAdvice"))
    ) 
    & ":" & Minute(First(Fields!Started.Value, "InspectionAdvice")) & " " & 
    IIf(Hour(First(Fields!Started.Value, "InspectionAdvice")) > 12, "pm", "am")
,
"REMOVED"
)

当Finished为null时，一切正常。

知道为什么我不能让丑陋的陈述的两个部分协同工作吗？我猜想Reporting Services正在尝试以错误状态解析某些IIF，这将导致NULL Finished字段出现错误，从而导致错误？

奥兹威

不知道为什么，但是比较日期的部分导致错误：

IIf(First(Fields!Started.Value.Date, "InspectionAdvice") = First(Fields!Finished.Value.Date, "InspectionAdvice")

如您所说，由于某种原因，RS试图进行评估Fields!Finished.Value.Date，从而导致错误。

我简化了您的代码，发现此解决方法工作正常：

=IIf(IsNothing(Fields!Finished.Value), 
    "finished is null", 
    IIf(DateDiff(DateInterval.Day, Fields!Finished.Value, Fields!Started.Value)=0,  
        "same day",
        "different day"
        )
    )

结果：生成报表生成器3.0