Formatting Timespans within Business Objects Desktop Intelligence (BO Deski)

Question

Can anyone tell me how to format a given number of seconds (for example 16742 seconds) in a human readable form (for example 1d 5h 2m 13s)

• using a BO field formula
  or
• from within a T-SQL SELECT statement (where the Duration as Seconds is from a SUM aggregate)?

Answer 1

Hello, Personally I would make a TSQL function for this. Below is a sample that you can adapt:

IF(OBJECT_ID('FN_STRING_TO_TIME', 'FN') IS NOT NULL)
    DROP FUNCTION FN_STRING_TO_TIME
GO

CREATE FUNCTION dbo.FN_STRING_TO_TIME( 

    -- Seconds to convert
    @pSeconds INT
)
RETURNS VARCHAR(12)
---------------------------------------------------------------------------------------
-- Developer:       Linus Brimstedt
-- Date:     2009-03-17
--
-- Function:     Returns the given seconds in H:MM:SS format
--    
-- Output:          String in format H:MM:SS
--    
---------------------------------------------------------------------------------------
BEGIN

-----------------------------
-- Variables
-----------------------------

    DECLARE @output  VARCHAR(30) -- Describe the variables

    DECLARE @minutes INT
    DECLARE @hours  INT

-----------------------------
-- Implementation
-----------------------------

    SET @minutes = @pSeconds / 60
    SET @pSeconds = @pSeconds % 60

    SET @hours = @minutes / 60
    SET @minutes = @minutes % 60

-----------------------------
-- Return output
-----------------------------
    RETURN CAST(@hours AS VARCHAR) + ':'
    + dbo.FN_STRING_LPAD(@minutes, 2, '0') + ':'
    + dbo.FN_STRING_LPAD(@pSeconds, 2, '0') 
END
GO

-- Test
DECLARE @seconds INT
,   @got  VARCHAR(12)
,   @expected VARCHAR(12)

SELECT  @seconds = 67
,   @expected = '0:01:07'
SET @got = dbo.FN_STRING_TO_TIME(@seconds)

IF(@got != @expected)
    RAISERROR('FN_STRING_TO_TIME(%d) returned bad value: Expected: %s, got %s', 11, 11, @seconds, @expected, @got)

SELECT  @seconds = 60 * 60 * 7 + 60 * 14 + 34
,   @expected = '7:14:34'
SET @got = dbo.FN_STRING_TO_TIME(@seconds)

IF(@got != @expected)
    RAISERROR('FN_STRING_TO_TIME(%d) returned bad value: Expected: %s, got %s', 11, 11, @seconds, @expected, @got)

Cheers /L

Answer 2

I have done it using Deski and four variables. Start of with the hours measure, create some variables, in the following my year variable is vYears, my Month variable is vMonths, my days variable is vDays, and my hours variable is vHours.

vYears uses the following calculation:

=Floor(Sum(<Hours Billed by Date Billed>/24)/365.25)

vWeeks uses the following calculation:

=Floor(Sum((Sum(<Hours Billed by Date Billed>/24)/365.25)-<vYears>)*365.25/7)

vDays uses the following calculation:

=Floor(Sum(Sum((Sum(<Hours Billed by Date Billed>/24)/365.25)-<vYears>)*365.25/7-<vWeeks>)*52/24)

vHours uses the following calculation:

=Truncate(Sum((Sum(Sum((Sum(<Hours Billed by Date Billed>/24)/365)-<vYears>)*365/7-<vWeeks>)*52/24-<vDays>)*24) , 2)

I Then can output these variables by doing the following:

=<vYears>+" Years "+<vWeeks>+" Weeks "+<vDays>+" Days "+<vHours>+" Hours"

If you need further information please dont hesitate in asking.

Thanks,

Matt