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.
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".
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
Best Regards,
__________________________________________________
Sincerely,
Rex Lin
Microsoft Online Community Support
If there is any question or the issue is not resolved, please feel free to mark the thread as not resolved
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.
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
text.
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
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.
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.
Participant
1433 Points
504 Posts
Export report to excel - format items as numbers
Jun 25, 2007 03:56 AM|Yani Dzhurov|LINK
Hi,
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:
=FormatNumber(Fields!someField.Value,0)
or
=FormatNumber(Fields!someField.Value,2)
Tried a second approach to use: "Format" property to "g" or "N" as specified here:
http://msdn.microsoft.com/en-us/library/ms157406.aspx
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?
Thanks,
Yani
Star
13358 Points
2110 Posts
Re: Export report to excel - format items as numbers
Jun 28, 2007 03:49 AM|Rex Lin - MSFT|LINK
HI, Yani:
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?
__________________________________________________
Sincerely,
Rex Lin
Microsoft Online Community Support
If there is any question or the issue is not resolved, please feel free to mark the thread as not resolved
Participant
1433 Points
504 Posts
Re: Export report to excel - format items as numbers
Jun 28, 2007 04:35 AM|Yani Dzhurov|LINK
Hi,
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".
Thanks,
Yani
Star
13358 Points
2110 Posts
Re: Export report to excel - format items as numbers
Jun 29, 2007 02:33 AM|Rex Lin - MSFT|LINK
HI, Yani:
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
__________________________________________________
Sincerely,
Rex Lin
Microsoft Online Community Support
If there is any question or the issue is not resolved, please feel free to mark the thread as not resolved
Participant
1433 Points
504 Posts
Re: Export report to excel - format items as numbers
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,
Yani
None
0 Points
14 Posts
Re: Export report to excel - format items as numbers
Nov 21, 2007 06:48 AM|sups123|LINK
Hi
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
Sups123
Member
10 Points
3 Posts
Re: Export report to excel - format items as numbers
Nov 28, 2007 04:07 AM|psubbus|LINK
Hi Yani,
Try the below code
=FORMATNUMBER(Fields!LICENSE_FEE_AMOUNT.Value,2,True, True)
where LICENSE_FEE_AMOUNT is the name of the value that is being displayed.
2 is the number of decimal places.
Thanks
Subbu [:)]
None
0 Points
1 Post
Re: Export report to excel - format items as numbers
Nov 29, 2007 12:11 PM|praveen karnam|LINK
Hi
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 text.
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
None
0 Points
47 Posts
Re: Export report to excel - format items as numbers
Mar 03, 2008 09:30 AM|Joanne wong|LINK
Hi,
I've encountered the same problem. I wondered did you guys solve the problem yet. If so, please post the your solution.
Thanks.
Regards,
Joanne
None
0 Points
2 Posts
Re: Export report to excel - format items as numbers
May 16, 2008 10:18 AM|jlenz|LINK
Hi,
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.
Contributor
6678 Points
2004 Posts
Re: Export report to excel - format items as numbers
May 16, 2008 10:24 AM|satalaj|LINK
do you have registry like this on server
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]
"win32"="C:\\WINDOWS\\System32\\msexcl40.dll"
"DisabledExtensions"="!xls"
"ImportMixedTypes"="Text"
"FirstRowHasNames"=hex:01
"AppendBlankRows"=dword:00000001
"TypeGuessRows"=dword:00000000
None
0 Points
2 Posts
Re: Export report to excel - format items as numbers
May 16, 2008 10:42 AM|jlenz|LINK
yep!
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.
None
0 Points
2 Posts
Re: Export report to excel - format items as numbers
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.
Member
60 Points
49 Posts
Re: Export report to excel - format items as numbers
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.
Regards,
becklery.