Dynamic numeric scaling?

[hespora]

Hi there,


on relational, I've got measure that can realisically have values ranging from 12.34 up to 123,456,789.12. Is there any way to dynamically scale such values, as in:

if >100, omit decimals
if >10000, scale -3 and add 'K'
if >10000000, scale -6 and add 'M'

Now obviously, I could do this by manual calculation and turning the numbers into strings. That makes lists awkward and crosstabs impossible to work with. I'd like to be able to do this while retaining numerical format.

[Lynn]

Hi there,


on relational, I've got measure that can realisically have values ranging from 12.34 up to 123,456,789.12. Is there any way to dynamically scale such values, as in:

if >100, omit decimals
if >10000, scale -3 and add 'K'
if >10000000, scale -6 and add 'M'

Now obviously, I could do this by manual calculation and turning the numbers into strings. That makes lists awkward and crosstabs impossible to work with. I'd like to be able to do this while retaining numerical format.

An advanced conditional style might be just what the doctor ordered.

Click on your metric in your layout and create a new advanced conditional style.

Add expressions for each of the scenarios you need. It will work like a case statement and stop evaluating once it finds a match, so you'd reverse the order of your expressions above to start with the largest.

An example of one expression would be something like:

Code Select Expand


[YourQuery].[YourMetric] > 10000000


For each expression within the condition you can click the pencil to the right of the expression and set the data format as desired.

If I've missed the mark or if there are other options I'm sure people will chime in.

[BigChris]

What Lynn has suggested certainly should work, but can I just sound a word of caution? If all of the numbers in your report are of the same scale that would be a reasonable thing to do, but if you've got mixed scales you could end up giving a confusing picture. Relying on the user to notice the M at the back of a number when all the rest of the numbers have just got their decimals taken off might be optimistic.

Other than that...go for it.

[hespora]

BigChris: You certainly are right, and this will be a topic for much debate within my team and with the recipients before actually deploying this. However, in some case we really have huge layout issues simply due to running out of real estate within the report designs, as a few large figures are increasing column width.

Lynn: You are the best; you just made my weekend! :)

[Lynn]

Hooray! Glad it was a helpful approach.

There is certainly a layout challenge there as you and Chris point out. Other formatting options to draw the eye or otherwise distinguish the M or K situations might be something to consider. Or perhaps you can have the content organized in sections such that the different scaled figures are all appearing in the same section. Impossible to know if that is a reasonable suggestion without knowing more but tossing it out in case it sparks ideas.