Export to excel Formatting Problem

• Niels Naglé

  Ten Centuries

  Points: 1058

  More actions

  April 22, 2008 at 3:09 am

  #184647

  Good morning all,

  I have some problems when i export my report to excel, the values are converted to text. I have several reports that i explicitly cast the values to CInt or Cdec and this works fine. But now i have a problem with some reports and they occur with the following kind of expression:

  Expression:

  “=IIF((sum(Fields!Something.Value)+ sum(Fields!SomethingElse.Value)) = 0 , “-”,(sum(Fields!Something.Value)+sum(Fields!SomethingElse.Value)) )”

  The value is constantly converted to Text. The following options don’t work:

  - converting the last part to the CInt()

  - Giving the format of the textbox C0 or N0

  When i replace the “-” with “0″ it works fine but i really need the “-” to be placed in the textbox.

  So if anyone have an idea please let me know

  Thanks in advance

• Neuro

  Ten Centuries

  Points: 1063

  More actions

  April 23, 2008 at 3:02 am

  #805930

  How about trying a formating for that particular field

  In the properties for that field, in the formatting tab try using this as:

  £#,##0.00;-£#,##0.00;-

  the last dash represents the zero , and when I export the data to excel this remains a zero, but is masked with the -

• Kevin-458339

  SSCrazy

  Points: 2717

  More actions

  April 23, 2008 at 3:24 am

  #805942

  If you are able to format the Excel document before hand that would be an advantage, but if this does not work or you are not able to then try this:

  One of our developers had a work-around, not pretty but it worked, he placed a ' (single text quote) in front of the value being placed into the cell.

  This ensured that the value being placed into the cell forced the field to be a text field - you do not see the ' on the sheet - but it is actually there.

  This may work for you after you have tried all other options

  Let us know

  Thanks

  Kevin

• Niels Naglé

  Ten Centuries

  Points: 1058

  More actions

  April 23, 2008 at 7:52 am

  #806060

  Hey all,

  The solution to the problem is:

  In the properties for that field, in the formatting tab use the following format: ##0;-##0;-

  Besides that you also need to explicitly cast the data to an CInt like:

  = CInt (

  IIF(

  SUM(something) + SUM(Somethingelse) IS NOTHING,

  0,

  SUM(something) + SUM(Somethingelse)

  )

  )

  Thanks for the replys on the answer and providing the part of the solution.

  Greetings Niels