Solved

To create computed column in composite datawindow against nested datawindow.

Posted on 2011-03-21
4
899 Views
Last Modified: 2013-12-26
pb10.5

I have a composite datawindow "dw_balance_sheet"

It has two nested datawindow "dw_asset_side" and "dw_liability_side"

dw_asset_side has computed column "compute_sum_asset" with expression "sum(amt for all)
dw_liability_side has computed column "compute_sum_liability" with expression "sum(amt for all)
in composite datawindow I want to create a computed column which will hold the difference amt of compute_sum_asset - compute_sum_liability

Is it possible?
0
Comment
Question by:Mehram
  • 2
  • 2
4 Comments
 
LVL 3

Expert Comment

by:ssisworo
ID: 35179882
The first is that you can not be made to compute column which can read from datawindowchild powerscript unaided, but there are other ways to obtain this information:

The first way:
with the help powerscript, namely reading the value of the second composite datawindow datawindowchild in it. then the value is 'setitem' in a column provided in the datawindow itself. I think this is rather cumbersome way, because it requires powerscript thus less practical.

The second way:
by creating a union query in the main datawindow. then a direct value added. I enclose the following example. in the example it is assumed there are 2 pieces of a table containing the asset and liability. hopefully help.

QUERY :
 query
DATAWINDOW :
 datawindow design
0
 

Author Comment

by:Mehram
ID: 35186852
Hi ssisworo:

The scond way is not affordable to me because the sql behind is complex and time consuming.
for example the sql of dw_liability_side is (pl. see code snippet)

Therefore I would like to go the second way but cant figure out how to write getitem syntax for nested rports.

datawindow control = dw_1
dataobject = dw_balance_sheet
what is the syntax to know value against the two nested dws in dw_balance_sheet

select
Company = (select top 1 dbo.f_decrypt(company_name) from company where company_code = :ra_company_code) 
,		note_no=z.note_no
	,	note_descr=z.note_descr
	,	note_heading=z.note_heading
	,	notehead=z.note
	,	amt=sum(z.amt)


from 
(
select 
	 x.note_no
	,note_descr=x.note_descr
	,note_heading=x.note_heading
	,note=x.note_headno
	,amt=sum(amt)+
	 sum(case when amt_debit  > 0 then amt_debit else 0 end)+
	 sum(case when amt_credit > 0 then amt_credit else 0 end)
from
(
SELECT	
	 note_no 	  = acc_range.note_no 
	,subnote_no	  = acc_range.sub_note_no
	,acc_code	  = vd.acc_code
	,note_heading = notem.note_heading
	,amt          = sum (case acc_range.get_amt_dcnr 
									  when 'R' then (vd.credit_rs - vd.debit_rs)
									  when 'N' then (vd.debit_rs - vd.credit_rs ) else 0    end)
	,amt_debit    = sum (case when acc_range.get_amt_dcnr ='D' then  (vd.debit_rs - vd.credit_rs ) else 0 end)
	,amt_credit   = sum (case when acc_range.get_amt_dcnr ='C' then (vd.credit_rs - vd.debit_rs) else 0 end)
	,note_descr    = notem.note_descr
	,note_headno  = notem.main_note_headno 
	
FROM gl_vchr_det vd
join gl_notes_acc_ranges acc_range  on ( vd.acc_code between acc_range.start_acc_code  and acc_range.end_acc_code) 
join gl_notes_mst notem              on (notem.note_no = acc_range.note_no ) 

where	( notem.note_no not like '0020')
  and ( vd.company_code = case when :ra_company_code = '00' then vd.company_code else :ra_company_code end)
  and	( notem.bl_pls_ind  = 'B' ) 
  and	( notem.acc_break=1) 
  and	( vd.trans_date <= :ra_date_to)
  and ( vd.vchr_code <> 	CASE when vd.trans_date = ( select dt2 from gl_calendar where vchr_name ='yearend' and ( :ra_date_TO  between dt1 and dt2))
  and  :ra_date_to = ( select dt2 from gl_calendar where vchr_name ='yearend' and ( :ra_date_TO  between dt1 and dt2)) then  'YEAREND'  else 'NOTHING' END  )

	
 group by 
 acc_range.note_no
,notem.main_note_headno 
,notem.note_descr
,vd.acc_code
,acc_range.sub_note_no
,notem.note_heading
)
x
group by
	x.note_no
,	x.note_descr
,	x.note_headno
,	x.note_heading
union all
select
  '0020'
,	'Unappropriated Profit'
,   'Paid Up Capital & Reserve'
,	'1'
,	amt = isnull(sum(y.amt_2 -y.amt_1 ),0)
+  isnull((select sum(vd.credit_rs-vd.debit_rs)	
from  gl_vchr_det 	vd
where ( left(vd.acc_code,7)  = any (select left(pnl_acc_code,7) from company where company_code = case when :ra_company_code = '00' then '01' else :ra_company_code end))
     and ( vd.company_code = case when :ra_company_code = '00' then vd.company_code else :ra_company_code end)
	  and (vd.year < (select top 1 year  from gl_calendar where dt2 = :ra_date_to) )),0)
+ isnull(( select sum(credit_rs - debit_rs)   /* to negate difference of profit */
    from gl_vchr_det vd
    where acc_code = '12110200051'    
    and trans_date <= :ra_date_to
    and ( company_code = :ra_company_code )
   ),0)

from
(
SELECT  
	 note_no = acc_range.note_no 
	,subnote_no	= acc_range.sub_note_no
	,note_headno = notem.main_note_headno 
	,note_heading=notem.note_heading
	,acc_code	= vd.acc_code
	,notem.note_descr
	,amt_1 = sum(case when (notem.note_no not in('0155','0190','0200')) then (vd.debit_rs - vd.credit_rs)  else 0 end)
	,amt_2 = sum(case when (notem.note_no  in('0155','0190','0200'))    then (vd.credit_rs - vd.debit_rs)  else 0 end)
FROM 	 gl_vchr_det vd
join  	gl_notes_acc_ranges acc_range   on ( vd.acc_code between acc_range.start_acc_code  and acc_range.end_acc_code) 
join   	gl_notes_mst notem               on ( notem.note_no = acc_range.note_no ) 
where	  ( vd.trans_date between :ra_date_from and :ra_date_to)
  and   ( notem.note_no between  '0150' and '0209' )
  and   ( vd.company_code = case when :ra_company_code = '00' then vd.company_code else :ra_company_code end)
  and   ( vd.vchr_code <> 	CASE when vd.trans_date = ( select dt2 from gl_calendar where vchr_name ='yearend'and ( :ra_date_TO  between dt1 and dt2))
  and  :ra_date_TO = ( select dt2 from gl_calendar where vchr_name ='yearend'
  and ( :ra_date_TO  between dt1 and dt2)) then  'YEAREND'  else 'NOTHING' END  )
group by 
 acc_range.note_no
,notem.main_note_headno 
,notem.note_descr
,vd.acc_code
,acc_range.sub_note_no
,notem.note_heading
)
y
)
z
group by 
 z.note_no
