ReportServer Forum

iamcarolek

Member

Registered: 2023-06-09

Parameter File Upload

Hi can you advise what the parameter file upload allows? Will an XLSX with data in a column be read, or a csv or txt file in a column or only as a CSV with commas separating?

I can upload a file successfully, but I get SQLException: Invalid column type when the parameter is added as part of the report query.  Without the parameter in the report query, the report works great.

The file I upload is numbers (right now just 1 number).  I have tried both txt and csv extensions.  The data it is mapping to is numeric (varchar2 cast to numeric(10,0)) and shows as numeric in the column list. 

The invalid column type is coming from the upload....I have just the number 1 in the file. I have tried without a return after the number and no return.  The field is "skunbr".  From below, it seems to get the WHERE correct - a1.skunbr IN (?)
But it doesn't seem to be able to replace the ? with the values in the upload file.  Help please!

Error preparing statement for executing the report query : <br><br>SELECT * FROM (SELECT * FROM (SELECT<br>    a1.dept_cd,<br>    a1.dept,<br>    a1.subdept_cd,<br>    a1.subdept,<br>    a1.skunbr as SKU,<br>    a1.product_name,<br>    a1.last_sold,<br>    SUM(a1.sales_qty)    AS sales_qty,<br>    SUM(a1.sale)         AS sales,<br>    SUM(a1.gross_margin) AS gross_margin,<br>    a2.oh_qty<br>FROM<br>    v_bd_sales a1<br>    LEFT JOIN v_bd_boh   a2 ON a1.pid = a2.pid<br><br>WHERE  a1.skunbr IN (?) and<br>    a2.boh_date = (<br>        SELECT<br>            MAX(a2.boh_date)<br>        FROM<br>            v_bd_boh a2<br>    ) <br>GROUP BY<br>    a1.dept_cd,<br>    a1.dept,<br>    a1.subdept_cd,<br>    a1.subdept,<br>    a1.skunbr,<br>    a1.product_name,<br>    a1.last_sold,<br>    a2.oh_qty<br><br>) wrappedQry) limitQry WHERE ROWNUM &lt; 0   /* user: 6 */   /* report: 222287 */   /* token: 222287-6-1688453491817--1869608054:0.06527285344775158 */   /* currentuser: 6 */ <br><br><br>    at net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.createStatement(JasperStyleParameterParser.java:308)<br>    at net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.getStatement(JasperStyleParameterParser.java:219)<br>    at net.datenwerke.rs.base.service.dbhelper.querybuilder.ManagedQuery.prepareStatement(ManagedQuery.java:89)<br>    ... 60 more<br>Caused by: java.sql.SQLException: Invalid column type<br>    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8079)<br>    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:7581)<br>    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8332)<br>    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8307)<br>    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:219)<br>    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setObject(NewProxyPreparedStatement.java:821)<br>    at net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.setStatementParameter(JasperStyleParameterParser.java:537)<br>    at net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.setStatementMultiParameter(JasperStyleParameterParser.java:444)<br>    at net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.setStatementMultiParameters(JasperStyleParameterParser.java:421)<br>    at net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.createStatement(JasperStyleParameterParser.java:300)<br>    ... 62 more<br>

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Parameter File Upload

Hi iamcarolek,

https://reportserver.net/en/guides/admi … selection/

The file selection parameter provides users with the means to select or upload one or more files that can then go into the report generation process. This is especially useful, when working with custom script reports or export targets that can make use of such additional information.

so this is useful for script reports, etc, not for normal reports. It is not meant to be used as a normal parameter as you are trying.

Regards,
Eduardo

iamcarolek

Member

Registered: 2023-06-09

Re: Parameter File Upload

Thank you for the reply.  We are on the Community Edition.  On the website for the edition comparison, the file parameter is shown as part of Community, but scripting is only part of Enterprise.  So in short, I have the file upload parameter, but can't use it under Community, lol.  I tried text parameter, but I will separately ask about that.  Thank you again for your help.  (We do plan on upgrading to Enterprise after January, but we have some months to go still