asked on December 6, 2023
Hi Flexmonster,
Is it possible to make a calculated field on a text-field
field: currency
type: text
values: EUR or USD
field: amount
type: number
values: <numbers>
I want to add a calculated value which does the following:
if ("currency" = "EUR", amount * 1, amount * 1.1)
Is this possible?
Regards Jaco
⋅ December 7, 2023
Hello, Jaco!
Thank you for writing to us.
We would like to know which data source type you need to perform these calculations with. This would greatly help us to give better advice, since some of the calculated values features are data source specific.
Looking forward to hearing from you.
Best Regards,
Maksym
⋅ December 11, 2023
Hi, Im using the "custom api datasource"
I do not want to add a new column server-side with the currency value. What i want is that our customer can define its own currency value in his report self.
Therefore he needs to have a "calculated value" , where he can make a formula based on a text value and the outcome is (in this case) a numeric value.
if ("currency" = "EUR", "amount" * 1", "amount" * 1.1)
regards Jaco
⋅ December 12, 2023
Hello, Jose!
Thank you for sharing more details with us.
To achieve this functionality with the custom data source API, we recommend using the customizeAPIRequest API call. This method allows customizing the request before sending it to a server. The general idea for the implementation is the following:
customizeAPIRequest API call./select request handler with the currency conversion logic, which will modify individual values based on the currency condition. Note that this logic should be implemented before aggregating the data.Please let us know if this approach would work for you.
Best Regards,
Maksym
⋅ December 12, 2023
Hi Maksym,
I dont think that is the answer for my question.
Can our users create their own calculated fields which use text value in the formula?
Or can i capture the formula in de customizeAPIRequest, do some stuff with it and add it to the request for /select and then do something with it on the server-side?
regards Jaco
⋅ December 14, 2023
Hello, Jaco!
Thank you for your reply.
We would like to start off by explaining how calculated values work with the custom data source API.
The calculated values are computed on the client for the custom API data source. This means that the calculations are performed based on already aggregated measures. When Flexmonster sends the /select request for a calculated value, this request only consists of measure names and aggregations that compose the formula, not the whole formula. As a result, converting each individual value based on the other value from the same record would only be possible on the server. Additionally, the calculated value functionality was designed for values, not hierarchies. Hence, comparing string members in the calculated value formula is not currently supported.
Taking into account the previously described specifics of how calculated values work with the custom API, your case requires a different approach. If there is a need for the user to choose a measure and then be able to convert it to a certain currency on the fly, this can be achieved using an approach where you add an additional field to the fields request, and then on the server calculate the value for it for a certain slice. Check out the step-by-step guide below:
/fields request, add the new measure(s) for converted currency values to a response so that it can be defined in the report or selected from the Fields List./select request handler, add the currency conversion logic based on individual value. Then, perform any necessary aggregation on this measure.Using this approach, you can create new measures that are unified into a user-defined currency. We would like to know if this approach works for your project. Also, we are ready to provide more details about the implementation if necessary.
Looking forward to hearing from you.
Best Regards,
Maksym
⋅ January 4, 2024
Hello, Jaco!
Hope you are doing well.
Our team is wondering if you were able to implement the currency conversion logic with custom API.
Looking forward to hearing your feedback.
Best Regards,
Maksym
⋅ January 8, 2024
Hi Maksym,
No we havent had the time yet to look at your proposed solution. We have planned to do it this month.
Will let you know if (or when) we get it working.
Regards Jaco
⋅ January 8, 2024
Hello, Jaco!
Thank you for sharing this news with us.
Feel free to contact us if you have any questions or need clarification as you navigate through the implementation.
Best Regards,
Maksym
⋅ January 30, 2024
if i understand correctly, this can not be done via the use of a "calculated field", but i need to create a "custom UI control" in which i define the column "currency" and where the user can choose which value he wants. That data is sent to the server where i parse the data and then return the calculated value?
I do not mind parsing the data on the server side, but i do mind creating a custom UI control for 1 specific field. I want the user to use his fantasy to create all kinds of formulas and not only the ones based on numbers, but also on strings.
It is unfortunately that comparing string members in the calculated value formula is not currently supported, that would really be a nice feature
Regards Jaco
⋅ January 31, 2024
Hello. Jaco!
Thank you for your feedback.
We understand that using strings in calculated values would be a useful feature. This feature is already on our customers' wishlist, and we will notify you if there are any updates on this matter.
Do not hesitate to contact us if any other questions arise.
Best Regards,
Maksym