Function give me an Error

building reports webintelligence
1

Hi All-

First off I am using Business Objects Enterprise XI Release 2. I have this report that shows me all the journal entries posted for an accounting period. One of the fields is called “Actual”. This is where we see the dollar value for each journal. For example let’s say we have a journal called ABC123.

Journal ID - Journal Line - Actual - Description
ABC123 - 1- 5,000 - Rent Expense
ABC123 - 2 - (5,000) - Owe Rent

For the Actual field I will see the 5,000 and the (5,000). What I wanted to do is separate anything that is a debit (positive number) and anything that is a credit (negative number). So I created a two columns next to the Actual field column. One of the columns was called Debit and the other was called Credit and I used the following formula:

For Debit
=If([Actual]>0;[Actual];“0”)

For Credit
=If([Actual]<0;[Actual];“0”)

I make Debit and Credit fields into variables so I can use them in a crosstab.

The main issue I am having is when I create a crosstab in BO. I am able to bring over the Debit and Credit variables to the crosstab but I am UNABLE to do a sum of the variables. I tried going back to the variable and editing the formula to the following:

For Debit
=ToNumber(If([Actual]>0;[Actual];“0”))

For Credit
=ToNumber(If([Actual]<0;[Actual];“0”))

But I notice I get errors for Debit and Credit, specifically #ERROR, for Actuals that have a comma in them (i.e., 3,000 or 5,678, (3,500), etc.). Actuals with only three digits come out fine.

I was thinking maybe if I can convert the text string to numbers I can do a summation when I create a crosstab.

Can someone please help me on this? I will greatly appreciate any help on this matter.

Thanks!

-Luis

lconstance :us: (BOB member since 2012-04-26)

2

Try doing a replace on the “,” in your data before the tonumber() call, i bet stripping that out gets you set.

B

bdouglas :switzerland: (BOB member since 2002-08-29)

3

Try formating the Actual Object in the Universe to 12345…I mean select a format without the comma’s.

And try changine the variables to

=If([Actual]>0;[Actual])

izak004 :us: (BOB member since 2010-11-10)

4

Thanks it worked!

lconstance :us: (BOB member since 2012-04-26)