Funky formatting of percentages in Excel export

I've created a report that has cells in it that I want to be formatted as percentages. I set the format code in the cell properties to "P1" and everything appears to work fine until I export to Excel. Some cells show up with only one digit after the decimal and some show up with two digits after the decimal.

I looked at the formatting in Excel (right click, Format Cells...) and it is set to "[$-1010409]#,##0.0#%". I don't have the first clue what that is "supposed" to do but what it does is this:

If I enter 100.00 in the cell it shows up as 100.0%. Expected

If I enter 100.10 in the cell it shows up as 100.1%. Expected

If I enter 100.15 in the cell it is displayed as 100.15%. Not expected

I have formatted the report to only show one digit after the decimal, but RS exports to Excel with some funky formatting that sometimes shows one digit and sometimes two digits after the decimal. Why wouldn't it simply format the cell as a percent? How can I get it to only show a single digit after the decimal?

This is Excel 2007 but Excel 2003 has the same behavior.

Thanks

--John

John T. Williams wrote:

"[$-1010409]#,##0.0%"

Try that in Excel.

|||

I'm not sure what that reply is supposed to be suggesting. The format code [$-1010409]#,##0.0% already exists in an Excel spreadsheet that was created when exporting a report.

The problem is that the format code in Excel (created by Reporting Services) is causing inconsistent formatting of percentages.

|||

You said that excel formated the cell to this:

[$-1010409]#,##0.0#%

I'm saying to try this:

[$-1010409]#,##0.0%

I'm thinking the last # tells it to display the second decimal place if the number has one.

|||

Sorry, I didn't see that anything was different in the code you included. Yes, that does work. However, the problem is that I don't know of a way to control what Reporting Services tells Excel to use as a formatting code. So, the question remains, why does Reporting Services create an Excel formatting code that causes inconsistent behavior? Is there a workaround for this? If this is a bug, how can I report it so that it gets looked at?

This report runs and exports using a subscription and is emailed out automatically to a group of people. The whole point of automating this was that nobody would have to go in and change anything. This certainly isn't a critical issue, it just looks unprofessional.