Hi all

In my SQl server I'm storing data in seconds.In my
analysis server cube i want to display the measure in
mins and seconds.when i aggregate that values it should
aggregate in mins and seconds. for example in my sql
server I'm storing it as 90 seconds.In analysis manager i
want to show that as 1 min and 30 seconds.If i divide
90/60 it'll give me 1.5 which tells 1 min and 50
secs,which is wrong.In sql server if i store in mins and
second while aggregation again same problem will occur.If
i store the value as 5.50 and 1.25 aggregation will give
me 6 mins 75 secs, but actually i should get 8 mins and
15 secnds.So pls anybody can help me how to proceed with
this problem?any clues or helps will be appreciated..

thanks in advance
sanith

hello Darryl
Thanks for the reply.I think I have not given you proper
input.My problem is not in the dimension level.Actually
I'm stroing the data in the fact table as seconds.Its an
integer filed.I need to display the aggregated measures
in mins and seconds.So i'll be creating the calculated
members inside the cube.There if i divide the seconds by
60,I won't be getting the actual value.For example my
value aggregated messure is 90 if i divide that by 60
i'll get 1.5 which is wrong.I should get 1 min and 30
seconds.So if nay way to display 1.5 as 1 min and 30
seconds in the calculated members of the cube.I'm not
using any MDX queries so i need to do all the
calculations inside the cube itself.I'm using OWC
component as client.Do u have any clues it'll be
greatful.Thanks in advance.
regards
sanith

Try this:

if your Seconds value will never be greater than 86400 (1 Day) then you can:
Set the Format of the Measure to 00:00:00
and the value to:
clng(Format("dbo"."TableName"."Seconds"/86400, "hhmmss"))

otherwise you will need to Create a normal measure for Seconds and then a
Calculated Member [Measures].[Duration] as [Measures].[Seconds] (it could be
anything you like, but it will be overwritten with Calculated Cells)

Create the following Calculated Cells:
[Update Duration1]
Format String = 0 "days" 00:00:00
Calc SubCube = {[Measures].[Duration]}
Calc Condition = [Measures].[Seconds]/86400>=2 and (not
IsEmpty([Measures].[Seconds]))
Calc Value = clng(Format("dbo"."TableName"."Seconds"/86400, "hhmmss")) +
clng(int([Measures].[Seconds]/86400)*1000000)

[Update Duration2]
Format String = 0 "day" 00:00:00
Calc SubCube = {[Measures].[Duration]}
Calc Condition = [Measures].[Seconds]/86400>=1 and
[Measures].[Seconds]/86400<2 and (not IsEmpty([Measures].[Seconds]))
Calc Value = clng(Format("dbo"."TableName"."Seconds"/86400, "hhmmss")) +
clng(int([Measures].[Seconds]/86400)*1000000)

[Update Duration1]
Format String = 00:00:00
Calc SubCube = {[Measures].[Duration]}
Calc Condition = [Measures].[Seconds]/86400<1 and (not
IsEmpty([Measures].[Seconds]))
Calc Value = clng(Format("dbo"."TableName"."Seconds"/86400, "hhmmss"))


Hope this helps
Aaron




"Sanith" <skumar (AT) 5by5networks (DOT) com> wrote