,z.note_descr
,z.note
,z.note_heading

Open in new window

0
 
LVL 3

Accepted Solution

by:
ssisworo earned 500 total points
ID: 35188719
if so let's try the first way.
1. create a composite datawindow with the object asin the picture in the form :
     a. dw_1 (composite asset)
     b. dw_2 (composite liability)
     c. t_diff (add a text object - to fill in the value of difference)
2.  datawindow pair are on the window that will be used
     can use the keypad to perform calculations, or
     can be attachedin the window open event , tailoredto the needs of
3. The following is a script to do the calculations and fill in values.
     In this case i attach in command button


Power Script :
 
integer li_asset_side, li_liability_side
datawindowchild ldwc_asset_side
datawindowchild ldwc_liability_side

// database connection
SQLCA.DBMS = "SYC Adaptive Server Enterprise"
SQLCA.Database = "FO"
SQLCA.LogPass = "master"
SQLCA.ServerName = "FSRVDB01"
SQLCA.LogId = "sa"
SQLCA.AutoCommit = True
SQLCA.DBParm = "Release='12.5'"
connect using SQLCA;

// set transaction
dw_1.settransobject(SQLCA)
dw_1.retrieve()

// get child datawindow asset
if dw_1.getchild('dw_1',ldwc_asset_side) < 0 then
	messagebox('error','error getchild dw_1')
end if

// get child datawindow liability
if dw_1.getchild('dw_2',ldwc_liability_side) < 0 then
	messagebox('error','error getchild dw_2')
end if

// get value
li_asset_side     = ldwc_asset_side.getitemdecimal(1,'compute_sum_asset')
li_liability_side = ldwc_liability_side.getitemdecimal(1,'compute_sum_liability')

// set different
dw_1.object.t_diff.text = string(li_asset_side -  li_liability_side)

Open in new window



sample asset dataobject
 sample asset dataobject

composite dataobject
 composite dataobject

Window and  the object
 Window and  the object

Run Time
 Run Time

The code
 The code
0
 

Author Closing Comment

by:Mehram
ID: 35188878
Great work!!!

Exactly what I wanted.

I am very much pleased.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deletiā€¦
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now