Hitachi Vantara Pentaho Community Forums
Results 1 to 14 of 14

Thread: Call procedure with report designer 3.6

  1. #1

    Default Call procedure with report designer 3.6

    Hi all,

    I had a problem with calling procedure to report designer 3.6.I had a procedure and a table but i couldn't access them from report designer.

    I don't know how and where i can write the format in report designer ?

    for example :
    procedure name : PEL.P_REPORT_TEST(${COUNTRY});
    table name : D.DM_LOCATION_REPORT_TEST
    parameter name : ${COUNTRY}

    thanks for all

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    As we are using JDBC for all database access, the common JDBC rules apply. Therefore stored procedures must be called with the standard syntax:

    Code:
    { call myStoredProc ( ${myParam} ) }
    Some drivers may accept additional (proprietary) ways to call stored procedures. Your database manual should give you more details on the exact syntax your database understands.

    In general, it is a good idea to state what database you are using. Right now, I would not be able to tell what vendor you use. SQL is treated more like a recommendation than a standard by the database vendors. So unless we know your DB-vendor we wont know the way they abused SQL to make the vendor lock-in happen. And thus we cannot guess the syntax of your SQL dialect either.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3

    Default

    Hi Taqua,

    We have a report and it has approximately 16 parameters.And we want to learn where we use this code ? (In the data tab like available queries ? or in the structure tab ? )

    example:

    select * from PEL.P_REPORT_TEST(${COUNTRY}); // this is not working //


    Our database is Oracle and our syntax like this :

    EXEC PEL.P_REPORT_TEST(${COUNTRY});

    thanks,
    Attached Files Attached Files

  4. #4

    Default

    Hi,

    I need an example and where do i write it for calling procedure ?

    Thanks,

  5. #5

    Default

    Does anyone know where we write the code,which calls procedure, in report designer 3.6 ?

    procedure name : p_test
    table name : t_test
    parameter name : country


    Is the syntax like that ? (i tried but it doesn't work)

    select *
    from {call p_test (${country})}

  6. #6
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    The JDBC standard (and probably your database documentation on the topic of JDBC) says that the syntax is

    {call procedure(parameter,parameter)}


    There is no "select *" on it, such stuff is vendor specific and if it works, you're lucky but if not then you are on your own.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  7. #7

    Thumbs up

    Hi all,

    First of all, i just want to share my thanks for Taqua for helping this issue.

    We solved the procedure problem like this :

    Step 1 : Create an oracle function that will return rows (PIPELINED function )

    CREATE OR REPLACE FUNCTION PDIETL.get_all_objects (
    p_owner IN VARCHAR2 DEFAULT '%',
    p_object_type IN VARCHAR2 DEFAULT '%',
    p_object_name IN VARCHAR2 DEFAULT '%',
    p_status IN VARCHAR2 DEFAULT '%')
    RETURN object_table_type PIPELINED AS
    BEGIN
    FOR cur IN (SELECT owner, object_type, object_name, status
    FROM all_objects
    WHERE owner LIKE p_owner
    AND object_type LIKE p_object_type
    AND object_name LIKE p_object_name
    AND status LIKE p_status
    ORDER BY owner, object_name, object_type)
    LOOP
    PIPE ROW(object_row_type(cur.owner, cur.object_type, cur.object_name, cur.status));
    END LOOP;
    RETURN;
    END;
    /

    Step 2 : We can write the query in query editor.

    SELECT *
    FROM TABLE(get_all_objects(${parameter1},${parameter2}))

    and it works ,

    Note : For step 1 , i used this link :

    http://it.toolbox.com/blogs/database...in-oracle-7802

    fyi,

  8. #8
    Join Date
    Mar 2011
    Posts
    8

    Default

    Name:  Stored Procedure.JPG
Views: 199
Size:  11.2 KBName:  Pentaho Report Designer Procedure Call.jpg
Views: 220
Size:  24.1 KB

  9. #9
    Join Date
    Mar 2011
    Posts
    8

    Default

    Hi Friends,

    Here is the screen shot of the stored procedure and how it has been called through the Pentaho Report Designer.
    Hope this would help.

    I suggest you to play around with all the curly braces and parenthesis and click on preview however we have provided the parameter so it won't show anything but if you are able to see the column names then you query should be correct otherwise it will show errors in a pop up window.

  10. #10
    Join Date
    Mar 2011
    Posts
    8

    Default

    Hi Friends,

    Here is the screen shot of the stored procedure and how it has been called through the Pentaho Report Designer.

    Hope this would help.


    I suggest you to play around with all the curly braces and parenthesis and click on preview however we have provided the parameter so it won't show anything but if you are able to see the column names then you query should be correct otherwise it will show errors in a pop up window.
    Attached Images Attached Images   

  11. #11
    Join Date
    Mar 2011
    Posts
    8

    Default

    I believe the procedure call is not clear in the image so here is the more clear version in text format.

    {call proc_geoDetails1(${OperatingSystem})}

  12. #12
    Join Date
    Feb 2012
    Posts
    4

    Default

    Hi miscvinod,How to get result from the oracle procedure?
    The example is not the oracle procedure, isn't it? In oracle, we cant only use the select.Could you give an oracle example?
    Thanks!
    Frank

  13. #13
    Join Date
    Mar 2011
    Posts
    8

    Default

    Sorry Man... It seems like I am 7 months late here... Actually was quite away with the tech stuff.

    Anyways here is the answer.
    Just call the procedure with following syntex.
    Syntex
    ----------
    {call <procedure name>(${<parameters that will be evaluated against select where clause>})}

    Example
    ----------
    {call proc_geoDetails1(${OperatingSystem})}

    Note :- Ignore < and > signs they are there just for the distinction.

  14. #14
    Join Date
    Feb 2013
    Posts
    530

    Default How to display multiple result sets of stored procedure

    Hi folks,

    It's well worked nice job.

    Now if we want to display multiple result sets in Pentaho report designer, how it is possible ?

    Suppose my stored procedure gives 3 different result sets .
    1st result set is having 2 rows,2 columns
    2nd result set is having 10 rows,5 columns
    3rd result set is having 3 rows, 8 columns

    First I need to display these 3 result sets of data as output and I wanna use 2nd result set for reporting.

    How it is possible in PRD ?

    Thanks in Advance.
    SADAKAR P

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.