Post reply

Export to excel Formatting Problem

  • 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

  • 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 -

  • 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

  • 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

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

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