Home » Developer & Programmer » Reports & Discoverer » Oracle Discoverer Problem
Oracle Discoverer Problem [message #242905] Tue, 05 June 2007 08:09 Go to next message
kkesari
Messages: 12
Registered: June 2007
Location: MCLEAN, VA
Junior Member

Hi ,

I need your help / suggestions on how to achieve the following in Oracle Discoverer.

I am having 2 custom tables, using a UNION in the Discoverer query, showing the report as

1. sum of amount column from Table A - always 1 row.
2. sum of amount columns from Table B , but group by a criteria and will be many rows depending on the parameters.

I need to show the difference between the amounts as explained below.

Eg.

1st row: Trial Balance for Jun'06 - 100$
2nd row: For schedule 1 - 10$
3rd row: For Schedule 2 - 30$
4th row: For Schedule 3 - 40$
5th Row: For Schedule 4 - 10$
========================================
Difference - 10$
========================================

How do I achieve this in Discoverer. It seems i can do a minus only between two adjacent cells in a single row.

I want the difference between the 1st row and the (sum of all the rest of the rows).

Please help me to achieve this in Oracle Discoverer.
Version: Oracle Discoverer Desktop 10.1.2.48 on Oracle 10G database.

Any suggestions are highly appreciated.

Thanks & Regards,
Kiran Kesari
Re: Oracle Discoverer Problem [message #243014 is a reply to message #242905] Tue, 05 June 2007 13:40 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
As far as I know, you will have to do this in the database itself (create a view), but it will be quite a good SQL task...

On the other hand, why not make one of the sides of the equation (so, results from table A or B in your case) a negative, then you can just sum all results, like:

1st row: Trial Balance for Jun'06 - 100$
2nd row: For schedule 1 - -/-10$
3rd row: For Schedule 2 - -/-30$
4th row: For Schedule 3 - -/-40$
5th Row: For Schedule 4 - -/-10$
========================================
Difference - 10$
========================================
Re: Oracle Discoverer Problem [message #243510 is a reply to message #243014] Thu, 07 June 2007 10:32 Go to previous message
kkesari
Messages: 12
Registered: June 2007
Location: MCLEAN, VA
Junior Member

As you suggested, I reversed the sign and doing a sum.
This works fine to me. Its easy and quick.

Thanks a lot.

Regards,
Kiran Kesari
Previous Topic: Creating a Oracle report and publishing it to the Oracle Portal
Next Topic: Oracle Discoverer report shows drilldown icon in Web version
Goto Forum:
  


Current Time: Sun Dec 04 20:32:54 CST 2016

Total time taken to generate the page: 0.08485 seconds