Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
swifte
Starting Member
1 Post |
 Posted - 2002-02-28 : 17:45:21
|
| I have a need to be able to view hour and minute data via a cube measure to track resource utilization. For example: (HH:MM)ROW1 - 16:45ROW2 - 24:15TOTAL ALL ROWS - 41:00How can this be done so that the cube will sum and rollup these values properly? What data type should be used in the fact table, and how should the measure be configured?Thank in advance for any help offered here! |
|
|
Tim
Starting Member
392 Posts |
Posted - 2002-03-06 : 18:29:56
|
| First of all, convert your values to represent seconds and store as an integer data type in the fact table. Call this column "intSeconds".In your example:24:15 = 1,455 seconds16:45 = 1,005 secondsNow just make that a measure in your cube, and leave aggregate function as "sum". The measure should be named "intSeconds" by default so leave that as is.Now create a new calculated measure and name it "HolyGrail". Set it's value property to this:(int([Measures].[intSeconds] / 60) * 100) + ([Measures].[intSeconds] - (int([Measures].[intSeconds]/60)*60))Now set it's format string property to this:00:00Now set the visible property of the intSeconds measure to false so that it's hidden, otherwise you'll have a total in seconds as well.Process your cube and ask the boss for a raise!Hope that helps. |
 |
|
|
|
|
|
Time Measures