Patterns and Decimal/Thousands separators

[hespora]

Hi,


so I'm generally using data -> default data formats to specify my standard format for currency fields. No decimals, "." as thousands separator, currency symbol after the figures. Works fine so far.

Now unfortunately, I have one currency field that always is a delta value, i.e., in this, I want to format a value of 12345 as "+12.345€". To the best of my knowledge, getting a "+" in front of the value is only possible via patterns, so I'm using a pattern of "+###,###€" for this. This however defaults my thousands separator to a comma rather than a point.

The pattern documentation that I found says that a comma in the pattern is a *placeholder* for the thousands separator, so my thought is that it will look up - somewhere - which separator to use. Unfortunately, it definitely is not the thousands separator setting in the same field's data format.

How do I get both my plus in front of positive values (any other way than patterns maybe?), and my point as a thousands separator?

[hespora]

oh dear... nobody got an idea on this? :(

[BigChris]

Do you want to do anything with the field? For example, do you want to calculate a total, or multiply it by another field? The reason for asking is that you might be able to do what you're trying to do by converting the field to a string. You'd need to manipulate the field to get the format you want, and you might have to do some tricky things if the field goes over 999999...but all of that falls apart if it still needs to be a numeric field.

[hespora]

yea, unfortunately I do. Totalling and divisions need to be done with that field.

[Lynn]

Can you use standard formatting rather than a pattern and just unlock the layout to include a text item or expression for the "+"?

If it is always a positive figure then just drag in a text item for the "+" into your layout next to the figure. If it could be "+" or "-" based on the contents of the value then the "-" would come up anyway, so you just need to deal with >0 or =0 situations. Maybe something along the lines of this:

Code Select Expand


case
  when [YourValue] > 0 then '+'
else ''
end


The else condition would only come into play when the value is equal to zero.

The reference to [YourValue] rather than [YourPackage].[YourQuerySubject].[YourValue] should indicate the timing to be after auto aggregation. I did a quick test with automatic aggregation and it seems to work as I'd expect it to. If this is even a viable option for you then of course rigorous testing would be warranted.

I didn't mess around with patterns so I can't really say for sure whether it is possible to do it that way or not.

[hespora]

Lynn, for now I believe what you posted works fine, but like you said, I'm going to test that a bit more. Thanks for that idea!