Cascading Parameter in Dynamic Listing

ReportServer (EN)
1

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

2

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

3

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

4

Good Morning Rijo,

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

Cheers,
Thorsten

5

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

6

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

7

Hi Arno,

Thanks for the help.
It’s working.

Thanks,
Rijo