Solved

Dynamic filter when a datawindow is being previewed/printed in pb 7.

Posted on 2004-08-19
12
530 Views
Last Modified: 2013-12-26
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
Comment
Question by:Mateen
12 Comments
 
LVL 8

Expert Comment

by:Vikas_Dixit
ID: 11839863
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
 

Author Comment

by:Mateen
ID: 11840128
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
 
LVL 14

Accepted Solution

by:
sandeep_patel earned 500 total points
ID: 11840704
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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

by:Mateen
ID: 11849430
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
 

Author Comment

by:Mateen
ID: 11849812
I see! You said << Change where clause before retrieving record from database>>
                                                           --------------------
0
 

Author Comment

by:Mateen
ID: 11850369
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
 
LVL 18

Expert Comment

by:diasroshan
ID: 11850882
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
 

Author Comment

by:Mateen
ID: 11850976
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
 
LVL 14

Expert Comment

by:sandeep_patel
ID: 11851136
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
 

Author Comment

by:Mateen
ID: 11857702
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
 

Author Comment

by:Mateen
ID: 11857721
Hi Sandeep:

The problem found. It seems that in sqlpreview event after changing sqlsyntax one must write
 SETSQLPREVIEW(SQLSYNTAX).
0
 
LVL 14

Expert Comment

by:sandeep_patel
ID: 11857756
Oh Yeahhh!!!!!!!!!

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

have a nice time...

-sandeep
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to make AngularJS IntelliSense working in VS 2012 4 174
Object Data Source 4 126
HTML-Kit Question 5 99
Recommendation vb6 to vb.net or others 14 142
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…
Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

813 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

14 Experts available now in Live!

Get 1:1 Help Now