Home » Developer & Programmer » Reports & Discoverer » Sub total in report (Oracle Express edition 10g , Developer 6I)
Sub total in report [message #606998] Fri, 31 January 2014 22:43 Go to next message
shahzaib_4vip@hotmail.com
Messages: 410
Registered: December 2008
Location: karachi
Senior Member
Dear all

I am making Trail Balance and in my Chart of account Control account is 1001,2001 and Detail account is 1001001 to 1001099 & 2001001 to 2001099 when i select total trail balance report i just required the sub total of 1001 and 2001


Regards


Shahzaib Ismail
Re: Sub total in report [message #607044 is a reply to message #606998] Sat, 01 February 2014 13:31 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I have difficulties in understanding messages where the whole text is written in a single sentence with no punctuation. Therefore, I just *think* that you might not be able to do that using a summary column, but using a formula column instead (with your own logic in there).
Re: Sub total in report [message #607090 is a reply to message #607044] Sun, 02 February 2014 22:32 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 410
Registered: December 2008
Location: karachi
Senior Member
Thanks for your reply here is my query of report

SELECT to_char(A.acc_id),
       Initcap(a.acc_name),
       control_detail,
       nvl(a.op_dabit,0)+nvl(dabqt,0)-nvl(cerqt,0)Opening_Balance,
       s.sq Activity_Debit,
       t.tq Activity_Credit,
       nvl(a.op_dabit,0)+nvl(dabqt,0)-nvl(cerqt,0)+nvl(s.sq,0)-nvl(t.tq,0) Closing_Balance
FROM Coa a,
  (SELECT cb.cb_acc_id,
          sum(nvl(cb.dabit,0))sq
   FROM cb
   WHERE vdate BETWEEN nvl(:Date_from, vdate) AND nvl(:Date_to ,vdate)
   GROUP BY cb_acc_id)s,
  (SELECT cb.cb_acc_id,
          sum(nvl(cb.credit,0))tq
   FROM cb
   WHERE vdate BETWEEN nvl(:Date_from, vdate) AND nvl(:Date_to ,vdate)
   GROUP BY cb_acc_id)t,
  (SELECT cb_acc_id,
          sum(dabit)dabqt
   FROM cb
   WHERE vdate < nvl(:Date_from ,vdate)
   GROUP BY cb_acc_id)cbdbef,
  (SELECT cb_acc_id,
          sum(credit)cerqt
   FROM cb
   WHERE vdate < nvl(:Date_from ,vdate)
   GROUP BY cb_acc_id)cbcbef
WHERE cbdbef.cb_acc_id(+)=a.acc_id
  AND cbcbef.cb_acc_id(+)=a.acc_id
  AND s.cb_acc_id(+)=a.acc_id
  AND t.cb_acc_id(+)=a.acc_id
  AND acc_id BETWEEN nvl(:Account_no_from ,acc_id) AND nvl(:Acccount_no_to , acc_id) CONNECT BY
  PRIOR acc_id = CATAGORY_id
  START WITH acc_id BETWEEN 01 AND 1000
ORDER Siblings BY acc_id /



The result of this query is attached

When you see the report i need sub total of Tangible Fixed assets,intangible Fixed assets and as on


I hope you understand now


Regards


Shahzaib Ismail


Re: Sub total in report [message #607101 is a reply to message #607090] Mon, 03 February 2014 00:12 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Layout suggests that this *might* be a "Group above" layout, where titles you mentioned (Tangible Fixed Assets, Intengible Fixed Assets etc.) are master group fields. If that's so, no problem - create a summary column which will break on that group (instead of "Report", which is default).

If it isn't a group above layout but an ordinary tabular report, then you can't do what I described. See if you can switch to group above. If not, you'll have to calculate it manually. A formula column is one option. Another one is to create a new query that is linked with the one you already posted, which calculates values you are interested in. Yet another option is to use analytic form of the SUM function, but Reports 6i can't use it directly, which means that you'd have to create a view, prepare all values, and then display them in report.

There might be another options, but I can't remember any of them. Someone else might so - wait a little bit longer.
Re: Sub total in report [message #607154 is a reply to message #606998] Mon, 03 February 2014 06:01 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 410
Registered: December 2008
Location: karachi
Senior Member
its a group above report , i already create a summary coloum but they summarized all coloum of debit and credit. I Want to summarized after one detail entry complete like tangible assets


Regards


Shahzaib Ismail
Re: Sub total in report [message #607160 is a reply to message #607154] Mon, 03 February 2014 06:14 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Then you don't have a structure that is deep enough. It seems that you don't need just one group above, but 2, 3, 4 or more.
Re: Sub total in report [message #607233 is a reply to message #606998] Tue, 04 February 2014 02:20 Go to previous message
shahzaib_4vip@hotmail.com
Messages: 410
Registered: December 2008
Location: karachi
Senior Member
Thank you sir Little foot

Problem solved




Regards


Shahzaib ismail
Previous Topic: oracle reports 11g R2
Next Topic: Report 10g time out
Goto Forum:
  


Current Time: Thu Mar 28 10:31:21 CDT 2024