Solve problem with the representation of the numbers in Excel by using Export to Excel from Reporting Services

The numbers show correctly in the report. It is used as regional formatting for Germany. The text box property is configured with a number like here:

As report you can see it:

Now I export it to excel and the problem is here. The numbers are displayed as text.

It cannot use for calculation. It should display as numbers. I tried different kind of text properties and it won’t work. I just tried with U.S. format and it works, but this is not our goal to use it.

By chance, I discovered another solution. It should add the format directly in an expression of Reporting Services. At the moment it displays this value:

=IIF(Fields!January.Value > 0, Fields!January.Value,””)
The solution is, use with the function “cDec”

=IIF(Fields!January.Value > 0, cDec(Fields!January.Value),””)
Now it works. It looks the same like before, but the Excel displays as a number and I can set the summary without changing the format of the numbers.

It was really stressfully job for me. I spent lot of time to fix this problem. I couldn’t find any solution in internet. Now I’m happy about it. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *