SSAS2005 Measure FormatString Property

Question

SSAS2005 Measure FormatString Property

hasanaink

SSC Veteran

Points: 220
More actions
October 10, 2008 at 8:26 am

#199147

Hello All,
I need to display time durations in the format "hh:mm:ss", I have achieved this to some degree, by ensuring that the data retrieved by the SSAS from the data warehouse is of type "float" this has been done as follows:
CONVERT(float, dateadd(ss, TimeDuration, 0))
In the AS database cube measure FormatString property I put the format mentioned. However if the time duration exceeds 1 day, the hours simply rollover and instead of say 45:00:00 for a 45 hour duration I get instead "21:00:00". How do I get 45:00:00? Also if that is not possible is there a way to get "+1d 21:00:00" instead?
Thanks
Hasanain

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

dva2007 SSCertifiable Points: 7707 More actions · Answer 1

Hi,

sorry i didnt test this. But if you see the last 2-3 conversation in this topic then u would be able to get something. Please check last query.

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3964391&SiteID=17

regards,

vijay

dva2007 SSCertifiable Points: 7707 More actions · Answer 2

please check this as well.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98507

regards,

Vijay

hasanaink SSC Veteran Points: 220 More actions · Answer 3

Hi,

I don't think I was clear... I do not have a problem with T-SQL, I know the conversions that can be done using T-SQL. However, I do have a problem with Analysis Services Database Cube Measure aggregates.

Thats where the problem comes in, even if I convert within T-SQL, how is AS supposed to sum (or any other aggregation) a non-numeric string (of format "hh:mm:ss").

Now when designing the AS database cube measures within BIDS, the Measure FormatString property, allows conversion, the following site explains this: http://sqlblogcasts.com/blogs/drjohn/archive/2007/12/03/ssas-working-with-date-and-time-measures-to-calculate-average-elapse-time.aspx

...and that is what I used.

Therefore, I need to know if this can be modified in some-way, so that I can show any amount of time even days in the hh:mm:ss format, this would obviously require the "hh" part to exceed 24.

Thanks

Hasanain

hasanaink SSC Veteran Points: 220 More actions · Answer 4

Found the solution ... http://sqlblog.com/blogs/mosha/archive/2008/09/26/displaying-duration-values-mdx-expressions-in-format-string.aspx

I used the idea given here to create a calculated member with the appropriate FORMAT_STRING MDX expression.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Agent Dur In Call]
AS [MEASURES].[AgentDurInCall], FORMAT_STRING = IIF ([MEASURES].[AgentDurInCall] < 1, 'hh:mm:ss', '"' + cstr(int([MEASURES].[AgentDurInCall])) + ' days" hh:mm:ss'),
NON_EMPTY_BEHAVIOR = { [AgentDurInCall] },
VISIBLE = 1;

I didn't know that an MDX expression can be used in FORMAT_STRING in this manner; in the cube designer however assigning an MDX expression for a measure will not work.

Regards

Hasanain

denglishbi SSCrazy Eights Points: 8831 More actions · Answer 5

Just wanted to remind you, and I am not sure how you are using this measure, but now that it is a calculation you will not be able to use the built-in drillthrough capabilites without some sort of Action or custom code.

http://sqlblog.com/blogs/mosha/archive/2008/09/01/drillthrough-on-calculated-measures.aspx

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com

kristel.demotte Grasshopper Points: 21 More actions · Answer 6

Hey,

This solution works fine for me in the BIDS Cube browser; but not in excel 2007.

It's like Excel does not recognize the format.

MDX Code:

[Measures].[Days Difference Decimal],

format_string=IIF([Measures].[Days Difference Decimal] >= 0,

IIF(

[Measures].[Days Difference Decimal] < 1

,'"0 days" hh:mm:ss'

,'"' + cstr(int([Measures].[Days Difference Decimal])) + ' days" hh:mm:ss')

,

IIF(

[Measures].[Days Difference Decimal] > -1

,'"-0 days" hh:mm:ss'

,'"' + cstr(int([Measures].[Days Difference Decimal])) + ' days" hh:mm:ss')

)

Result in de Cube Browser:-2 days 17:42:11

0 days 18:49:57

125 days 16:09:07

Excel:

#################

0 days 18:49:57

#################

Thanks

DataVeteran SSC Enthusiast Points: 142 More actions · Answer 7

For Excel 2007 to use the FORMAT_STRING setting correctly, you may also need to set the LANGUAGE property for the calculated member - see this blog post;

http://bisqlserver.blogspot.com/2008/12/ssas-calculated-member-currency-format.html

crazy.fingers.16 SSC Veteran Points: 274 More actions · Answer 8

So if I want to display HH:MM:SS with no days, i.e. if it is 100:05:01, display it as "100:05:01" and not as "4 days 04:05:01" , then how do I do that?

Thanks!