0

How can I get current date on hour-minutes-seconds (06:13:47) format in MDX?

I'm trying to create a 'New Calculated Member' on the 'Calculations' Options inside the Cube used.

CALCULATE;
CREATE MEMBER CURRENTCUBE.[Measures].AverageAnalysisTimeFormatted
AS FORMAT([Measures].[AverageAnalysisTime], "HH:MM:SS"),
VISIBLE = 1 ;

My current result inside as it is (without the Calculated Member) is AnalysisTime = 1574,30

Which is 1574 seconds, or 26 minutes.

2

Try with this, I got 00:26:14 on Adventure Works cube:

with member measures.AverageAnalysisTime as 1574

member measures.AverageAnalysisTimeFormatted as measures.AverageAnalysisTime / (3600.0 * 24.0)
           ,format_string = 'hh:mm:ss'


           select measures.AverageAnalysisTimeFormatted on 0 from [Adventure Works]

enter image description here

1

You need to use "Now" and "Cstr" function . Take a look at the query below

with member measures.t as cstr(datepart("h",Now()))+':'+cstr(datepart("n",Now()))+':'+cstr(datepart("s",Now()))

select 
{
[Measures].[Internet Sales Amount],measures.t 
}
on columns,
[Product].[Category].[Category]
on rows 
from 
[Adventure Works]

Result

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.