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
I would check the field settings. Other than that, I think you are correct in needing a fancy statement.
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.
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…
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!
Try this:
=Switch(Fields!DocUnitCost.Value * 100 Mod 1 = 0,"c2",
Fields!DocUnitCost.Value * 1000 Mod 1 = 0,"c3",
1=1,"c4")
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?
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 It has nothing to do with the last case.