Sitemap

Amicuk Programming Answers

Is it possible to achieve Group By....Having functionality in Discoverer?

2015-10-11   Views:0

Advertisement

Hello All, I need to print amount due for each customer when it is negative (less than zero). i.e. sum of all transactions against the customer should be less than zero. This is simple through sql if we write "select sum(amount) group by customer hav

Hello All,
I need to print amount due for each customer when it is negative (less than zero). i.e. sum of all transactions against the customer should be less than zero. This is simple through sql if we write "select sum(amount) group by customer having sum(amount) < 0".
But I need to do this in discoverer. Now the issue is I need to pass two parameters at runtime. First one will be Date (<= as_of_date) and another one will be having multiple values (in clause).
If I base my report on underlying report view which uses 'group by... having' sql as described above, I will have to use parameter sheet and use syscontext to pass parameters.
Instead, it will be easier if i pull in all the data in discoverer report and then use parameters to filter it out. But then is there any way to group on customer and having clause to print only those customers where sum(amount) < 0?
Anish

The replay answer
Advertisement
Hi Russ,
I am new to discoverer so pardon me if I am not able to get you quickly.
I am sorry, but I did not get you when you say calc1 = SUM( CASE WHEN amount <0 then amount ELSE 0 END).
May be I was not able to describe you problem. Below is the SQL statement that will give me desired results. I want to achieve same results in Discoverer by getting rid of outer select query with grouping. I will be passing org_id and GL date as parameters.
select customer_number, name, sum(amount_due) Amount_Due, org_id, Business_Segment
from
SELECT hca.account_number customer_number, hp.party_name NAME,
aps.trx_number, aps.gl_date,
aps.amount_due_remaining amount_due, aps.class, aps.org_id org_id
,hca.attribute3
FROM apps.ar_payment_schedules_all aps,
apps.hz_cust_accounts hca,
apps.hz_parties hp
WHERE aps.org_id IN (570, 573)
AND aps.gl_date <= '30-NOV-2008'
AND aps.class in ('CM','INV','DM')
AND aps.customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
group by customer_number, name,org_id, Business_Segment
having sum(amount_due) < 0
order by 1
Hope I am able to explain you now.
Thanks,
Anish

Go to See the other 8 answers

Build a target group by Partner Function

Category:DefaultRelease time:-0001-11-30Views:130

Hi forum friends Please I need a help. I want to create a target group by Partner function (BUT100). I have tried to use the join: BUT100(partner field) + BUT0CC(Partner) + BUT051(Partner1) + BUT000(Partner) but it doenst work. How can i do it? Thank[More]

Target Group by partner function (created BP BUT100)

Category:DefaultRelease time:-0001-11-30Views:130

Hi forum friends Please I need a help. I want to create a target group by Partner function (BUT100). I have tried to use the join: BUT100(partner field) + BUT0CC(Partner) + BUT051(Partner1) + BUT000(Partner) but it doenst work. How can i do it? Thank[More]

How to find suitable( existing) function group for a Function Module ?

Category:DefaultRelease time:-0001-11-30Views:130

How to find suitable( existing) function group for a Function Module to be created? This is FM for converting amounts to text.Hi,         If you are not sure into which Function group your FM should go in then its advised to create a Function group o[More]

What happened to the "bookmark this group of tabs" function?

Category:DefaultRelease time:-0001-11-30Views:130

Firefox 6.0 doesn't have a "bookmark this group of tabs" function on the bookmarks menu. What happened to it? Can it still be done? How? This is progress???Thanks. Is it me, or is this completely counterintuitive? I expect the tab context menu t[More]

No entries in Define conversion group and external function module setting.

Category:DefaultRelease time:-0001-11-30Views:130

Hi all, After active bussiness function SAP Oil & Gas, my system have a problem in SPRO. SPRO --> IMG --> Industry solution Oil & Gas (Downstream) --> HPM --> QCI configuration --> Define conversion group and external function modul[More]

How to access the screen from one function group to other function group

Category:DefaultRelease time:-0001-11-30Views:130

