Report on EazyBI that shows how long an issue was in a state, expressed in hours, minutes and second

[Saul Alexander Mata Velasquez]

Hi community!

First of all a greeting

I hope you can help me

I have a requirement to make a report in EazyBI that shows me how long an issue was in a state, expressed in hours, minutes and seconds.

I could make the report, but only expreeed in hours and minutes, but I can't find a way to express the SECONDS

Any ideas?

PD: To express it in hours and minutes, I did the following:

[Measures].[Days in transition status]*24*60

Thank you.

[Lauma Cīrule]

Hi Saul!

You are correct, default eazyBI formatting offers the minute formatting with ##h ##m assuming the number is in minutes. Additionally, eazyBI imports the Days in transition status with a scale of four which gives the precision down to minutes (one second would be ~0.00001 from a day).

But, if having approximately 10s possible error is OK for you, it is possible to construct a string (this will not be drillable measure, but number converted to a string) with MDX that would return a result in the format of #d #h #m #s. Try the following formula for that

(Case
When [Measures].[Days in transition status]>=1
Then Format(Int([Measures].[Days in transition status])
,"0d ") -- int returns the whole days
Else ""
End)

||

(Case
When [Measures].[Days in transition status]>0.0417
Then Format(Int(([Measures].[Days in transition status]
- Int([Measures].[Days in transition status])) *24)
,"0h ") -- get the whole hours by substracting the whole days
Else ""
End)

||

(Case
When [Measures].[Days in transition status]>0.00069
Then Format(Int(([Measures].[Days in transition status] *24 
- Int([Measures].[Days in transition status] *24 )) *60)
,"00m ") -- substract the whole hours to get minutes
Else ""
End)

||

(Case
When [Measures].[Days in transition status]>0.00001
Then Format(([Measures].[Days in transition status] *24 *60 
- Int([Measures].[Days in transition status] *24 *60 )) *60
,"00s") -- substract the whole minutes to get remaining seconds
Else ""
End)

Lauma / support@eazybi.com

[Saul Alexander Mata Velasquez]

@Lauma Cīrule Thanks for your help, it works perfect.