Menu
Download Free Trial

Can we do concatenation in CalculatedMember?

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>

14 answers

Public
Iryna Kulchytska Flexmonster 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

Public
Maria Cristina Debuayan 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.
 

Public
Ian Sadovy Flexmonster 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

Public
Maria Cristina Debuayan 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.

Public
Ian Sadovy Flexmonster 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

Public
Maria Cristina Debuayan March 31, 2016

Hi Ian,
 
Thank you. Hope you can include this in future release.

Public
Maria Cristina Debuayan March 31, 2016

Hi Ian,
 
Additional question:
Can we apply cell formatting instead via javascript upon loading the pivot grid?

Public
Ian Sadovy Flexmonster 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

Public
Maria Cristina Debuayan 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.

Public
Ian Sadovy Flexmonster April 1, 2016

Yes, we will add support in the future versions.

Public
Maria Cristina Debuayan 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.
 

Public
Maria Cristina Debuayan 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.
 

Public
Ian Sadovy Flexmonster 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

Public
Maria Cristina Debuayan April 5, 2016

Hi Ian,
 
Thanks for the confirmation.

Please login or Register to Submit Answer