In SSRS How to show extended decimal places only when needed?

Kinetic ERP
,
1

I am customizing the Purchase Order report to change the unit price field (=Fields!DocUnitCost.Value) to show 4 decimal places. But I only want to show the decimal places when they exist, if there are just 0s there, I can ignore them and just show 2 decimal places.
I think I could do this with a fancy case or if statement. Is there a better way?
Thanks for your time!
Nate

2

I would check the field settings. Other than that, I think you are correct in needing a fancy statement.

2 Likes
3

In the format setting plug in:

=Switch(Fields!DocUnitCost.Value Mod .01 = 0,"c2",
		Fields!DocUnitCost.Value Mod .001 = 0,"c3",
		1=1,"c4")

First true statement executes, so if you didn’t want any decimals on even dollar amounts, add one more case at the top where Mod 1 results in c0.

1 Like
4

I think this will work, but it seems to be ignoring the formatting and just displaying 4 decimals. Regardless of if those extra decimal places are just 0. I know it is not the quotation marks. Is there something else I am missing? I think that 1=1 is a little sus…

5

I swapped that 1=1 for:

=Switch(Fields!DocUnitCost.Value Mod 0.01 = 0, "c2", Fields!DocUnitCost.Value Mod 0.001 = 0, "c3", Fields!DocUnitCost.Value Mod 0.0001 = 0, "c4")

This modified my format so that 0.045 came out as 0.04500

In this example I want to see 0.045. If I just set it to always show 4 digits I get 0.0450.

That formula really looks like it should work!

6

Try this:

=Switch(Fields!DocUnitCost.Value * 100 Mod 1 = 0,"c2",
		Fields!DocUnitCost.Value * 1000 Mod 1 = 0,"c3",
		1=1,"c4")
2 Likes
7

That works! I don’t know why, but it does! Can you help me understand the 1=1 part, and why this would work when the other formula wouldn’t?

8

1=1 always evaluates to true, so it functions as an “else”. For some reason (like the modulus issue above) I ran into an issue with SSRS when just stating true. 1=1 accomplishes the same thing in the fewest number of keystrokes.

Also, the other formula doesn’t work because SSRS evaluates n * 100 mod 1 differently from n mod .01 because :person_shrugging: It has nothing to do with the last case.

2 Likes