Pentaho

 View Only

 PDI pentaho reporting output parameter issue

  • Pentaho
  • Kettle
  • Pentaho
  • Pentaho Data Integration PDI
Narasimha Rao Ch's profile image
Narasimha Rao Ch posted 09-18-2018 12:37

Hi,

  I m using pentaho Data integrator 8.1 and pentaho reporting output component inside a transformation. The report contains a simple integer parameter ${batchno} which is working fine with report designer. But while running the PDI transformation it is unable to substitute the parameter value in the report. Here is the detailed error message i am getting. My data source is Mysql - JDBC connection.

2018/09/18 17:49:21 - Generate Reports.0 - ERROR (version 8.1.0.0-365, build 8.1.0.0-365 from 2018-04-30 09.42.24 by buildguy) : Unexpected error

2018/09/18 17:49:21 - Generate Reports.0 - ERROR (version 8.1.0.0-365, build 8.1.0.0-365 from 2018-04-30 09.42.24 by buildguy) : org.pentaho.di.core.exception.KettleException:

2018/09/18 17:49:21 - Generate Reports.0 - There was an unexpected error processing report 'D:\.....\testreport.prpt' to produce file 'D:\...\testreport.pdf' with processor: PDF.

2018/09/18 17:49:21 - Generate Reports.0 - Failed at query: select id_batch,object_name from pdi_channel_log

where id_batch =  ${batchno}

2018/09/18 17:49:21 - Generate Reports.0 -

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.di.trans.steps.pentahoreporting.PentahoReportingOutput.processReport(PentahoReportingOutput.java:418)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.di.trans.steps.pentahoreporting.PentahoReportingOutput.processRow(PentahoReportingOutput.java:143)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)

2018/09/18 17:49:21 - Generate Reports.0 - at java.lang.Thread.run(Thread.java:748)

2018/09/18 17:49:21 - Generate Reports.0 - Caused by: org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: select id_batch,object_name from pdi_channel_log

where id_batch =  ${batchno}

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:184)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:142)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStaticInternal(CompoundDataFactory.java:172)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:154)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryInternal(CachingDataFactory.java:408)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryStatic(CachingDataFactory.java:151)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStaticInternal(CompoundDataFactory.java:168)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:154)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryData(CompoundDataFactory.java:67)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.states.datarow.DefaultFlowController.performQueryData(DefaultFlowController.java:256)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.states.datarow.DefaultFlowController.performQuery(DefaultFlowController.java:192)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.states.process.ProcessState.initializeForMasterReport(ProcessState.java:318)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.prepareReportProcessing(AbstractReportProcessor.java:442)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.processReport(AbstractReportProcessor.java:1433)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.di.trans.steps.pentahoreporting.ReportExportTask.execute(ReportExportTask.java:89)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.di.trans.steps.pentahoreporting.ReportExportTask.run(ReportExportTask.java:76)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.di.trans.steps.pentahoreporting.PentahoReportingOutput.processReport(PentahoReportingOutput.java:399)

2018/09/18 17:49:21 - Generate Reports.0 - ... 3 more

2018/09/18 17:49:21 - Generate Reports.0 - Caused by: java.sql.SQLException: Parameter metadata not available for the given statement

2018/09/18 17:49:21 - Generate Reports.0 - at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)

2018/09/18 17:49:21 - Generate Reports.0 - at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)

2018/09/18 17:49:21 - Generate Reports.0 - at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)

2018/09/18 17:49:21 - Generate Reports.0 - at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)

2018/09/18 17:49:21 - Generate Reports.0 - at com.mysql.jdbc.MysqlParameterMetadata.checkAvailable(MysqlParameterMetadata.java:70)

2018/09/18 17:49:21 - Generate Reports.0 - at com.mysql.jdbc.MysqlParameterMetadata.getParameterClassName(MysqlParameterMetadata.java:143)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrize(SimpleSQLReportDataFactory.java:356)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:277)

2018/09/18 17:49:21 - Generate Reports.0 - at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:179)

2018/09/18 17:49:21 - Generate Reports.0 - ... 19 more

2018/09/18 17:49:21 - Generate Reports.0 - child index = 1, logging object : org.pentaho.di.core.logging.LoggingObject@b66df16 parent=1cfddaa4-866d-4b08-9efb-c2a8842102e1

2018/09/18 17:49:21 - Generate Reports.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)


#PentahoDataIntegrationPDI
#Pentaho
#Kettle
Ricardo Miguel Díaz Razo's profile image
Ricardo Miguel Díaz Razo posted 09-20-2018 17:27

Plase can you attach your .ktr file (or make a print screen)?

I think you are using a variable in your TABLE INPUT, but you need to put a "?" for this.

Narasimha Rao Ch's profile image
Narasimha Rao Ch posted 09-25-2018 05:39

Hi,

Thanks for the reply. Here the screen shots of my transformation. I m using the generaterows step for the input details.

pastedimage_2

pastedimage_3

In the testreport i have a parameter with the name batchno

pastedimage_4

    In the report query the parameter is called as shown in the below screen shot

pastedimage_5

Dean Flinter's profile image
Dean Flinter posted 09-25-2018 08:01

I had this exact problem.

The PDI reporting output step will not work with PRD report parameters where the parameter is powered by a query. Remove this and it should work

If it does not then you need to set the type of the parameter to string and create another parameter with Value Type:Object and put this into the post processing formula section:

=CSVARRAY([batchno])

Also make sure that this new parameter is below the original one in the list of parameters, otherwise it will not work.

Then in your query you must reference this new parameter. For example if you called it batchno2 then your query should be

select id_batch, object_name from pdi_channel_log

where id_batch=${batchno2}

Narasimha Rao Ch's profile image
Narasimha Rao Ch posted 09-25-2018 10:30

Hi,

    Thanks for the reply. I tried both the ways, but no luck. still giving the same error for ${batchno2} . I see the sample given in PDI pentaho reporting output is working fine with numeric parameters. But the difference is the data source given in the sample is JNDI, in my case jdbc to mysql. Could it be an issue with jdbc mysql ?

Thanks,

Narasimha

Dean Flinter's profile image
Dean Flinter posted 09-25-2018 13:42

JNDI shouldn't be an issue

What I did is almost identical to yours.

In your Generate Rows step change the type from integer to string and try both methods again.

If that still doesn't work, the only thing I can think of is that I used a Data Grid step (all type string) instead of Generate Rows so perhaps that could be it?

Dean Flinter's profile image
Dean Flinter posted 09-25-2018 13:45

I would also turn off validate values in the PRD, just in case

Related Content