Export report to excel - format items as numbers

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://msdn2.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

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?

|||

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

|||

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

|||

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

|||

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

|||

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

|||

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