Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Changing Format of field for display Expand / Collapse
Author
Message
Posted Monday, March 2, 2009 1:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 24, 2010 1:57 PM
Points: 182, 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

Post #666862
Posted Monday, March 2, 2009 2:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 24, 2010 1:57 PM
Points: 182, 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)

Post #666924
Posted Thursday, March 5, 2009 7:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 20, 2014 12:32 PM
Points: 2,655, Visits: 6,014
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
Post #669189
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse