Modify querytext of dataset called by inner table with data from outer table [message #742422] |
Thu, 20 October 2011 13:27 |
John Weber Messages: 3 Registered: July 2009 |
Junior Member |
|
|
I have a report with an outer table calling upon a dataset of filter data. I am grouping by filter name, and each filter may have several lines returning table, column, and values to match. I am using a concatenation aggregation to generate a sql fragment for a where clause like:
AND AE_P_PHS_E.CRAFT_CODE IN ('B-SIGNS') AND AE_P_PHS_E.SHOP IN ('B-PAINT SHOP') AND AE_P_PHS_E.STATUS_CODE IN ('20-W.F.M.') AND AE_P_PRO_E.CATEGORY IN ('B-INS','B-AR','B-SR','B-PM') AND AE_P_PRO_E.ORDER_TYPE IN ('B')
I am then calling an inner table to which I want to apply the filter. I have stored in a global variable the base SQL, which ends in WHERE 1=1. I have a script on the Dataset for the inner table on beforeOpen:
this.queryText = reportContext.getPersistentGlobalVariable('baseSQL') + row._outer["Concatenation"];
This fails, presumably, because row._outer works from the child table, but not from the dataset called by the child table.
Any thoughts on how to accomplish this?
|
|
|
Re: Modify querytext of dataset called by inner table with data from outer table [message #742573 is a reply to message #742422] |
Thu, 20 October 2011 16:05 |
|
Instead of using row._outer can create a dataset parameter for the inner
dataset and use the binding tab on the inner table to set the parameter
to the row._outer value. Then in the beforeOpen script you can
reference the dataset parameter like:
inputParams["mynewdatasetparameter"].value
Jason
On 10/20/2011 9:27 AM, John Weber wrote:
> I have a report with an outer table calling upon a dataset of filter
> data. I am grouping by filter name, and each filter may have several
> lines returning table, column, and values to match. I am using a
> concatenation aggregation to generate a sql fragment for a where clause
> like:
>
> AND AE_P_PHS_E.CRAFT_CODE IN ('B-SIGNS') AND AE_P_PHS_E.SHOP IN
> ('B-PAINT SHOP') AND AE_P_PHS_E.STATUS_CODE IN ('20-W.F.M.') AND
> AE_P_PRO_E.CATEGORY IN ('B-INS','B-AR','B-SR','B-PM') AND
> AE_P_PRO_E.ORDER_TYPE IN ('B')
>
> I am then calling an inner table to which I want to apply the filter. I
> have stored in a global variable the base SQL, which ends in WHERE 1=1.
> I have a script on the Dataset for the inner table on beforeOpen:
>
> this.queryText = reportContext.getPersistentGlobalVariable('baseSQL') +
> row._outer["Concatenation"];
>
> This fails, presumably, because row._outer works from the child table,
> but not from the dataset called by the child table.
>
> Any thoughts on how to accomplish this?
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.03561 seconds