In a highlight in a report I want to count all the instances where some currency field has the value "0", so I am using "filter related items":
$C_IGA = 0 -> error: Data types Currency and Numeric not compatible for '=' operation
$C_IGA = currency(0) and $C_IGA/Currency(1) = 0 -> error: "Function Currency() can be used only in calculated field"
How can I count the number of items where the value of a currency field is "0"?
Please sign in to leave a comment.
Hi Peter,
Please try $C_IGA = NULL.
I have also added a feature request to add currency() function to the formula in reports, so that you can convert currency fields to numeric, the same way you currently can in our customization engine. ID for your reference: **CR-259958
**All the best,
Diana
Hi Diana,
I do not need the ones with NULL but where the currency value is zero/0
Hi Peter,
You can try to create a currency custom field, that is formula based: currency(0). Then compare:
$C_IGA=$C_ZeroCurency.
Diana
Is this really the only option? Can it not be done with a formula?
Currently, this requires a bit of a workaround with a custom field, but we are planning to add currency() function to reports formula, so that it becomes possible in the near future.
I ran into another case today where I need this (i.e. to make greater than for a currency field) - so is there any update to this?
Following - we also have a need to compare to 0.
Hi Guys,
I still struggle with the topic you started 4 years ago. Have you managed to resolve it?
Maciej
Hello Maciej,
Say one wants to create a report highlight that counts projects with 0 Actual Cost. Steps to implement this:
1) Create a custom currency field on the Project entity with formula equal to Currency(0):
2) Create the report and add a highlight:
3) Results:
I hope this is helpful.
Best regards,
Roland
Is there any update on CR-259958? I need to be able to add a calculated field that converts a currency field and multiplies it by another numeric field within a report. To do this I need to convert the currency field to numeric but the Currency() function is not available in reporting
You can create a custom field (formulaic) to do the conversion and then use the custom field in the report.
Hi Thomas,
I think you should be able to multiply a currency field by a numeric field - no conversion should be needed.
Could you please check and confirm?
Thank you,
Roland
Hi Roland - I can do this but this will only multiply by the currency amount that is the system currency (in my case USD). What I want to do is multiply by the local currency amount which is a non-USD based currency. Do you know a way to do this?
Hi Thomas,
In that case, you'll have to create a custom field as per Josh's recommendation. As for the CR-259958, there's still not ETA.
I hope this helps.
Roland
