Home » Developer & Programmer » Reports & Discoverer » Z SHAPE TOTAL
Z SHAPE TOTAL [message #440841] Wed, 27 January 2010 05:55 Go to next message
deepak3arora
Messages: 32
Registered: October 2009
Location: chandigarh
Member

how can i calculate this thing :
openin_bal as 2000...

txn 1 as 200
txn 2 as 500
txn 3 as -300


like

2500 200 2700
2700 500 3200
3200 -300 2900


I have 2 database fields : one op_bal and multiple txn

Regards,
Deepak Arora
Re: Z SHAPE TOTAL [message #440911 is a reply to message #440841] Wed, 27 January 2010 22:22 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Please post a test case.....
Z shape total using what?
sql/reports ? what?
Or if you want to read the previous row its a FAQ.Search before posting.

Please read the Forum guide lines....

sriam Smile

[Updated on: Wed, 27 January 2010 22:23]

Report message to a moderator

Re: Z SHAPE TOTAL [message #441267 is a reply to message #440911] Sat, 30 January 2010 00:55 Go to previous messageGo to next message
deepak3arora
Messages: 32
Registered: October 2009
Location: chandigarh
Member


I DIDN'T FOUND ANYTHING IN FORUM REGARDING 2 THIS....


I WANT THIS 2 HAPPEN IN ORACLE REPORTS OR EITHER IN SQL QUERY...


I AM HAVING 2 TABLES (1)"OPENING_BAL" WITH OPENING BALANCES OF EACH PARTY_CODE AND
(2) "TRANSACTIONS" HAVING TRANSACTIONS OF EACH PARTY_CODE


AND I HAVE ALREADY PROVIDED THE TEST CASE
Re: Z SHAPE TOTAL [message #441304 is a reply to message #441267] Sat, 30 January 2010 08:24 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
deepak3arora wrote on Sat, 30 January 2010 07:55
AND I HAVE ALREADY PROVIDED THE TEST CASE

You did? Where? Someone has told you lies about how a test case is supposed to look like. It helps other people to easily create working environment so that they could try to help you. Because, as far as I'm concerned, I can live happily without your (or anyone else's) problems. So, if you need help, why don't you help us to help you?

This is how a test case should look like; try to remember and do it by yourself next time. It contains CREATE TABLE and INSERT INTO statements; if you did it, I wouldn't have to do it myself. Because, who can guarantee that I correctly understood what you were saying?

Besides, I believe that you did it wrong; if opening balance equals 2000, how come the result starts with 2500?

SQL> create table op_bal (party_code varchar2(1), op_bal number);

Table created.

SQL> create table txn (party_code varchar2(1), id number, val number);

Table created.

SQL> insert all
  2    into op_bal values ('A', 2500)
  3    into op_bal values ('B', 1000)
  4    into txn values ('A', 1, 200)
  5    into txn values ('A', 2, 500)
  6    into txn values ('A', 3, -300)
  7    into txn values ('B', 1, 100)
  8    into txn values ('B', 2, -700)
  9  select * from dual;

7 rows created.

SQL> select * from op_bal;

P     OP_BAL
- ----------
A       2500
B       1000

SQL> select * from txn order by party_code, id;

P         ID        VAL
- ---------- ----------
A          1        200
A          2        500
A          3       -300
B          1        100
B          2       -700

SQL>


Here's one way to do that; I'm not saying it is perfect and it probably could be done better, but OK, here you go (let's call it <the_query>, for future reference):
SQL> select y.party_code,
  2    y.id,
  3    lag(y.run_tot) over (partition by y.party_code order by y.id) prev_tot,
  4    y.val,
  5    y.val + lag(y.run_tot) over (partition by y.party_code order by y.id) total
  6  from
  7    (select x.party_code,
  8      x.id,
  9      x.val,
 10      sum(x.val) over (partition by x.party_code order by x.id rows unbounded preceding) run_tot
 11    from
 12      (select 0 id, o.party_code, o.op_bal val from op_bal o
 13      union
 14      select t.id, t.party_code, t.val from txn t
 15      ) x
 16    order by x.party_code, x.id
 17    ) y
 18  order by y.party_code, y.id;

P         ID   PREV_TOT        VAL      TOTAL   
- ---------- ---------- ---------- ----------
A          0                  2500
A          1       2500        200       2700
A          2       2700        500       3200
A          3       3200       -300       2900
B          0                  1000
B          1       1000        100       1100
B          2       1100       -700        400

7 rows selected.

SQL>


How it works? It begins with the UNION of two tables, which emulates a situation where the opening balance represents a transaction (its ID would then be 0):
SQL> select x.party_code, x.id, x.val
  2  from (select 0 id, o.party_code, o.op_bal val
  3          from op_bal o
  4        union
  5        select t.id, t.party_code, t.val
  6          from txn t
  7       ) x
  8  order by x.party_code, x.id;

P         ID        VAL
- ---------- ----------
A          0       2500
A          1        200
A          2        500
A          3       -300
B          0       1000
B          1        100
B          2       -700

7 rows selected.

SQL>

Next, you calculate a running total. It can be done with the analytical form of the SUM function:
SQL> select x.party_code,
  2      x.id,
  3      x.val,
  4      sum(x.val) over (partition by x.party_code order by x.id rows unbounded preceding) run_tot
  5    from
  6      (select 0 id, o.party_code, o.op_bal val from op_bal o
  7      union
  8      select t.id, t.party_code, t.val from txn t
  9      ) x
 10    order by x.party_code, x.id;

P         ID        VAL    RUN_TOT
- ---------- ---------- ----------
A          0       2500       2500
A          1        200       2700
A          2        500       3200
A          3       -300       2900
B          0       1000       1000
B          1        100       1100
B          2       -700        400

7 rows selected.

SQL>

Finally, you format the output by some help of another analytical function, LAG. The result is <the_query>.


Output contains rows with ID = 0. Omit them in report (in Format Trigger, "RETURN (ID <> 0);", or wrap the query with another SELECT, such as
select party_code, id, prev_tot, val, total
from <the_query>
where id <> 0
order by 1, 2;


That should be all, I guess.
Re: Z SHAPE TOTAL [message #442124 is a reply to message #440841] Thu, 04 February 2010 18:13 Go to previous message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
Great job by little foot.

As this question is raised in reports forum and the user is aslo new in this forum so far. So, i want to give user a thinking parameter that the same result can be achieved in oracle reports with using column summary etc ( and not using analytical function in query)


Good luck

-Dude
Previous Topic: Page items problem
Next Topic: Can we Run multiple reports by using 1 report server.
Goto Forum:
  


Current Time: Fri Dec 09 02:08:34 CST 2016

Total time taken to generate the page: 0.05199 seconds