Export to Excel

Question

Export to Excel

2Tall

SSChampion

Points: 14632
More actions
September 15, 2010 at 4:35 am

#229297

Hi. I have a report that accounts run via reporting services then export to excel. There main moan is the formatting once in excel.
The following is representative of code within the dataset:
ROUND((CurItemValue / Quantity * QuantityOutstanding),2) AS CurValueOutstanding,
Question: Can the code be modified so when exported to excel it is in the desired number format (as opposed General)?
I would guess the answer is no as the formatting is down to Excel.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35545 More actions · Answer 1

Have you tried setting the format property of the text box that contains the value you are having issues with? Instead of using the FORMAT() function inside of an expression, use the property of the text box. That should transfer over to excel when it's exported, or at least it does when I export dates formatted as such.

You probably want to use the standard Decimal format code from here http://msdn.microsoft.com/en-us/library/aa720653.aspx

For to convert .050 to .05 you would use d2

Hopefully that helps,

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

tsmith-960032 SSCrazy Points: 2518 More actions · Answer 2

You can select multiple, or single cells to set the format properties as Luke says above.

Examples of what you may put in the format field are :

c2 (currency, 2 decimal places)

n2 (number, 2 decimal places)

n0 (number, no decimal)

d (date format)

t (time format)

You can google this issue and come up with numerous formats.

Luke L SSC-Dedicated Points: 35545 More actions · Answer 3

tsmith-960032 (9/15/2010)
You can select multiple, or single cells to set the format properties as Luke says above.
Examples of what you may put in the format field are :
c2 (currency, 2 decimal places)
n2 (number, 2 decimal places)
n0 (number, no decimal)
d (date format)
t (time format)
You can google this issue and come up with numerous formats.

Thanks for the correction there... I just relized I was thinking 2 decimal places and is why I said to use d2. But that's a date format.

Sorry for any confusion. N2 was what I meant.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

2Tall SSChampion Points: 14632 More actions · Answer 4

Thanks for posting, I will look into the formatting as suggested.

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper