Spliting data in the single column into two columns in Cognos Report Studio..??

[pooja]

Hi All,

Is there a way to spliting data in the single column into two columns in Cognos list report? Below are the example.....

FROM:
---------
code - items - amount
-----------------------------
A       Furniture          500
B       Computer        1000
C       Maintenance   (700)
D       BusinessTrip   (200)
....
....


TO:
----
code - items -        debit           Credit
-----------------------------------------------
A       Furniture          500            0
B       Computer        1000           0
C       Maintenance       0          (700)
D       BusinessTrip       0          (200)



Much appreciate you help.

P

[pricter]

Do you have a rule in order to split the column?

[pooja]

Thank you.

Yes I do have. and it is based on account number.
but while doing case statement based on account number I am able to get only positive number (debit column) which correct. But for with the same logic for Credit Column - same debit column numbers are repeated.

[pricter]

As far I see you should create two data items
one for credit and one for debit

Credit

If ([Code1] in ('A','B')) then ([Amount]) else (0)

Debit

If ([Code1] in ('C','D')) then ([Amount]) else (0)

[pooja]

Thank  you...but NO LUCK

Again DEBIT column is correct but I got all '0' value on CREDIT data item (column).

[Lynn]

What expression do you have for your debit and credit query items? Or try to mock up a similar situation using the Cognos sample data and post the XML. Then people will have more to go on to try to assist.

[Satheesh]

Hi Pooja,


do like this
Create One Data Item DT1 with below logic.


if([amount] < 0 ) then
('Credit')
else
('Debit')


In cross tab
Drag Code and Items in Rows
Drag DT1 in Columns
Drag amount in Measure


You will get