Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

Posted on 2004-08-19
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.


Question by:Mateen

Expert Comment

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


Author Comment

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.

LVL 14

Accepted Solution

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+"'")

and in printend event write down


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+"'")

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:


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


Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

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')"

the messagebox is showing the addition line AND (CONTRACT.EXECUTIVE_CODE='36') but no effect when report is retrieved. Am I missing something.


Author Comment

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

Author Comment

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')"
int 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 = 'L') and
                  ( fabric_type.fabric_type = contract_det.fabric_type) AND
                  ( rtrim(contract_mst.contract_no) = rtrim(:ra_contract_no))

the problem seems to be because of  retrieval  argument element. Please help.
LVL 18

Expert Comment

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

do ask if u need more assistance...


Author Comment

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.

LVL 14

Expert Comment

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

check whether it is the same as in sqlpreview event ?


Author Comment

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.

Author Comment

ID: 11857721
Hi Sandeep:

The problem found. It seems that in sqlpreview event after changing sqlsyntax one must write
LVL 14

Expert Comment

ID: 11857756
Oh Yeahhh!!!!!!!!!

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

have a nice time...


Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

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…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

828 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