asked on March 9, 2016
hi,
i have separate measures for processing hour and processing minute and i would like to display the processing time in HH:MM format.
but formula for ProcessingTime is not working. Can you give me example on how to concatenate values with different types as well as casting values.
thank you.
see below for my calculatedvalues.
<CalculatedMember name='ProcessedDurationHours' dimension='Measures' visible='false'>
<Formula>COALESCEEMPTY([Measures].[ProcessedHour] + INT([Measures].[ProcessedMinute]/60), 0)</Formula>
</CalculatedMember>
<CalculatedMember name='ProcessedDurationMinutes' dimension='Measures' visible='false'>
<Formula>COALESCEEMPTY([Measures].[ProcessedMinute] - INT([Measures].[ProcessedMinute]/60) * 60, 0)</Formula>
</CalculatedMember>
<CalculatedMember name='ProcessingTime' caption='加工実績時間' dimension='Measures'>
<Formula>IIf(([Measures].[ProcessedDurationHours] + [Measures].[ProcessedDurationMinutes]) > 0, (STR([Measures].[ProcessedDurationHours]), + ":" + IIf([Measures].[ProcessedDurationMinutes] < 10, '0' + STR([Measures].[ProcessedDurationMinutes]), STR([Measures].[ProcessedDurationMinutes]))), '')</Formula>
</CalculatedMember>
⋅ March 9, 2016
Hello Maria,
The component does not support such a calculated member. Could you please tell us if there is any alternative pivot table that shows such calculated members in HH:MM format?
Kind regards,
Iryna
⋅ March 10, 2016
Hi Ira,
The current system that we are migrating to flexmonster was developed using SQL Server Business Intelligence Development Studio.
I got the calculations from there.
Also, can you tell me what's wrong with my ProcessedDurationHours and ProcessedDurationMinutes?
I tried to display them in grid but all values are 0 but in grand total, the total values are displayed.
Thanks.
⋅ March 10, 2016
Hello Maria,
Actually, it's hard to say what is wrong with formulas without a sample of your database and full schema.
Also, as a client-side component, Flexmonster shows data from Mondrian "as is".
Please note that the SQL Server Business Intelligence and Mondrian are different technologies and some features may work otherways (or not work at all). From our side, we can only check whether all the data from the OLAP server is received and shown correctly.
But if some calculations aren't correct, probably there are some issues with schema/Mondrian.
Please let me know if you have any other questions.
Regards,
Ian
⋅ March 31, 2016
Hi Ian,
Thank you for your reply.
I got the correct calculations but total time is displayed as 13000 instead of 1:30:00
I've tried <CellFormatter> and <UserDefinedFunction> but both were not working.
formatString='##:##:##' is also not working.
⋅ March 31, 2016
Hello Maria,
Unfortunately, there is no such option to use formatting that is defined in the Mondrian schema.
Our technical team will research this issue to make it available in the future releases.
Regards,
Ian
⋅ March 31, 2016
Hi Ian,
Thank you. Hope you can include this in future release.
⋅ March 31, 2016
Hi Ian,
Additional question:
Can we apply cell formatting instead via javascript upon loading the pivot grid?
⋅ March 31, 2016
Maria,
There is an API call to set formatting (flexmonster.setFormat()), but such formatting as "##:##:##" is not supported in current version.
Thanks,
Ian
⋅ April 1, 2016
Hi Ian,
If we add our own format function, how do integrate it with the pivot grid?
Also, is it possible to support time format in the future?
Thanks.
⋅ April 1, 2016
Yes, we will add support in the future versions.
⋅ April 4, 2016
Hi Ian,
Thank you, we will wait.
For now, we would like to create separate javascript function to format date values in grid.
We can get the current value (getValue function) but there is no set or update value functions in API.
Please advise on how we can proceed with this.
⋅ April 4, 2016
Hi Ian,
re: Unfortunately, there is no such option to use formatting that is defined in the Mondrian schema.
pls confirm if the following is not supported as well:
<CalculatedMemberProperty name='FORMAT_STRING' expression="CAST([Measures].[ProcessedDurationHours] AS STRING) || ':' || CAST([Measures].[ProcessedDurationMinutes] AS STRING)" />
Thank you for your continuous support.
⋅ April 4, 2016
Hi Maria,
You are right, there are no set or update value functions in API. Unfortunately, there are no functions in API that can be used to implement the alternative idea to format date values in the grid using separate javascript function.
I confirm that the following is not supported so far as well:
<CalculatedMemberProperty name=’FORMAT_STRING’ expression=”CAST([Measures].[ProcessedDurationHours] AS STRING) || ‘:’ || CAST([Measures].[ProcessedDurationMinutes] AS STRING)” />
Kind regards,
Ian
⋅ April 5, 2016
Hi Ian,
Thanks for the confirmation.
