none
Problem with Hour Formats

    Question

  • Hello,

    I have a field smallint which gives duration in seconds. I want to show it in on SSRS but on  ' hh:mm:ss ' instead of second, so I use the following custom expression:

     

    =IIF(Fields!.Value=0,"00:00:00",

    IIF( Len(""& Fix((Fields!.Value/3600)&"")=1,"0" & Fix((Fields!.Value/3600),Fix((Fields!.Value/3600))

    & ":" & IIF(Len(""& Fix(((Fields!.Value mod 3600) / 60) &"")=1,"0" & Fix(((Fields!.Value mod 3600) / 60),Fix(((Fields!.Value mod 3600) / 60))

    & ":" & IIF(Len(""& ((round(Fields!.Value mod 3600) mod 60)&"")=1,"0" & ((round(Fields!.Value mod 3600) mod 60),((round(Fields!.Value mod 3600) mod 60))

    )

     

     

    The problem is that, when I export the report to Excel, I cannot make calculations on this column, because Excel does not know the format of the column.

     

    What can I do to have this field with an hour format in SSRS, and in Excel too?

    Friday, March 27, 2009 4:38 PM

Answers

  • Is it possible for you to perform the calculation in a sql call, and then you would have the value from the database to use to format the column...therefore making Excel happy.

    There isn't anything in that calculation that you are doing, near as I can tell, that you couldn't do in a sproc/udf call, where you pass the field in question.

    Cheers!
    • Marked as answer by Douja Monday, March 30, 2009 4:29 PM
    Friday, March 27, 2009 9:34 PM

All replies

  • Is it possible for you to perform the calculation in a sql call, and then you would have the value from the database to use to format the column...therefore making Excel happy.

    There isn't anything in that calculation that you are doing, near as I can tell, that you couldn't do in a sproc/udf call, where you pass the field in question.

    Cheers!
    • Marked as answer by Douja Monday, March 30, 2009 4:29 PM
    Friday, March 27, 2009 9:34 PM
  • I have already do it, and i can have the field in excel and SSRS in the date format.
    But the problems is that, i can't do an agregation for this field in reporting services ( and that very imoprtant for me),because it's in the date format.
    And in excel i have a problem in the calculation, for exemple the sum of too field : 00:00:10 and 00:00:01 is 48:00:11, i have 48 hour in more .

    Someone have a solutions please!!!!!!!!
    • Marked as answer by Douja Tuesday, March 31, 2009 10:10 AM
    • Unmarked as answer by Douja Tuesday, March 31, 2009 10:10 AM
    Tuesday, March 31, 2009 10:10 AM