• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

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_executive 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.

 

0
Mateen
Asked:
Mateen
1 Solution
 
Vikas_DixitCommented:
do you already have system in place to assign such rights ? or this also is to be designed?
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
0
 
MateenAuthor Commented:
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.



0
 
sandeep_patelCommented:
Hi mateen,
following are the different ways

1) Use Filter

case A : If you don't want to display records of executive_code = '05 in preview but don't want to print then write down following code in printstart event  of datawindow [rdw_contract]

This.SetFilter("executive_code='"+gs_executive code+"'")
This.Filter()

and in printend event write down

This.SetFilter('')
This.Filter()

case B: If you don't want to display records of executive_code = '05' in preview also then write down following code in retrieveend event of datawindow [rdw_contract]

This.SetFilter("executive_code='"+gs_executive code+"'")
This.Filter()

2) Change where clause before retrieving record from database

as u told your select statement is select * from contact_mst. So right down following code in sqlpreview event of datawindow [rdw_contract]

String ls_syntax

ls_syntax = sqlsyntax + " Where executive_code = '" + gs_executive_code + "'"

Note : here sqlsyntax is the argument in sqlpreview event. sqlpreview event triggers when u calls retrieve() or update() function for that datawindow.

3) Define retrieval argument in report datawindow ( if possible and permitted ) and call function:

rdw_contract.Retrieve(gs_executive_code)

I think these are the ways that u can use according to your conviniency...or if i m wrong and couldn't understand your problem properly...let me know...

regards
-sandeep

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MateenAuthor Commented:
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.

0
 
MateenAuthor Commented:
I see! You said << Change where clause before retrieving record from database>>
                                                           --------------------
0
 
MateenAuthor Commented:
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_CODE='36')"
mle_1.text=new_select
int retvalue
retvalue=dw_1.SetSQLSelect(new_select)
messagebox(string(retvalue),new_select)

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.contract_no) = rtrim(:ra_contract_no))
 AND (CONTRACT_MST.EXECUTIVE_CODE='36')

the problem seems to be because of  retrieval  argument element. Please help.
0
 
diasroshanCommented:
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.Table.Select='select...'")

do ask if u need more assistance...

Cheers,
Rosh
0
 
MateenAuthor Commented:
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.





0
 
sandeep_patelCommented:
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.table.select
messagebox('syntax',ls_syntax)

check whether it is the same as in sqlpreview event ?

-sandeep
0
 
MateenAuthor Commented:
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.
0
 
MateenAuthor Commented:
Hi Sandeep:

The problem found. It seems that in sqlpreview event after changing sqlsyntax one must write
 SETSQLPREVIEW(SQLSYNTAX).
0
 
sandeep_patelCommented:
Oh Yeahhh!!!!!!!!!

sorry i forgot to mention that...anyway so problem solved ? or still any confusion ?

have a nice time...

-sandeep
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now