Changing Format of field for display

  • All

    Ok, I am an expert DBA that is a novice at reporting, so this may be something really easy to do.....

    I have a report that generates data in seconds. Some users like it in seconds, because the can download this to excel and perform additional analysis. Other users like the report to be more readable and show time in an Hour:Min:Sec format.

    1) is there a way to set a "display format" flag on the report so that if the users don't like the format, then they hit the reformat button and it goes from raw seconds to hh:mm:ss format?

    2) Anyone have a function already written to do the seconds to time format conversion?

    Thanks in advance

    Eric Peterson

  • Why wait when I can write it my self......Here is a function to convert the seconds to DD:HH:MM:SS

    now still wondering if I can toggle or if a new report will be needed.

    CREATE FUNCTION [dbo].[TimeConvert] (@SecIn int)

    RETURNS char(11)

    AS

    Begin

    declare @daysint

    declare @hours int

    declare @min-2 int

    declare @sec int

    declare @timeOut varchar(11)

    --************ days *************

    set @days = @secIn / 86400 -- as Days

    If @days > 9

    Set @timeout = convert(char(2), @days) + ':'

    else

    if @days > 0

    Set @timeout = '0' + convert(char(1), @days) + ':'

    else

    Set @timeout = '00:'

    set @days = @days * 86400

    --************ hours *************

    set @hours = (@secIn - @days) / 3600 --as Hours

    If @hours > 9

    Set @timeout = @timeout + convert(char(2), @hours) + ':'

    else

    if @hours > 0

    Set @timeout = @timeout + '0' + convert(char(1), @hours) + ':'

    else

    Set @timeout = @timeout + '00:'

    set @hours = @hours * 3600

    --**************** min ****************

    set @min-2 = (@secIn - @days - @hours) / 60 --as Hours

    If @min-2 > 9

    Set @timeout = @timeout + convert(char(2), @min-2) + ':'

    else

    if @min-2 > 0

    Set @timeout = @timeout + '0' + convert(char(1), @min-2) + ':'

    else

    Set @timeout = @timeout + '00:'

    set @min-2 = @min-2 * 60

    --**************** sec ****************

    set @sec = (@secIn - @days - @hours - @min-2 )

    If @sec > 9

    Set @timeout = @timeout + convert(char(2), @sec)

    else

    if @sec > 0

    Set @timeout = @timeout + '0' + convert(char(1), @sec)

    else

    Set @timeout = @timeout + '00'

    Return(@timeout)

    END;

    GO

    -- select dbo.TimeConvert(123456)

    -- select dbo.TimeConvert(123456)

    -- select dbo.TimeConvert(123456)

    -- select dbo.TimeConvert(123456)

    -- select dbo.TimeConvert(123456)

    -- select dbo.TimeConvert(123456)

  • You could do this not so much with a button, but with a new report parameter...

    You could create a string type parameter that allows the user to chose the format mperhaps called Format, before they execute the report. Set the label to something the users understand, Set the value to 1 and 0 or some other.

    Then in the format statement for the field use an IIF expression based on the value of the parameter, something like the following pseudocode =IIF(Parameters!Format.value = 0, "HH:MM:SS", "Just Seconds")

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply