Export to Excel - Negative value in Parenthesis

Sbhat
Sbhat Member Posts: 301
edited 2012-08-10 in NAV/Navision Classic Client
I have a report that exports to excel. On the report i have a variable called 'NetLoss' which is of type Decimal and when there is a negative value then i want to show the value in parenthesis in Excel. For example NetLoss := -5000, i want to show it in excel as (5000) without the negative sign. No matter what i do when it goes to excel it just shows it as -5000, i tried '('+format(Netloss)+'), also i tried DELCHR function to remove the negative sign and no luck and then in MakeDataexcelbody i have

Addcolumn(Netloss,' ',true,true,false).

Does anyone know what can be done to make it work such that it shows (5000).

S.

Comments

  • winfy
    winfy Member Posts: 8
    edited 2012-08-10 
    format(-Netloss)
    or
    format(ABS(Netloss))

    The first one: all values change the sign (vice versa)
    The second one: always positive.

    Decide yourself.
  • Chinmoy
    Chinmoy Member Posts: 359
    Excel treats a column with () as a negative number so copy/paste this code:

    TxtValue := '( ' + FORMAT(ABS(<NegativeDecimalValue>)) + ')';

    and put the TxtValue in the cell. This will work.

    Note: Please copy/paste the code because, the space here '( ' after the ( character is not a blank space.

    Hope this helps.

    Chn
  • Sbhat
    Sbhat Member Posts: 301
    Hi chinoy -

    I copied and pasted your code and it shows in excel as -5000 and it you just put it as '('+format(Netloss)+') it shows it as (-5000) and shows the - sign.
  • Chinmoy
    Chinmoy Member Posts: 359
    See the attached results it generates with me: (NAV 2009 R2)

  • ufuk
    ufuk Member Posts: 514
    Another possibility is inserting #,##0 ;[Red](#,##0);- value to the NumberFormat field in Excel Buffer. (Remove [Red] part if you do not require) This way it will format value using Excel Format Cells property.
    Ufuk Asci
    Pargesoft
  • winfy
    winfy Member Posts: 8
    Oh, okay it is new to me!

    NumberFormat 0,00;(0,00) works too!

Categories