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 experts, We are using IBM AIX 5.3L OS on IBM Servers. Oracle Database EE 10G 10.1.0.4.0, RAC and ASM Presently There are 3 diskgroup (Filesystem) in my DB suppose +ABC +DEF +GHI Now I want to add a new Diskgroup (File system) Say +XYZ and I want t [More]
Hi all experts! I have a question concerning sapscript and Intrastat. First some background information: Country's inside EU have to inform to the locally central Statistic bureau. This information can be printed out from SAP. For my case we have to [More]
hi! My goal is to change HAP_DOCUMENT BSP to allow Further Participants(FP) to change goal setting and appraisal documents. for this i am changing the page document.where.participated.htm  to enable the FP to change document(employee columns ONLY (ob [More]
Hi All, I have a report in IR for which essbase is the source. When i try to process the report i am getting an error as below. Request [Report] from user[username] was terminated since it exceeded the time limit Any idea on how this could be resolve [More]
I haven't been good about authorizing and reauthorizing computers over the years. My daughter has her iPod that is partially synced to this and that computer. She has dropped music on it from iTune Libraries I have had, but we have a problem now beca [More]
i am currently experiencing problems adding , then connecting to stations on nokia net radio app i have a n95-2 running firmware v30.0.018 i have managed to install correctly net radio and net radio keyspace, but when i add my friends radio station a [More]
I have two MBPs a personal one and one from work. I am backing up to an external drive but I just realized they are both named the same. Can I rename one? Thanks for your help.Yes, from the Sharing pane of System Preferences. (60970)Read other 2 answ [More]
Hi, Just set up my Snow Leopard Server and am trying to add a Wiki icon via the web interface. Does not matter what computer I am on, when I select any type of image, JPEG, PNG, TIFF, doesn't matter. I get an error message. "https://ljeproductions.co [More]
1.Can we create user defined triggers?if yes...how? 2.How can we use the search functionality? 3.What is the use of relations in object navigator of forms? 4.What is the use of graphics which is under canvas node in object navigator? 5.Can we use mor [More]
Hi All, We are trying to create a persistent javascript cookie in $OA_HTML/Appslocallogin.jsp to monitor EBS sessions through Oracle RUEI. 1. we are not sure that the custom javascript cookie cookie creation code to be added in $OA_HTML/Appslocallogi [More]