March 2, 2009 at 1:29 pm
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
March 2, 2009 at 2:48 pm
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:'
--**************** 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)
March 5, 2009 at 7:08 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply