How to create a procedure from birt tool?

Sai krishna
edited February 11, 2022 in Analytics #1

Hello,

Good morning!
Could anyone please tell me how to create a procedure using birt. Thanks in advance.

Warning No formatter is installed for the format ipb

Comments

  • Warning No formatter is installed for the format ipb
  • No Jeff, I would like to create a procedure in which i have to write insert query there.

    Warning No formatter is installed for the format ipb
  • BIRT is only for reporting. It does not have the ability to execute a SQL "insert" statement. You would need to create your own servlet:

    https://forums.opentext.com/forums/discussion/61115/birt-report-with-database-write-back

    Warning No formatter is installed for the format ipb
  • Thank you Jeff.

    I need small help.

    • If user ran a report by giving the values to the prompt.
    • Again when he ran the report again he have see the default values in the prompts previously entered by him.
      Could you please tell me how to make this thing happen. I am struggling to crack this issue since a week. :neutral:

    Thanks in advance.

    Warning No formatter is installed for the format ipb
  • @Sai Krishna said:
    Thank you Jeff.

    I need small help.

    • If user ran a report by giving the values to the prompt.
    • Again when he ran the report again he have see the default values in the prompts previously entered by him.
      Could you please tell me how to make this thing happen. I am struggling to crack this issue since a week. :neutral:

    Thanks in advance.

    Hello Jeff,

    Good morning!
    Could you please help me to crack this issue.
    Thanks in advance.

    Warning No formatter is installed for the format ipb
  • Hi Sai,

    BIRT does not have functionality to save data. It queries and displays data. So the first question to answer is how do you save the user-entered parameter values. If you can use the Information Hub server to host reports, it has built-in functionality to save user-entered parameter values and display them the next time the report is run. Without iHub, you need to come up with your own method of saving the values. Here is a previous discussion on the same subject that hopefully will be helpful:

    https://forums.opentext.com/forums/discussion/58646/saving-report-parameters-user-perferences

    Warning No formatter is installed for the format ipb
  • @jfranken said:
    Hi Sai,

    BIRT does not have functionality to save data. It queries and displays data. So the first question to answer is how do you save the user-entered parameter values. If you can use the Information Hub server to host reports, it has built-in functionality to save user-entered parameter values and display them the next time the report is run. Without iHub, you need to come up with your own method of saving the values. Here is a previous discussion on the same subject that hopefully will be helpful:

    https://forums.opentext.com/forums/discussion/58646/saving-report-parameters-user-perferences

    Hello Jeff,

    I would like to use the Information Hub server to host reports. As you said, it has the built-in functionality to save user-entered parameter values and display them them the next time the report run,
    Is there any way to find that built-in functionality access so then we can display the values in prompts.

    Warning No formatter is installed for the format ipb
  • Hi Sai,

    When you run the command highlighted below in Information Console, you will be prompted to enter the report parameters. After you enter the values and save them, a new report icon will be created. You might need to refresh the folder to see it. Whenever you run the report using the that icon, it will default all of the parameters to the values you saved.

    Warning No formatter is installed for the format ipb
  • @jfranken said:
    Hi Sai,

    When you run the command highlighted below in Information Console, you will be prompted to enter the report parameters. After you enter the values and save them, a new report icon will be created. You might need to refresh the folder to see it. Whenever you run the report using the that icon, it will default all of the parameters to the values you saved.

    Thanks for the reply. I have tried this by giving the default values to the prompts and ran the report I am able to see the values I have entered. I have also ran the report by modifying the prompt default value. But when I have re ran the report I am not getting the previous input values instead of that I am getting the values I have entered while creating the .rov file. Could you please tell me where I am doing mistake. Thanks in advance.

    Warning No formatter is installed for the format ipb
  • I just tested this. Every time I run the .rptdesign I get the default parameter values defined in the report. Each time I run the .rov I get the default parameter values specified in the "Create parameter value file" menu command. That is how it is designed to work. If you need different default values, you can make another .rov and optionally delete the first one.

    (Note: I will be away next week.)

    Warning No formatter is installed for the format ipb
  • Hello Jeff,

    I have created a table in DB with 2 columns for iHub server Username and another for prompt value.
    I have also create a procedure in pgAdmin DB which contains "Insert query" as the code.
    When I have call the procedure with hardcoded inputs, I unable to see those entered values in the table. Can please tell me why it was happening like that. Thanks in advance

    Warning No formatter is installed for the format ipb
  • Hi Sai,

    I need more info:

    When I have call the procedure with hardcoded inputs, I unable to see those entered values in the table.

    How are you making the call? From iHub? From pgAdmin?

    What is the syntax of the call?

    Do any messages display when you run it?

    What messages do you see in the Postgres logs?

    Warning No formatter is installed for the format ipb
  • jfranken
    edited July 19, 2019 #14

    Hi Sai,

    I noticed that you posted a question to Joel in the other forum related to this issue. I don't think Joel monitors these forums at the current time. In Joel's post that you quoted, he was simply explaining the basic behavior of the Parameter Value File in iHub. I'll go over the process in detail to make sure it is clear:

    Create a report with parameters in the BIRT Designer. Optionally set default values for the parameters in the Parameter editor while designing the report. Save the report and upload it to iHub.

    if you run the report in Information Console, the default parameter values specified in the Parameter editor when you designed the report will be used as the defaults in the Parameter window. As you know, if you change the parameter values and run the report, the next time you run the report your changes to the parameters will not be saved. The default values specified when the report was designed will be used again as the default values in the Parameter window.

    Alternatively, you can select the "Create Parameter Value File" menu command from the report's menu in Information Console. The Parameter window will display with the same default values that display when you run the report. However, if you change the parameter values in this Parameter window, the values will be saved. A new file will be created in Information Console. The file Type will be "Report Parameter Values".

    If you run the original report document again after creating the new file, its Parameter window will still use the default values specified when the report was designed, not the values you changed and saved. (This is a part of the process that you might be missing.) To use the newly changed parameter default values, you need to run the newly created file of type "Report Parameter Values". The Parameter Value File not only stores the modified parameter values, you also run it to run the report. Think of it as a report launcher that contains the modified parameter values.

    Create a new Parameter Value File each time you want to change the default parameter values and then be able to use those values again in the future.

    This functionality should work as I described on the standard iHub install. You should not need to add stored procedures, etc.

    I hope this helps.

    Warning No formatter is installed for the format ipb
  • @jfranken said:
    Hi Sai,

    I need more info:

    When I have call the procedure with hardcoded inputs, I unable to see those entered values in the table.

    How are you making the call? From iHub? From pgAdmin?

    What is the syntax of the call?

    Do any messages display when you run it?

    What messages do you see in the Postgres logs?

    Hello Jeff,

    Thanks for the reply. At present I am making call from pgAdmin. I have created a procedure in pgAdmin and I am calling that function from Birt report.
    Syntax of that call: call function_name(param_value1, param_value2)
    No I didnt seen any message displayed in the report.
    I have no idea where to find the postgres Logs. Can you please tell me where to find them.

    Thanks in advance.

    Warning No formatter is installed for the format ipb
  • @jfranken said:
    Hi Sai,

    I noticed that you posted a question to Joel in the other forum related to this issue. I don't think Joel monitors these forums at the current time. In Joel's post that you quoted, he was simply explaining the basic behavior of the Parameter Value File in iHub. I'll go over the process in detail to make sure it is clear:

    Create a report with parameters in the BIRT Designer. Optionally set default values for the parameters in the Parameter editor while designing the report. Save the report and upload it to iHub.

    if you run the report in Information Console, the default parameter values specified in the Parameter editor when you designed the report will be used as the defaults in the Parameter window. As you know, if you change the parameter values and run the report, the next time you run the report your changes to the parameters will not be saved. The default values specified when the report was designed will be used again as the default values in the Parameter window.

    Alternatively, you can select the "Create Parameter Value File" menu command from the report's menu in Information Console. The Parameter window will display with the same default values that display when you run the report. However, if you change the parameter values in this Parameter window, the values will be saved. A new file will be created in Information Console. The file Type will be "Report Parameter Values".

    If you run the original report document again after creating the new file, its Parameter window will still use the default values specified when the report was designed, not the values you changed and saved. (This is a part of the process that you might be missing.) To use the newly changed parameter default values, you need to run the newly created file of type "Report Parameter Values". The Parameter Value File not only stores the modified parameter values, you also run it to run the report. Think of it as a report launcher that contains the modified parameter values.

    Create a new Parameter Value File each time you want to change the default parameter values and then be able to use those values again in the future.

    This functionality should work as I described on the standard iHub install. You should not need to add stored procedures, etc.

    I hope this helps.

    Hello Jeff,

    Good morning!
    What you explained was so good and clear as well. But I have a small doubt regarding this. In the above explanation you have mentioned - "Create a new Parameter Value File each time you want to change the default parameter values and then be able to use those values again in the future." - actually I didnt get this point because if we create more than one file the user may get confused to which report he has to run and if I ran the ROV file instead of birt design file, I can get the modified data, but is there any possibility to modify those default values in parameters automatically by considering the Username of IHub server.

    Warning No formatter is installed for the format ipb
  • Hi Sai,

    If you want the user to enter default parameter values and save them in iHub, the only built-in option is the one I described. The solution requires that the user run the Parameter Value file, not the original report.

    The interface provides options for saving the Parameter Value file with a custom name, choosing a folder in which to save the file, and overwriting the existing file or creating multiple file versions. The iHub server also has functionality to set permissions on files and folders. So, for example, users could each have a folder only they can access that contains icons to launch reports with specific sets of default parameters.

    The user could be instructed to provide a descriptive name that includes the report name and indicates the parameters being run, i.e. Quarterly Sales Report for Product XYZ, where 'XYZ' is the default parameter value.

    Instead of using a Parameter Value file, you could write a custom Java servlet to save and retrieve the default parameter values. Are you a Java programmer or do you have access to such a resource? OpenText has a Professional Services team that develops custom solutions for customers.

    Warning No formatter is installed for the format ipb
  • @jfranken said:
    Hi Sai,

    If you want the user to enter default parameter values and save them in iHub, the only built-in option is the one I described. The solution requires that the user run the Parameter Value file, not the original report.

    The interface provides options for saving the Parameter Value file with a custom name, choosing a folder in which to save the file, and overwriting the existing file or creating multiple file versions. The iHub server also has functionality to set permissions on files and folders. So, for example, users could each have a folder only they can access that contains icons to launch reports with specific sets of default parameters.

    The user could be instructed to provide a descriptive name that includes the report name and indicates the parameters being run, i.e. Quarterly Sales Report for Product XYZ, where 'XYZ' is the default parameter value.

    Instead of using a Parameter Value file, you could write a custom Java servlet to save and retrieve the default parameter values. Are you a Java programmer or do you have access to such a resource? OpenText has a Professional Services team that develops custom solutions for customers.

    Hello Jeff,

    Could you please help me how to request Professional Services team regarding this issue.

    Warning No formatter is installed for the format ipb
  • Hi Sai,

    I sent an email to the Professional Services team with your name, the email address in your forum profile, and a link to this thread. I also recommend that you contact your sales representative. If you don't have your sales representative's contact information, you can go to the OpenText support home page by clicking the "OpenText" logo at the very top of this page. In the upper right corner of that page is a link to "Live Chat". It allows customers to instantly start a text chat with a Customer Service Representative. They will be able to provide whatever contact information you need for Sales and Professional Services.

    Warning No formatter is installed for the format ipb
  • @jfranken said:
    Hi Sai,

    If you want the user to enter default parameter values and save them in iHub, the only built-in option is the one I described. The solution requires that the user run the Parameter Value file, not the original report.

    The interface provides options for saving the Parameter Value file with a custom name, choosing a folder in which to save the file, and overwriting the existing file or creating multiple file versions. The iHub server also has functionality to set permissions on files and folders. So, for example, users could each have a folder only they can access that contains icons to launch reports with specific sets of default parameters.

    The user could be instructed to provide a descriptive name that includes the report name and indicates the parameters being run, i.e. Quarterly Sales Report for Product XYZ, where 'XYZ' is the default parameter value.

    Instead of using a Parameter Value file, you could write a custom Java servlet to save and retrieve the default parameter values. Are you a Java programmer or do you have access to such a resource? OpenText has a Professional Services team that develops custom solutions for customers.

    Hi Jeff,

    • I have created a function in postgres AdminIII. Which contains the - Insert query to the Table 'Insert1'. While I am
      running that function it was working good(inserting the given values into Table).

    • I have created a Dataset which takes query from procedure. And I have given that same query in that dataset.

    • But when I am calling that function from the report it was just displaying the return value but not inserting the given
      values into the DB table.

    Can you please tell me, is there any step I am missing there.

    And also please tell me how to run the procedure/function from the report.

    Thanks in advance.

    Warning No formatter is installed for the format ipb
  • Hello Jeff,

    I have also tried this in my opensource tool. When I run the report with the procedure(contains Insert query) the given values in the parameters are getting added to the database.
    When tried the same in my commercial tool. It was not happening, the values given by calling the function are not adding in the database. It means the logic was working in opersource tool but not in Commercial tool. Can you please tell me is there any other way to make these values added into Database using commercial tool.

    Thanks in advance.

    Warning No formatter is installed for the format ipb
  • jfranken
    edited July 30, 2019 #22

    Hi Sai,

    I was able to insert data into the database running from the commercial Designer (tested in 16.4, build v20180424) using a stored procedure. My database for the test was MySQL. The report throws an error but still runs and does the insert into the database table successfully. As I mentioned before, modifying the database from the Designer is not supported, so be aware that the error and potentially other issues can occur when using this method.

    Here are the details:

    Database table:  'customer'.  
    Table column:  'customerid'.  
    Stored procedure: 'p2'
    Schema: 'test2'.  
    
    Procedure:
    
    USE `test2`;
    DROP procedure IF EXISTS `p2`;
    DELIMITER $$
    USE `test2`$$
    CREATE PROCEDURE `p2` (In id1 INT, OUT myc int)
    BEGIN
    insert into test2.customer values (id1);
    SELECT test2.customer.customerid INTO myc from test2.customer where test2.customer.customerid=id1;
    END$$
    DELIMITER ;
    

    The report contains a stored procedure Data Set. The type is specified in the rarely used 'Data Set Type' dropdown when creating the Data Set.

    Data Set query:   {call test2.p2(?,?)}
    

    There are two Data Set parameters. The first parameter is an input parameter linked to a Report parameter for entering the value to be inserted into the database. The second is a parameter named 'myc' used to get the value back from the procedure and display it on the report. The 'Direction' property in the second parameter is set to 'Output'.

    To display the value returned from the procedure on the report, add a grid and set the grid binding to the Data Set. No columns will display in the binding list. Insert a Data element in the grid. The Data element expression builder will include the output parameter that was specified in the Data Set. Select it. The column will automatically be added to the grid binding when the Data element is saved. If the procedure returns a select statement instead of an output parameter, use a table instead of a single Data element to display the data.

    The report throws an error (Cannot get ODA driver parameter metadata.) when it is run, but it completes and the data displays. The data also gets inserted correctly into the database (run a simple select statement to test). Another potential cause of errors is if there is an attempt to insert a primary key twice in the table. Note that in addition to running the report, the query will run and the procedure will be called when the Data Set Preview is selected, and it can run at other times as well, like when the Data Set is saved.

    Warning No formatter is installed for the format ipb
  • Thats really works. I am using postgres Admin III database. Here I am unable to create Procedure only the Functions. Can you please give me any example using Postgres or by using Functions.
    One thing to tell you that. In opensource, I am using the same PGAdmin III, while calling function the DB was updating.
    But in Commercial tool, it was not.

    Can you please give me any example using Postgres or by using Functions.

    Warning No formatter is installed for the format ipb
  • Hi Sai,

    I don't have time to test with Postgres at the moment. Here is a link I found that shows how to create stored procedures in Postgres:

    postgresqltutorial.com/postgresql-create-procedure/

    Warning No formatter is installed for the format ipb