Currency format

building reports webintelligence
1

Hi All,
I am having amount field and I need to show it with $ symbol as a currency.
I did following steps
1.Right click the column and choose format number
2.select the currency and select one of the format available

It works fine with the amounts greater than 0,but when amount is 0 it don’t even shows the value.

Actual Column
Amount

100.00
120.20
0.00

Output I am getting
Amount

$100.00
$120.20

Desired output
Amount

$100.00
$120.20
$0.00

So, how can I achieve that.

GNK_BO (BOB member since 2007-07-17)

2

GNK,write the below format in formula bar.

="$"+[Your Measure Object]

Arjun (BOB member since 2008-07-28)

3

Custom formatting? If it is Desk-I, I would create a custom format like this.
Number -> Positive ($###.00) -> Negative ($###.00)-> Equal to zero ($0.00)

Do you see any such option in Web-I?

Or else, what about an alerter?

Jansi :india: (BOB member since 2008-05-12)

4

Also, make sure that a zero is being returned from your query and not a null. You can use a formula like this in your report:

If (IsNull([measure]);0;[measure])

bkaporch :us: (BOB member since 2008-12-02)

5

Check with this formula:
Create a variable: =If(IsNull([Amount]);0;[Amount])
Measure="$"+FormatNumber([Variable];"(#,##.00)")

Arjun (BOB member since 2008-07-28)

6

Hi All,
Thanks for your suggestions.

Firstly my column don’t return any null values. Its is as shown below

Amount

100.00
120.20
0.00

Jansi,
It is a Webi Report. I added the custom format you suggested
Positive: ($ ###.00)
Negative: ($###.00)
Equal to Zero:($0.00)
It is showing up nothing in that column with the custom format.

bkaproch,
My column has values as 0.00 and don’t have any null values.

Arjun,
Your first suggestion did not work,your next post did but just minor change in the format #,##0.00. I needed to put 0 before the decimal and it worked perfect

Create a variable: =If(IsNull([Amount]);0;[Amount])
Measure="$"+FormatNumber([Variable];"(#,##0.00)")
Thanks for your trick.

GNK_BO (BOB member since 2007-07-17)

7

Format u canot change through properties u have to change the formula by Currency=$"+FormatNumber([Variable];"(#,##0.00)") …

yalisis (BOB member since 2009-07-27)

8

The formula you mentioned doen’t work.Check once again.

Arjun (BOB member since 2008-07-28)