hinamansoor
asked on
Group wise total of Block in oracle form
i have a block with these column
Code: [Select all] [Show/ hide]
srno, fin-year Ename All_Amount C_amount
1 2012-2013 A 50 10
2 2012-2013 B 30 10
3 2012-2013 C 10 20
========================== ========== ========== ========== =
Total 90 40
========================== ========== ========== ========== ====
srno, fin-year Ename All_Amount C_amount
1 2013-2014 d 20 5
2 2013-2014 F 30 10
========================== ========== ========== ==========
50 15
i want to total block fin_year wise as i mention above how i can handled in block.
how i can add summary?
Thanks in Advance
Hina mansoor
Code: [Select all] [Show/ hide]
srno, fin-year Ename All_Amount C_amount
1 2012-2013 A 50 10
2 2012-2013 B 30 10
3 2012-2013 C 10 20
==========================
Total 90 40
==========================
srno, fin-year Ename All_Amount C_amount
1 2013-2014 d 20 5
2 2013-2014 F 30 10
==========================
50 15
i want to total block fin_year wise as i mention above how i can handled in block.
how i can add summary?
Thanks in Advance
Hina mansoor
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry , I did only test the select itself
change
create or replace yourview
by
create or replace view yourview
change
create or replace yourview
by
create or replace view yourview
ASKER
Sir view is create and i called it also on canvas but there is no group total simple data is showing as we normally table called on canvas. attaching result picture. Sir what would be the next step after called view on canvas.
Thanks
Hina mansoor
result-picture.PNG
Thanks
Hina mansoor
result-picture.PNG
Do it again (calling on canvas) (i assume you use the wizard) choose layout style tabular in stead of form (that results in all columns of a row on 1 line) and choose records displayed at 15 or 20 so there will be more then 1 row visible on the screen.
Otherwise use the layour editor to place the columns on a row and change the block-property 'number of records displayed' to the value you want.
Otherwise use the layour editor to place the columns on a row and change the block-property 'number of records displayed' to the value you want.
ASKER
Yes it working fine for current year it showing me a TOTAL my question is to you Sir if i would put some more data for 2013-2014 it will give me TOTAL under the 2012-2013 TOTAL or not. Attaching snap.
Once again Thank you very for helping me a lot.
Thanks
Hina mansoor
result-total.PNG
Once again Thank you very for helping me a lot.
Thanks
Hina mansoor
result-total.PNG
Yes , because of the order by in the query.
This is the way i tested it: (based on your example data)
with yourtable
as
(
select 1 srno, '2012-2013' fin_year, 'A' ename , 50 all_amount, 10 c_amount from dual union
select 2 srno, '2012-2013' fin_year, 'B' ename , 30 all_amount, 10 c_amount from dual union
select 3 srno, '2012-2013' fin_year, 'C' ename , 10 all_amount, 20 c_amount from dual union
select 1 srno, '2013-2014' fin_year, 'd' ename , 20 all_amount, 5 c_amount from dual union
select 2 srno, '2013-2014' fin_year, 'F' ename , 30 all_amount, 10 c_amount from dual
)
select * from
(
select to_char(srno) srno, fin_year, Ename, All_Amount, C_amount
from yourtable
union
select 'Total' , fin_year, null, sum(All_Amount) , sum(c_amount)
from yourtable
group by fin_year
)
order by fin_year
, case srno
when 'Total' then 1
else 0
end
/
This is the way i tested it: (based on your example data)
with yourtable
as
(
select 1 srno, '2012-2013' fin_year, 'A' ename , 50 all_amount, 10 c_amount from dual union
select 2 srno, '2012-2013' fin_year, 'B' ename , 30 all_amount, 10 c_amount from dual union
select 3 srno, '2012-2013' fin_year, 'C' ename , 10 all_amount, 20 c_amount from dual union
select 1 srno, '2013-2014' fin_year, 'd' ename , 20 all_amount, 5 c_amount from dual union
select 2 srno, '2013-2014' fin_year, 'F' ename , 30 all_amount, 10 c_amount from dual
)
select * from
(
select to_char(srno) srno, fin_year, Ename, All_Amount, C_amount
from yourtable
union
select 'Total' , fin_year, null, sum(All_Amount) , sum(c_amount)
from yourtable
group by fin_year
)
order by fin_year
, case srno
when 'Total' then 1
else 0
end
/
ASKER
gr8 logic Sir how would i implement this code in oracle form in tabular
Thanks
Thanks
You don't have to. You have already created the form using the view.
To test 2013-2014 either add the records of 2013-2014 in your test database.
Or replace the view you already made with the testquery
create or replace view yourview as
with ... (same code as testquery)
To test 2013-2014 either add the records of 2013-2014 in your test database.
Or replace the view you already made with the testquery
create or replace view yourview as
with ... (same code as testquery)
ASKER
Sir you are really gr8, it worked Thanks you very much Sir
Thanks
Hina mansoor
Thanks
Hina mansoor
ASKER
ERROR at line 1:
ORA-00922: missing or invalid option
Thanks
Hina mansoor