SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing Format of field for display


Changing Format of field for display

Author
Message
Eric Peterson
Eric Peterson
Say Hey Kid
Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)

Group: General Forum Members
Points: 664 Visits: 65
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
Eric Peterson
Eric Peterson
Say Hey Kid
Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)

Group: General Forum Members
Points: 664 Visits: 65
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 @days int
declare @hours int
declare @min 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 = (@secIn - @days - @hours) / 60 --as Hours

If @min > 9
Set @timeout = @timeout + convert(char(2), @min) + ':'
else
if @min > 0
Set @timeout = @timeout + '0' + convert(char(1), @min) + ':'
else
Set @timeout = @timeout + '00:'

set @min = @min * 60


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

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

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)
Luke L
Luke L
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8271 Visits: 6139
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

For better help with performance problems please read this
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search