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



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.
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

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 =

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
edit html 3 123
Display current date,month,and year to JTextField 3 41
Doxygen plugin for Android Studio 1 470
debug as  junit test 4 65
Here is a helpful source code for C++ Builder programmers that allows you to manage and manipulate HTML content from C++ code, while also handling HTML events like onclick, onmouseover, ... Some objects defined and used in this source include: …
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from ( Go to that link and select download selenium in the right hand columnThat will then direct you to their downlo…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

746 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

12 Experts available now in Live!

Get 1:1 Help Now