Eclipse Community Forums: BIRT » Modify querytext of dataset called by inner table with data from outer table

Help

Home

Home » Archived » BIRT » Modify querytext of dataset called by inner table with data from outer table

Show: Today's Messages :: Show Polls :: Message Navigator

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?

Report message to a moderator

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

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?

Report message to a moderator

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

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

Report message to a moderator

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

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!

Report message to a moderator

Previous Topic:	fonts in birt generated pdf reports.
Next Topic:	Run/view reports/dashboards on tomcat staging server.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 13:24:46 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter