SSRS Format seconds as time (negative seconds)

问题:

I have a column with integers: TotalSec that has seconds. It can be 0, negative or positive.

I have to format these seconds in a report. But cant get something working for the negative seconds.

My logic: For 0 = Nothing, For Positive format as HH:mm:ss For Negative - ABS the value then format as -HH:mm:ss

=IIF(SUM(Fields!TotalSec.Value)=0, Nothing, IiF(SUM(Fields!TotalSec.Value)>0,
Format(DateAdd("s",SUM(Fields!TotalSec.Value), "00:00:00"), "HH:mm:ss"), "-" & Format(DateAdd("s",ABS(SUM(Fields!TotalSec.Value)), "00:00:00"), "HH:mm:ss")))

I get an #Error for the Negative numbers. With the warning:

Warning 2   [rsRuntimeErrorInExpression] The Value expression for the textrun ‘TotalSec.Paragraphs[0].TextRuns[0]’ contains an error: The added or subtracted value results in an un-representable DateTime.  Parameter name: value

回答1:

It worked like this:

=IIF(SUM(Fields!TotalSec.Value)=0,Nothing,IIF(SUM(Fields!TotalSec.Value)< 0,"-"&Format(DateAdd("s",ABS(SUM(Fields!TotalSec.Value)), "00:00:00"), "HH:mm:ss"),Format(DateAdd("s",ABS(SUM(Fields!TotalSec.Value)), "00:00:00"), "HH:mm:ss")))

回答2:

I cleaned up the previous answer:

=IIf(Fields!elapsed.Value < 0, "-", "+")
&
Format(
    DateAdd(
        "s",
        Abs(Fields!elapsed.Value),
        "00:00:00"
    ),
    "HH:mm:ss" ' can be m:ss
)

The "+" is to keep the results lined up, and can be replaced with "" if desired.