Home » Developer & Programmer » Reports & Discoverer » Oracle Discoverer Problem
Oracle Discoverer Problem Tue, 05 June 2007 08:09
 kkesari Messages: 12Registered: 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).

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
 skooman Messages: 913Registered: 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
 kkesari Messages: 12Registered: 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: Wed Jun 28 07:19:32 CDT 2017

Total time taken to generate the page: 0.09854 seconds