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 (BOB member since 2012-04-26)