Mateen
asked on
Dynamic filter when a datawindow is being previewed/printed in pb 7.
Can I make use of any global variable at the time of previewing / printing a report.
suppost a table namely contract has many columns including a column namely [executive_code]. A datawindow [rdw_contract ] has been made out of sql: "select * from contract_mst". The global variable gs_executive_code holds value '05'. Is it possible that when rdw_contract is being previewed / printed the report should be filtered to [executive_code=gs_executi ve code] meaning executive_code='05'.
The whole thing is that I have been asked that an executive (my system holds that executive in the global variable gs_executive_code) should be able to see his data only. This is one thing as I am afraid that there may arise situations that executive_code '05' is absent and executive code '04' has been given the rights to take contract reports of '04' himself and '05'.Then when rdw_contract is being previewed / printed the report should be filtered to [executive_code='04' or executive_code='05'.]
How to manage the whole thing dynamically.
suppost a table namely contract has many columns including a column namely [executive_code]. A datawindow [rdw_contract ] has been made out of sql: "select * from contract_mst". The global variable gs_executive_code holds value '05'. Is it possible that when rdw_contract is being previewed / printed the report should be filtered to [executive_code=gs_executi
The whole thing is that I have been asked that an executive (my system holds that executive in the global variable gs_executive_code) should be able to see his data only. This is one thing as I am afraid that there may arise situations that executive_code '05' is absent and executive code '04' has been given the rights to take contract reports of '04' himself and '05'.Then when rdw_contract is being previewed / printed the report should be filtered to [executive_code='04' or executive_code='05'.]
How to manage the whole thing dynamically.
ASKER
Hi vikas
<<I think it will be a better idea to provide a where clause instead.. so that the data that's not to be shown is not retrieved>>
Off-course it should be so. But how to do. Although I have some system in place to assign such rights but for the time being I want to ignore that system because that is very difficult and not pretty also. So I would like your design.
<<I think it will be a better idea to provide a where clause instead.. so that the data that's not to be shown is not retrieved>>
Off-course it should be so. But how to do. Although I have some system in place to assign such rights but for the time being I want to ignore that system because that is very difficult and not pretty also. So I would like your design.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi sandeep:
Thanks 4 your versatile suggestion.
I am trying in sqlpreview event
string ls_syntax
ls_syntax=sqlsyntax+" AND (CONTRACT.EXECUTIVE_CODE=' 36')"
sqlsyntax=ls_syntax
messagebox('',ls_syntax)
the messagebox is showing the addition line AND (CONTRACT.EXECUTIVE_CODE=' 36') but no effect when report is retrieved. Am I missing something.
Thanks 4 your versatile suggestion.
I am trying in sqlpreview event
string ls_syntax
ls_syntax=sqlsyntax+" AND (CONTRACT.EXECUTIVE_CODE='
sqlsyntax=ls_syntax
messagebox('',ls_syntax)
the messagebox is showing the addition line AND (CONTRACT.EXECUTIVE_CODE='
ASKER
I see! You said << Change where clause before retrieving record from database>>
--------------------
--------------------
ASKER
Hi Sandeep.
Before retrieve() I wrote
Dw_1.settransobject (sqlca)
string old_select, new_select
old_select = dw_1.GetSQLSelect()
new_select = old_select+" AND (CONTRACT_MST.EXECUTIVE_CO DE='36')"
mle_1.text=new_select
int retvalue
retvalue=dw_1.SetSQLSelect (new_selec t)
messagebox(string(retvalue ),new_sele ct)
retvalue is showing -1
where clause is showing this
WHERE ( contract_det.contract_no = contract_mst.contract_no ) and
( customer.cust_code = contract_mst.cust_code) and
( contract_det.quality_code = quality.quality_code) and
( colour.colour_code = contract_det.colour_code) and
( contract_mst.contract_type = 'L') and
( fabric_type.fabric_type = contract_det.fabric_type) AND
( rtrim(contract_mst.contrac t_no) = rtrim(:ra_contract_no))
AND (CONTRACT_MST.EXECUTIVE_CO DE='36')
the problem seems to be because of retrieval argument element. Please help.
Before retrieve() I wrote
Dw_1.settransobject (sqlca)
string old_select, new_select
old_select = dw_1.GetSQLSelect()
new_select = old_select+" AND (CONTRACT_MST.EXECUTIVE_CO
mle_1.text=new_select
int retvalue
retvalue=dw_1.SetSQLSelect
messagebox(string(retvalue
retvalue is showing -1
where clause is showing this
WHERE ( contract_det.contract_no = contract_mst.contract_no ) and
( customer.cust_code = contract_mst.cust_code) and
( contract_det.quality_code = quality.quality_code) and
( colour.colour_code = contract_det.colour_code) and
( contract_mst.contract_type
( fabric_type.fabric_type = contract_det.fabric_type) AND
( rtrim(contract_mst.contrac
AND (CONTRACT_MST.EXECUTIVE_CO
the problem seems to be because of retrieval argument element. Please help.
hi mateen,
Limitations to using SetSQLSelect :
Use SetSQLSelect only if the data source for the DataWindow object is a SQL SELECT statement 'without retrieval arguments' ...
so mateen now u know why SetSQLSelect returns -1....
i would have used Filter!!!
anyways... try and use...
dw_1.Modify("DataWindow.Ta ble.Select ='select.. .'")
do ask if u need more assistance...
Cheers,
Rosh
Limitations to using SetSQLSelect :
Use SetSQLSelect only if the data source for the DataWindow object is a SQL SELECT statement 'without retrieval arguments' ...
so mateen now u know why SetSQLSelect returns -1....
i would have used Filter!!!
anyways... try and use...
dw_1.Modify("DataWindow.Ta
do ask if u need more assistance...
Cheers,
Rosh
ASKER
Hi experts:
Because of Limitations to using SetSqlSelect as pointed out by diaroshan I had to accomodate in my existing design.
In my system every report datawindow is retrieved this way:
ll_row = dw_1.retrieve(aa_arg[1],aa _arg[2], aa_arg[3], aa_arg[4], aa_arg[5], &
aa_arg[6], aa_arg[7], aa_arg[8], aa_arg[9], aa_arg[10])
the initial value of entire array is null.
thus rdw_contract datawindow has two retrieval arguments ra_contract_no and ra_executive_code.The user is providing one argument ra_contract_no being kept in aa_arg[1] while aa_arg[2] is being filled at runtime by gs_executive_code.
This is ok.
Because of Limitations to using SetSqlSelect as pointed out by diaroshan I had to accomodate in my existing design.
In my system every report datawindow is retrieved this way:
ll_row = dw_1.retrieve(aa_arg[1],aa
aa_arg[6], aa_arg[7], aa_arg[8], aa_arg[9], aa_arg[10])
the initial value of entire array is null.
thus rdw_contract datawindow has two retrieval arguments ra_contract_no and ra_executive_code.The user is providing one argument ra_contract_no being kept in aa_arg[1] while aa_arg[2] is being filled at runtime by gs_executive_code.
This is ok.
hi mateen,
u are saying that u have already 'ra_executive_code' as a retrieval argument in your datawindow. so what's the problem now. It means that in your datawindow where clause u have defined that
contract.executive_code = :ra_executive_code
can u post your code where u r calling retrieve function for the datawindow ?
and u can change sqlsyntax that is being sent to the database from sqlpreview event. so if u want to change anything in your select or update or delete or insert statement before it executes u can change it from sqlpreview event. and here no need to use setsqlsyntax(). u can directly change the value or argument [sqlsyntax]
so << Change where clause before retrieving record from database>> means that here u want to change the where clause so u can use sqlpreview syntax and it must work. U r saying that in messagebox it is fine. can u check the sqlsyntax after report is retrieved ?
code in retrieveend event as below:
string ls_syntax
ls_syntax = dw_1.Object.datawindow.tab le.select
messagebox('syntax',ls_syn tax)
check whether it is the same as in sqlpreview event ?
-sandeep
u are saying that u have already 'ra_executive_code' as a retrieval argument in your datawindow. so what's the problem now. It means that in your datawindow where clause u have defined that
contract.executive_code = :ra_executive_code
can u post your code where u r calling retrieve function for the datawindow ?
and u can change sqlsyntax that is being sent to the database from sqlpreview event. so if u want to change anything in your select or update or delete or insert statement before it executes u can change it from sqlpreview event. and here no need to use setsqlsyntax(). u can directly change the value or argument [sqlsyntax]
so << Change where clause before retrieving record from database>> means that here u want to change the where clause so u can use sqlpreview syntax and it must work. U r saying that in messagebox it is fine. can u check the sqlsyntax after report is retrieved ?
code in retrieveend event as below:
string ls_syntax
ls_syntax = dw_1.Object.datawindow.tab
messagebox('syntax',ls_syn
check whether it is the same as in sqlpreview event ?
-sandeep
ASKER
Hi Sandeep
I wrote code in retrieveend as suggested by u. The syntax is not as it was in sqlpreview event. In fact there is no change in the original sql select.
I wrote code in retrieveend as suggested by u. The syntax is not as it was in sqlpreview event. In fact there is no change in the original sql select.
ASKER
Hi Sandeep:
The problem found. It seems that in sqlpreview event after changing sqlsyntax one must write
SETSQLPREVIEW(SQLSYNTAX).
The problem found. It seems that in sqlpreview event after changing sqlsyntax one must write
SETSQLPREVIEW(SQLSYNTAX).
Oh Yeahhh!!!!!!!!!
sorry i forgot to mention that...anyway so problem solved ? or still any confusion ?
have a nice time...
-sandeep
sorry i forgot to mention that...anyway so problem solved ? or still any confusion ?
have a nice time...
-sandeep
you can always filter out the dw before printing.. and use the global variable..
I think it will be a better idea to provide a where clause instead.. so that the data that's not to be shown is not retrieved..
can u clarify a bit more...
Regards,
Vikas