Changing Format of field for display

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services 2005 Development » Changing Format of field for display

Changing Format of field for display

Rate Topic

Display Mode

Topic Options

Author

Message

Eric Peterson

Posted Monday, March 02, 2009 1:29 PM

SSC-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

Eric Peterson

Posted Monday, March 02, 2009 2:48 PM

SSC-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

Luke L

Posted Thursday, March 05, 2009 7:08 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 12:24 PM
Points: 2,892, Visits: 5,871

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 »

Permissions