ReportServer Forum

rijopius

Member

Registered: 2013-09-30

Cascading Parameter in Dynamic Listing

Hi,

I am trying to use cascading parameters in Dynamic Listing Report.
I have made paramter2 key dependent on parameter1 key while adding keys.
Still parameter2 SQL is fetching result without filtering as per parameter1.

Please help.

Best Regards,
Rijo

Thorsten J. Krause

Guest

Re: Cascading Parameter in Dynamic Listing

Hi,

how did you reference parameter1 in the query of parameter2? Just marking the parameters as dependent does not automatically imply any filtering.

Usually you would write your parameter2 query as something to the extend of:

Select key_col, val_col from mytable where some_col = $P{param1}

Cheers,
Thorsten

rijopius

Member

Registered: 2013-09-30

Re: Cascading Parameter in Dynamic Listing

Hi Thorsten,

Tried this with a single parameter.

key   name
===  =====
key1 param1

select param1 from param_table---> This is my parameter.
Now in my report query I added this paramater as below:

select * from transact_tbl where column1=$P{param1}

Gets the error saying Parameter 'param1' doesn't exists.

Please help.

Thanks,
Rijo

Thorsten J. Krause

Guest

Re: Cascading Parameter in Dynamic Listing

Good Morning Rijo,

in the query, the key is used to identify the parameter. The name is only displayed to the user.

Cheers,
Thorsten

rijopius

Member

Registered: 2013-09-30

Re: Cascading Parameter in Dynamic Listing

Hi

Thanks for replying.
Still I am facing some error.

This is what I am using the report query.

where column1 in (${key1})

Query could not be prepared: Error preparing statement for executing the report query :

net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.createStatement(JasperStyleParameterParser.java:222) <br> at net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.getStatement(JasperStyleParameterParser.java:121) <br> at net.datenwerke.rs.base.service.dbhelper.querybuilder.ManagedQuery.prepareStatement(ManagedQuery.java:155) <br> ... 54 more <br>Caused by: java.sql.SQLException: Invalid column type <br> at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113) <br> at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:147) <br> at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:209) <br> at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9308) <br> at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8889) <br> at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9611) <br> at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9594) <br> at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setObject(NewProxyPreparedStatement.java:901) <br> at net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.setStatementParameter(JasperStyleParameterParser.java:439) <br> at net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.setStatementParameter(JasperStyleParameterParser.java:239) <br> at net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.createStatement(JasperStyleParameterParser.java:214) <br> ... 56 more <br>

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: Cascading Parameter in Dynamic Listing

Hi Rijo,

the syntax for using parameters together with IN clauses is a bit different. Assuming that key1 corresponds to a list parameter the correct syntax would be

SELECT * FROM XX WHERE $X{IN, column1, key1}  

I hope this helps.

Arno

rijopius

Member

Registered: 2013-09-30

Re: Cascading Parameter in Dynamic Listing

Hi Arno,

Thanks for the help.
It's working.

Thanks,
Rijo