Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: How to call functions or procedures in report designer

  1. #1

    Default How to call functions or procedures in report designer

    Hello,

    I'm using Report Designer and I dont know how to call a function or procedure. Some days ago I used Eclipse to make reports and for calling a function I used to do

    {?=call MONITOR_REPORT.PKG_GRAFICA_BUILD.FUNC_GRAFICA_METRICA('GESTION','PROTOTIPO','METRICAS','METRICAS DE BBDD','GENERAL','DB TIME (S)','HORA',to_date('2011-01-09 00:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2011-01-10 23:59:59','yyyy-mm-dd hh24:mi:ss'),?,?,null,?,?,'1234567')}

    to call the function FUNC_GRAFICA_METRICA, but now I have to do it with Report Designer and I cant find a way to do it.

    If someone knows how to do it, I'd be very thankful.

    Regards,

    Jesi

  2. #2

    Default

    Hi jficheux,

    We fix the solution like this :

    Step 1 :

    Create a pipeline function in database

    Step 2 :

    Call this procedure in pentaho report designer :

    SELECT
    *
    FROM TABLE(FUNCTION_TEST_TABLE(
    ${PARAMETER1},
    ${PARAMETER2},
    ${PARAMETER3}
    ))

    i hope it works, and the link for pipeline fucntion is :

    http://it.toolbox.com/blogs/database-solutions/returning-rows-through-a-table-function-in-oracle-7802
    Last edited by engineer; 03-30-2011 at 05:04 AM.

  3. #3

    Default

    Hi, I do the same as you say (you can see it in the bold font), but I get the next error:

    org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: SELECT * FROM TABLE(myDemoPack.prodFunc('GESTION','PROTOTIPO','METRICAS','METRICAS DE BBDD','GENERAL','DB TIME (S)',${AGRUPACION1},to_date('2011-01-09 00:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2011-01-10 23:59:59','yyyy-mm-dd hh24:mi:ss'),${BBDD1},null,null,8,9,'1234567'))
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:236)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:95)
    at org.pentaho.reporting.ui.datasources.jdbc.ui.JdbcPreviewWorker.run(JdbcPreviewWorker.java:102)
    at java.lang.Thread.run(Thread.java:662)
    ParentException:
    java.sql.SQLException: Tipo de columna no válido (it means column type invalid)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
    at oracle.jdbc.driver.OracleStatement.get_internal_type(OracleStatement.java:6164)
    at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:1316)
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2746)
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:3015)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:322)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:232)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:95)
    at org.pentaho.reporting.ui.datasources.jdbc.ui.JdbcPreviewWorker.run(JdbcPreviewWorker.java:102)
    at java.lang.Thread.run(Thread.java:662)

    Do you know what's going on??
    Thank you very much. Regards,

    JESI

  4. #4

    Default

    Hi,

    I don't know the error exactly (it might be the date format), but i want to inform you about a point :

    When i checked my query ( preview ), it gives an error.According to me ,its about parameter validation.
    But when i run the report ignoring this error, it works.

    I hope it works,

    PS : you will try to write null values also in ' ' : ('null')

  5. #5

    Default

    Hi,
    Thank you for your answer, but even if it would give me and error I cant show the result because in the report designer, when you add a query in the right panel, something like that:

    Query
    Procedure
    |
    |-> result

    Is the "result" what I throw to the report to see the value in the preview, but I dont get any result, are you able to help me?

    Thanks a lot.
    Regards,

    JESI

  6. #6

    Default

    Hello to everybody!!

    I'm still having problems with this issue, can anyone help me please?
    I'm getting mad with it, because I need to do it and I have to use a procedure or a function. I have no problems with queries, but with procedures I do.
    Because I had problems with the function, I tried to write a procedure but I still get the same error, "invalid column type". And I not able to show the resultset of my procedure.
    Please if someone knows how to do it, I would apreciate some help.

    Thanks in advance,

    Jesi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.