Hi everyone

I have one master table with the following structure

incm_id Category SubCategory

1001 Expenses A
1002 Expenses B
1003 Income C
1004 Income D

I have a child table where i am capturing amount value for the above table and structure is something like

id fk_incm_id Amount

10001 1001 25
10002 1002 30
10003 1003 40
10004 1004 50

Now using Discoverer plus i need to show this information which i am able to do

But i then need to show total where i must deduct the values of expenses from income (i.e. Income - Expense)
I tried using decode to make the values of expenses negative but i dont want the values to be displayed as negative and still able to achieve my task
Please help me if there is a way out

Do you have to have the child table in this format? It would be easier if the income and expenditure were in different columns. You could create two views, one for income and the other for expenditure then the query would be simple.

If you can't do it this way then you may be able to create some derived columns in discoverer based on the category type and build totals from there.

Alistair

My last reply wasn't very clear. If you can't have income and expenditure in different columns then try creating a view for income and one for expense. A simple join on the views would give you the results.

PS Not sure if a pivot table could be used to help get the totals?

Alistair

Hi Alistair

U did not get my problem. I my able to get both income and expenditure items in the report

But to calculate profit, I have to deduct income-expenditure. I tried using decode to negate the values of expenditure but problem is that they are being shown as negative values in the report (which is not acceptable). The report should show expenditure value as positive but as same time should be able to be deducted from income values

hope now u got my problem

also i am creating reports using Discoverer plus

regards

I do understand the problem. Is it possible to use pl/sql in a derived field to make the total?

Alistair

Or use something like this

select sum(decode(fk_incm_id,'1003',amount, 0))+sum(decode(fk_incm_id,'1004',amount, 0))
-sum(decode(fk_incm_id,'1001',amount, 0))+sum(decode(fk_incm_id,'1002',amount, 0))"PROFIT/LOSS"
from child_table;

Alistair