In one of my columns in SSRS, I have the expression as
=ROUND(Fields!Contribution_Margin_.Value,5) & "%"
As you can see I am hardcoding the percent symbol after rounding off the value till decimals. But the issue is that the user wants to see only 2 decimals in the SSRS report and when he export it to excel he want 5 decimals while clicking on that particular cell.
So I went into Text box properties and in the Number section, I rounded off to 2 but it is not happening as I am having the "%" symbol, in the end, so after this also it is giving me till 5 decimals in the report.
Any solution how to make it to 2 decimal places in SSRS.
Don't mix value and display - leave the value as is (instead of turning the Value property into a string with the format hard coded into it, thereby losing precision and type) and use the display properties to show that value in the precision required.
Make the cell value just be the field value: Fields!Contribution_Margin_.Value. This allows it to be exported as a number rather than text.
Set the Format property of the cell to display percentage to two decimal places: p2
When exported to Excel, the full field value will be there and the client can format it to 5 decimal places.