Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Modify querytext of dataset called by inner table with data from outer table
Modify querytext of dataset called by inner table with data from outer table [message #742422] Thu, 20 October 2011 13:27 Go to next message
John Weber is currently offline 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 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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?
Re: Modify querytext of dataset called by inner table with data from outer table [message #742694 is a reply to message #742573] Thu, 20 October 2011 18:33 Go to previous messageGo to next message
John Weber is currently offline John Weber
Messages: 3
Registered: July 2009
Junior Member
I have added the parameter, param_1, and in the binding tab for the inner table, added the binding to the concatenation. In beforeOpen on the query, I have:
this.queryText  = reportContext.getPersistentGlobalVariable('baseSQL') + inputParams["param_1"].value;

I get the following error:
Table (id = 9847): 
- An exception occurred during processing. Please see the following message for details:
Cannot set the string value ( 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')) to parameter 1.
Cannot set preparedStatement parameter string value.
SQL error #1: Invalid column index
Re: Modify querytext of dataset called by inner table with data from outer table [message #743854 is a reply to message #742694] Fri, 21 October 2011 19:37 Go to previous message
John Weber is currently offline John Weber
Messages: 3
Registered: July 2009
Junior Member
I got this working. Two notes:

I needed to add a '?' in a dummy expression in the where clause in my baseSQL, or it would error out on having a parameter, but not a matching '?' in the SQL.

Also, it would error out when using inputParams["param_1"].value. I had to use inputParams["param_1"]

Thanks for the help!
Previous Topic:fonts in birt generated pdf reports.
Next Topic:Run/view reports/dashboards on tomcat staging server.
Goto Forum:
  


Current Time: Fri Oct 31 17:11:37 GMT 2014

Powered by FUDForum. Page generated in 0.03458 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software