Hi Experts, How to access the the screen from one function group to other function group. Please help me on this , Bcoz it is client requirement i need to finish this urgently. Please Thanks BasuHI , You can do a call transaction or submit and return[More]

Business Partner: Valid Acct Groups per Partner Function

Category:DefaultRelease time:-0001-11-30Views:130

Hi, Where can we assign this: Business Partner: Valid Acct Groups per Partner Function Regards, AjitHi, IMG>Sales and Distribution>Basic Functions>Partner Determination>Set Up Partner Determination>Set Up Partner Determination for Customer[More]

Is it possible to achieve Group By....Having functionality in Discoverer?

Category:DefaultRelease time:2015-10-11Views:130

Hello All, I need to print amount due for each customer when it is negative (less than zero). i.e. sum of all transactions against the customer should be less than zero. This is simple through sql if we write "select sum(amount) group by customer hav[More]

How to achieve grouping of data based on one column

Category:DefaultRelease time:-0001-11-30Views:130

Hello PL/SQL Gurus/experts, I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production version I have following table - DROP TABLE T2; create table T2(Manager,Employee) as select 'Nikki','Ram' from dual union all select '[More]

Account Group and partner functions in customer master records

Category:DefaultRelease time:2015-10-11Views:130

Hi Gurus, I need your help to confirm the following understand if correct: 1. To create customer master record for partner function Ship-To, need to create a customer master record using the uniquely assigned account group for Ship-To , that is, 0002[More]

When I 'Tab Groups,' the search function immediately pops up (and it won't go away) every time, preventing me from accessing my grouped tabs. How can I prevent this from happening?

Category:DefaultRelease time:2015-10-11Views:130

Using Firefox 5.0 with Windows 7 on a laptop, whenever I click the 'Group Tabs' icon to the right of my tabs, the search function immediately comes up over my grouped tabs, preventing me from using any of them. When the search function comes up, the[More]

Hot
Hi Gurus, How can i see Log information of Report Run Time?, till now i am counted report run time manually. Is there any way i can see the workbook running time in log information?. Thanks & Regards VikramThere could be a few things - At one time, y [More]
I did a backup from all my files saved in old external hd to time capsule. After that double checked and recover one file from TC to mac air and it worked just perfect. Today went through TC to recover another file and none of them were there?Anybody [More]
HI i just spilled coffe on my laptop,  everything still works as it did before. no keys are sticky or non-functional(using it right now spilled coffe 50 mins ago). But right when it happend i took  out the power source cable and put it on the side to [More]
I am a UK resident and want to watch UK TV whilst travelling.  I have tried various proxy servers but they're always slow.  In the UK I have a Time Capsule that has my router attached and I connect to the internet through the Time Capsule (the old ve [More]
Hullo I have a single stereo track which I'm trying to compare on several different speaker systems - not an unusual task for those that frequent this forum I'd expect. Edit: It's a wee bit more complex than that. The track has a lot of processing (e [More]
I have to make a class in java that searches for information on a text file. My text file is described below. username=Hanako Sony cpname=Sony Corporation cpcode=500100 So, if the user inputs "username", then "Hanako Sony" should be re [More]
I WANT TO KNOW IF THERE IS ANY REASON NOT TO USE WINDOWS XP AS THE ITERATION WHEN I PERFORM THE BOOT CAMP PARTITION ON MY MACBOOK PRO?It could be done depending on when your MacBook Pro was made. Most MacBook Pro models from mid/late 2007 to early 20 [More]
Is anybody using the above? If so, have you developed any scripts to facilitate updating the Rose model, and which you'd be willing to share? Or perhaps discuss jointly writing same? Ideally, there would be an Fscript to facilitate exposting Forte pr [More]
Hi Everyone, Could a kind person with LabVIEW 2009 please save the attached code in LabVIEW 8.6. The code is of the Creating an Office 2007-like Ribbon in LabVIEW community page.  It would be very helpful! PS. Will reward kudos.   Kind Regards James [More]
How do I stop apps from running in background on my iPhone 4s?  I double tap the home button and the running apps will move right and left but no "x" appears to stop them from running.Very few apps actually run in the background: Understand mult [More]