ReportServer Forum

sritter

Member

Registered: 2020-07-07

JXLS - Dependent Queries

Hi,
I am trying to execute a query within an each-statement, using a parameter of from the data of the current loop.
Unfortunately nothing happens: no error, no data.

Should this work with the ReportServer jxls parser? (JXLS 2, JXLS Report)
Are there any hints on what needs to be done for this to work?

regards

Steffen

unsi_2

Member

Registered: 2020-06-24

Re: JXLS - Dependent Queries

do not include quotes within the query

eduardo

Administrator

Registered: 2016-11-01

Website

Re: JXLS - Dependent Queries

Hi sritter,

can you please post a minimal example of what you are trying to achieve?

Regards,
Eduardo

sritter

Member

Registered: 2020-07-07

Re: JXLS - Dependent Queries

I want to iterate over an entity like employees or locations and fill them in  row by row (works). For each of those created rows I want retrieve an additional result set (does not work): monthly performance (returning 12) which I want to add column by column. Or in JXLS speach an jx:each direction=right within the area of an jx:each direction=down. Therefore, the identifier of the first iteration would be a parameter to the second iteration.
But I cannot get the inner iteration to be executed even without parameter.

So basically the "dependent query" sample from v1 http://jxls.sourceforge.net/1.x/samples … ample.html in v2 syntax.

eduardo

Administrator

Registered: 2016-11-01

Website

Re: JXLS - Dependent Queries

Hi sritter,

does this work with JXLS2 (without reportserver) ? Do you have the corresponding JXLS2 syntax for the JXLS1 syntax you mention ?

Regards,
Eduardo

sritter

Member

Registered: 2020-07-07

Re: JXLS - Dependent Queries

does this work with JXLS2 (without reportserver)?

I don't know

Do you have the corresponding JXLS2 syntax for the JXLS1 syntax you mention ?

Yes, well the only difference between the normal grouping sample and the dependent query is, that the inner each assigns another jdbc.query to items, instead of _group.items.

eduardo

Administrator

Registered: 2016-11-01

Website

Re: JXLS - Dependent Queries

Hi sritter,

Dependent Queries are working in my testcase:

jx:area(lastCell="B3")
jx:each(items="jdbc.query('select OR_CUSTOMERNUMBER as customernr FROM T_AGG_ORDER')" var="order" lastCell="B3")
${order.customernr}
jx:each(items="jdbc.query('select CUS_CUSTOMERNAME name, CUS_PHONE phone FROM T_AGG_CUSTOMER where CUS_CUSTOMERNUMBER = ' +order.customernr )" var="customer" lastCell="B3")
${customer.name}

tested with the ReportServer Demo data.

Regards,
Eduardo

sritter

Member

Registered: 2020-07-07

Re: JXLS - Dependent Queries

OK, works, if I include the parameters with ' + + ' instead of ? and additional options to jdbc.query.

eduardo

Administrator

Registered: 2016-11-01

Website

Re: JXLS - Dependent Queries

OK, works, if I include the parameters with ' + + ' instead of ? and additional options to jdbc.query.

I didn't get this. What did you replace with + + ? and how does it work now in your case ?

Regards,
Eduardo

sritter

Member

Registered: 2020-07-07

Re: JXLS - Dependent Queries

"jdbc.query('select CUS_CUSTOMERNAME name, CUS_PHONE phone FROM T_AGG_CUSTOMER where CUS_CUSTOMERNUMBER = ' +order.customernr )"

instead of

"jdbc.query('select CUS_CUSTOMERNAME name, CUS_PHONE phone FROM T_AGG_CUSTOMER where CUS_CUSTOMERNUMBER = ?', order.customernr)"

eduardo

Administrator

Registered: 2016-11-01

Website

Re: JXLS - Dependent Queries

Hi sritter,

this seems not to work with the "?" syntax, this is correct. I raised RS-4284. I think this is not yet supported in JXLS2 or it is a bug. In the meanwhile, please use the "+" syntax.

Regards,
Eduardo