Last post Oct 27, 2009 01:17 PM by becklery
Jun 25, 2007 03:56 AM|Yani Dzhurov|LINK
I have a trouble when exporting a local report to excel.
The fields are exported to excel as text despite they are numbers and i have explicitly formatted them as such:
Tried a second approach to use: "Format" property to "g" or "N" as specified here:
but this again does not result in "Number" format in excel but in a custom one. But I'm required to output these fields in number format, not custom one. So the second option does not work for me.
Tried a third approach to do some casts like CDbl, but again this result in custom format.
There should be some way to instruct the report to output these fields in number format.
Does anyone knows it?
Jun 28, 2007 03:49 AM|Rex Lin - MSFT|LINK
What is the meaning of 'Custom '? From what kind item value you want to convert it into a "Number".
Would you please give us a sample for your scenario?
Jun 28, 2007 04:35 AM|Yani Dzhurov|LINK
Thanks for your reply.
My report contains multiple columns of integer and decimal values, which are shown perfectly in the Report Viewer.
However, that report is mainly used when it's being exported to excel.
However, when the report is exported to excel the integer and decimal values don't remain numbers any more, but are rendered as text.
But I need them in Number format.
Doing, what I've written above results in a custom format - which means, if you open an excel doc and select a cell -> right click on it -> "Format Cells" -> On the first Number Tab, there are multiple categories. So I need my cells to be in category "Number"
, instead of "Custom".
Jun 29, 2007 02:33 AM|Rex Lin - MSFT|LINK
Yes, i have gave it a try
1.When i set the 'Format' property of the column to C or N or CInt(Fields!someField.Value), it results to be the 'Custom Format' in the formatting property in the EXCEL
2.When i set FormatNumber(Fields!someField.Value,"N"), it results to be the 'General Format' in the formatting property in the EXCEL.
But i have not found any solution to make it render as the "Number Format" in the Excel. I will get inside into it in the following days and inform you whether i can get the answer ASAP.
If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.
I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance
Jul 01, 2007 07:19 AM|Yani Dzhurov|LINK
Hi Rex Lin,
Thanks a lot for you investigation.
You have correctly understood my question.
I would be very grateful in case you manage to find an answer to it :)
Thanks in advance,
Nov 21, 2007 06:48 AM|sups123|LINK
I am facing the same problem like the integer/number data rendered into excel is considered as General/Custom format instead of Number.
I tried with Int() in reports and it is too rendered as General format....
Actually I also have some percentage values in cells of a report but those are considered as text when exported to excel. I hae to format them as Percentage only. Here i have used FormatPercent( ) in reports
If you have found any solution for the above issue then could you please share it with me too. Your quick reply will be very helpful as I have to fix this issue ASAP.
Thanks in advance
Nov 28, 2007 04:07 AM|psubbus|LINK
Try the below code
where LICENSE_FEE_AMOUNT is the name of the value that is being displayed.
2 is the number of decimal places.
Nov 29, 2007 12:11 PM|praveen karnam|LINK
I think this can help you out..
Excel will interpret only some set of data types. If the field that is returned is some expression and the return type is not always specific data type then it will interpret as string. Let me explain this.
Consider you have expression like =iif(Fields!txIUnit.Value=1,fltQ1Actuals,'To Be decided'). In this example since the return type might be float or string, it will always interpret as string. that is why when downloaded in excel format it identifes it as
Try converting the expression into Decimal or Int or double, then it will be represented as text in the excel. It will be pure number.
I mean..Try as below
=CDbl(iif(Fields!txIUnit.Value=1,fltQ1Actuals,'To Be decided'))
Though the above code returns number if the condition is satisfied(txIUnit.Value=1), in other case it shows '#Error'
Please let me know if it makes sense what i have explained
Mar 03, 2008 09:30 AM|Joanne wong|LINK
I've encountered the same problem. I wondered did you guys solve the problem yet. If so, please post the your solution.
May 16, 2008 10:18 AM|jlenz|LINK
from my point of view it mostly happend if you change from one formating(english/RS) to another (e.g. german/Excel) and :
1. For formulas using IIF
2. In Groupings
And for some calculations there's a correct formating in Excel, sometimes not.
Just happend: a "single" IIF statement occured a text in excel, but for a further field with combined IIF's like =IIF(...) - IIF(...) I got numbers...
I really wonder if it is a RS or an Excel error.
May 16, 2008 10:24 AM|satalaj|LINK
do you have registry like this on server
May 16, 2008 10:42 AM|jlenz|LINK
but what to enter instead of "text" ?
I also just tried the
=CDbl(...) Version and it's working fine in a first test. But Probably not for percentages.
Oct 26, 2009 10:11 AM|farreachchad|LINK
I ran into a similar issue where I wanted to keep the numbers and percentages formatted in the report. However, due to the formatting they were not treated as numbers in excel.
Instead of exporting to excel, I exported to .csv. This removed all the styling which I assume was causing the issues with treating the formated numbers and percentages as strings.
Oct 27, 2009 01:17 PM|becklery|LINK
In my opinion, you can have a try of RAQ Report. For it allows users to export report to Excel fiel without any difference, and the operation can not be more easier. The only thing you need to do is selecting related item in the menu.
I wish this could be helpful.