asked on November 20, 2023
Hey!
I was wondering if there is a way to set calculated values to be exported to excel with 'GENERAL' format in the excel file.
The typical flexmonster format settings are not sufficient as there is a use case where I want to create a calculated value with formula like :
IF( sum('SOME_MEASURE') >= sum('OTHER_MEASURE'), 1, 0)
The resulting column will contain 0 and 1 values respectively but when I export to excel the column will contain 1.0 and 0.0 values respectively and the format of the column will be set to 'CUSTOM'. If I manually set the format of the column to 'GENERAL' the values will be converted to 1 and 0.
My question is is there a way to set every calculated value to be exported to excel with the 'GENERAL' format already set for the excel report?
⋅ November 21, 2023
Hello Aleksandar,
Thank you for reaching out to us.
We recommend setting the decimal places explicitly to "0" to show 0 and 1 values when exporting to Excel:
formats: [{
"name": "decimalFormat",
"decimalPlaces": 0
}],
You are welcome to check the following JSFiddle for reference: https://jsfiddle.net/flexmonster/m9y8pbes/
Please let us know if it works for you. Looking forward to hearing from you.
Kind regards,
Nadia
⋅ November 21, 2023
Hello Nadia, and thank you for the quick response!
I am not really looking for the format setting as the default flexmonster formats are working ok for us as we have many other cases where we need to have decimal precision which cannot be set manually with the format property. What I am looking for is a way to set the exported cells in excel to appear with the 'GENERAL' ( https://support.microsoft.com/en-us/office/reset-a-number-to-the-general-format-f71c3094-4231-408b-aef9-2f9506fa3021 ) excel format programatically (which I am not sure exists) which would fix all the issues we are having and also not mess up any other calculated value that is created in the flexmonster UI.
Best regards,
Aleksandar
⋅ November 22, 2023
Hello,
Thank you for the response.
Currently, there is no feature for setting the "General" Excel format for measures. Kindly note that it is possible to apply the custom format to the specific measure. This way, other measures will have the default Flexmonster format. This can be achieved by defining the name property in the Format object. For example:
formats: [{
"name": "decimalFormat",
"decimalPlaces": 0
}],
Then you can set the format name in the Slice object for the specific measure:
slice: {
...,
"measures": [{
"uniqueName": "Formula #1",
"formula": "if( sum(\"Price\") >= sum(\"Quantity\") , 0, 1)",
"caption": "Formula #1",
"format": "decimalFormat"
}]
},
Please check the following JSFiddle: https://jsfiddle.net/flexmonster/3t7s64bg/. When exporting to Excel, you can see that only "Formula #1" has no decimal places, so the format was applied to only one measure.
Please let us know if it works for you. Looking forward to hearing your feedback.
Kind regards,
Nadia
⋅ December 13, 2023
Hello Aleksandar,
Hope you are doing well.
We were wondering if you had a chance to check the suggested approach. Could you please confirm if it works for you?
Looking forward to hearing your feedback.
Kind regards,
Nadia