Home » Developer & Programmer » Reports & Discoverer » Running total (merged threads)
Running total (merged threads) [message #177114] Tue, 13 June 2006 02:26 Go to next message
mfa786
Messages: 210
Registered: February 2006
Location: karachi
Senior Member
Sir I have tow table in accbal have opening balance and in voudetail have daily transaction I need ledger report with opening balance
My report get opening balance from accbal and get befor given date balance from voudetail

Such as
For opening balance

Selelect case when dr>cr then dr-cr
When cr>dr then cr-dr
End opbal from accbal

For get balance form daily transaction

Select case when sum(debit)-sum(credit)> sum(credit)-sum(debit) then
sum(debit)-sum(credit)
when sum(credit)-sum(debit)>sum(debit)-sum(credit) then
sum(credit)-sum(debit)
end trbalance from voudetail
where date <givendate

cal acute both balance in variable

total =opbal+trbal

and send this total in report as opening balance on top of report and add this amount asper condition in amount report amount is debit and totalbal is debit then
placeholdercolumn=totalbal+reportdebit
or credit then
placeholdercolumn=totalbal-reportdebit

sir I need this type of calculation in report


or

if you give me idea n query is best for me I calculate with over function and use report only for output

please sir give me idea urgent

thank

aamir
Re: how I get running total by using tow table in repot balance pass by form parameter [message #177123 is a reply to message #177114] Tue, 13 June 2006 02:53 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
One tip: no need for CASE. Your Query can be written with the ABS function.
SELECT ABS(SUM (debit) - SUM (credit))
FROM   voudetail
WHERE  DATE < givendate
Running totals can be accomplished with analytic functions. I'll post a small example later on.

MHE
need help in pr [message #177149 is a reply to message #177123] Tue, 13 June 2006 04:51 Go to previous messageGo to next message
mfa786
Messages: 210
Registered: February 2006
Location: karachi
Senior Member
hi master

how i use emternal variable in query
Select accid,sum(debit)-&externalvariable accbal form accbal where accid= accode;


please give me idea

thank
runing total when group change add master column in detail runing total [message #177344 is a reply to message #177114] Wed, 14 June 2006 04:39 Go to previous messageGo to next message
mfa786
Messages: 210
Registered: February 2006
Location: karachi
Senior Member
hi master

how runing total when group change add master column in detail runing total

Sir I have

I have master detail table
I master I have column

Accid
Opdr
Opcr

Detail table column
accid
Debit
Credit

Sir I create report in report I need accumulative total or running total
Then I use one summary column in detail section and my result right but
I need add the opdr or opcr from master table in summary column when group change
Then add opdr in summary column and display this addition only in first row of group changing

Such as we use in formula column and placeholder column

If group= change then
:placeholdercolumn := :debit+:opdr
Else
:placeholdercolumn := :placeholdercolumn +:debit
End if;
Return :placeholdercolumn;

My question is how I know when group change
If I get when group change then I add master opdr in detail dr column

Pleas give me idea how I add master column in detail running column when group change

Thank

aamir

Re: runing total when group change add master column in detail runing total [message #177375 is a reply to message #177344] Wed, 14 June 2006 06:48 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Do you mean something like this:
/forum/fa/1189/0/

I have done the following:
1. query:
SELECT departments.department_id,
       employees.department_id,
       employees.employee_id,
       employees.last_name,
       employees.hire_date,
       employees.salary,
       departments.department_name
FROM   departments, employees
WHERE  departments.department_id = employees.department_id
The fields from departments are dragged upward to create a group. In this group I've added a summary column that sums the salary and set it to reset at report level.

There you have your running total.

MHE
  • Attachment: runtot1.jpg
    (Size: 23.57KB, Downloaded 2516 times)
Previous Topic: discoverer 10.1.2.0.2 Problems (Help Needed)
Next Topic: error "REP-1202: ORACLE logon not specified"
Goto Forum:
  


Current Time: Tue Nov 26 12:35:56 CST 2024