Modifying SQL in a Composite datawindow Report object

[girls3dog1]

I'd like to know if I can modify the SQL with(dw_name.modify('datawindow.table.select" = Select......"') for a report that is part of a composite datawindow object.

 

[tinac99]

I think you can do so by getting the nested datawindow using getchild function of the composite datawindow. Then, modify the SQL of the datawindowchild.

//declare datawindowchild
DataWindowChild dwc_statechild

//get datawindowchild
dw_1.GetChild('emp_state', dwc_statechild)

//* note: emp_state is the name of the nested dw.

//Then, change the SQL
dwc_statechild.modify('datawindow.table.select" = Select......&quot

 

[girls3dog1]

Than for the reply. I have tried this already with the GetchildDW method and then tried to modify Sql and it did not work. I have labored over this for many hours and have not been able to succeed. I've recently read something to the effect that report DW's cannot have the SQL modified at run time. I find this so limiting for my application. If you find workaround I'd be interested to hear the details.

 

[BetterLateThanNever]

Hi,

Maybe you could use a stored procedure to drive your child datastore and pass relevant arguments. Not that I've tried that, mind.

Cheers.

 

[girls3dog1]

I'm not sure how a child datastore would be implemented only because I have not used a datastore before. I'll read-up on it and see if this will work. The issue I'm trying to solve at runtime is being able to change the scope (SQL where clause) of the report based on any given user's criteria. It would be impossible to predict ahead of time the unique reporting scope a user might have up front and cast in concrete the where clause never to be changed again.

 

[BetterLateThanNever]

Hi,

Sorry, I meant child datawindow not datastore. So your datawindowchild dataobject possibly could be driven from a stored procedure, which could use a variety of methods to construct its own query sql (say from temporary tables populated with the users' choices) which would then return the data to your datawindowchild.

How much flexibility are you offering your users? I mean are they only able to choose, say, two figures to give a range or are you allowing them to select from many options?

Cheers.

 

[girls3dog1]

The range might be a single value or, as you noted, between two values.

 

[BetterLateThanNever]

Hi,

Couldn't you then have two arguments to the datawindow: al_high and al_low and have your SQL in the child datawindow as

SELECT * FROM x WHERE x.num >= al_low and x.num <= al_high

then programmatically decide how to populate the arguments i.e. if only one value is supplied, populate both arguments with the value otherwise populate them high and low respectively? You could supply the arguments to the parent datawindow as excess arguments, passing them directly to the report as columns, which the datawindow child could pickup. Possibly.

Cheers.

 

[girls3dog1]

I believe that would work. Now, what if I had a requirement for being able to modify the ORDER BY, at runtime?

 

[girls3dog1]

It works; finally! Thank-you for your help. The problem in the past appears to have been that after I modified the report DW SQL, I would perform a second retrieve of the whole composite DW in hopes of seeing the associated modified report DW. The data would never appear in the report. The following code performs a second retrieve, but this time it's a DataWindowChild retrieve and the data now appears. I'm not sure why the former does not work, but at this point I'll take it and move on. Here's the script:

**********************
Window Open event script

string ls_datawindow_object,ls_tag_sql_select

//Declare local structure & variables and extract it
s_pass_datasheet_preview lstr_datasheet_preview

//Get structure object passed from window w_datasheet_build
lstr_datasheet_preview = Message.PowerObjectParm

//Extract specification & datasheet no. from structure
is_specification = lstr_datasheet_preview.s_specification
il_datasheet = lstr_datasheet_preview.i_datasheet

//Modify sql for tags of composite dw
dw_datasheet_cmp.dataobject = ls_datawindow_object

//Retrieve so that report object dw_2 exists in dw to revise its sql
dw_datasheet_cmp.settransobject(sqlca)
dw_datasheet_cmp.retrieve(is_specification,il_datasheet)

//Declare varaible for datawindow child
Datawindowchild dw_child
dw_datasheet_cmp.GetChild('dw_2', dw_child)
ls_tag_sql_select = dw_child.describe
'DataWindow.Table.Select')

//ls_tag_sql_select = dw_datasheet_cmp.object.dw_2.object.DataWindow.Table.Select
integer li_pos1, li_pos2

//Find position of FROM
li_pos1 = pos(ls_tag_sql_select, "FROM &quot

//Find position of WHERE
li_pos2 = pos(ls_tag_sql_select, "WHERE &quot

//Revise sql with table name change
ls_tag_sql_select = mid(ls_tag_sql_select, 1, li_pos1 - 1) + " FROM tbl_instrument_list, " + is_tag_table + " tbl_ds " + mid(ls_tag_sql_select, li_pos2)

//Revise sql in report datawindow
dw_child.modify('DataWindow.Table.Select ="'+ ls_tag_sql_select +'"')

//Verify sql was revised in report datawindow - for debug only
ls_tag_sql_select = dw_child.describe('DataWindow.Table.Select')

//Retrieve report
dw_child.settransObject(sqlca)
dw_child.retrieve(is_specification, il_datasheet)