remote query, result in selectList

(11 posts) (2 voices)
  • Started 4 months ago by mfiorentino
  • Latest reply from mfiorentino

Tags:

No tags yet.

  1. mfiorentino, Member

    Hello,
    what is the best way to call a remote query and show the result in a selectList?
    Example:
    in database A I want to show as a filter of a report a selectList with the names of Sellers fetched from database B. I have created a report on mydbr of server A and it works, it shows me the names of the sellers. I can not understand how I can connect it to a selectList to use as a filter for a report

    thank you

    Posted 4 months ago #
  2. myDBR Team, Key Master

    Really depends on how you want to to use the select list.

    You can:

    • Use the sellers as join in query and show select columnfilter in the result[/url]
    • Use dbr.selectable
    • Store the seller list to a table in database A and that result set in parameter query for your report[/url]

    --
    myDBR Team

    Posted 4 months ago #
  3. mfiorentino, Member

    what I'm doing is creating a table in database A with all the sellers and now the report works. Where can I call the event update_seller table by call stored_procedure? I tried to do it at the beginning of the report but it tells me that the table still does not exist.

    DROP PROCEDURE IF EXISTS sp_DBR_TPVReport_MultiVentaMadrid
    $$
    CREATE PROCEDURE `sp_DBR_TPVReport_MultiVentaMadrid`(inStartDate date, inEndDate date, inLogin varchar(30), inStation varchar(30),inVendedor varchar(50),inTipoArticulo varchar (30),inProveedor varchar(30), inFormaPago varchar(15), inNumProducto varchar(30), inTipoVenta varchar(30))
    BEGIN
    call sp_DBR_call_remoteVendedor();
    select 'dbr.parameters.show';
    select 'dbr.title', concat('Statistics summary ', inStartDate, ' - ', inEndDate);

    select 'dbr.sum', 'Neto','Impuesto','Bruto';
    select 'dbr.count','Voucher';

    SELECT .............

    thanks

    Posted 4 months ago #
  4. myDBR Team, Key Master

    Not sure what the "event update_seller table" is that you are referring to. Is that the temporary table which is the parameter for dbr.remote.prepare?

    When you make a call to remote server you will have three reports:

    1. The main report which will tell which report to call in remote server (dbr.remote.prepare) and what is the report that will be shown after the data is being fetched (dbr.remote.call).
    2. The above mentioned procedure which is the parameter for dbr.remote.prepare
    3. The above mentioned procedure which is the parameter for dbr.remote.call

    So you can use the remote data once it is fetched, i.e in the procedure which is the parameter for the dbr.remote.call procedure.

    --
    myDBR Team

    Posted 4 months ago #
  5. mfiorentino, Member

    i do it:

    CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_DBR_TPVReport_MultiVentaMadrid`(inStartDate date, inEndDate date, inLogin varchar(30), inStation varchar(30),inVendedor varchar(50),inTipoArticulo varchar (30),inProveedor varchar(30), inFormaPago varchar(15), inNumProducto varchar(30), inTipoVenta varchar(30))
    BEGIN
    select 'dbr.parameters.show';
    select 'dbr.title', concat('Statistics summary ', inStartDate, ' - ', inEndDate);

    select 'dbr.sum', 'Neto','Impuesto','Bruto';
    select 'dbr.count','Voucher';

    select 'dbr.remote.prepare', 'Server_Madrid', 'remote_data_tmpVendedor', 'sp_DBR_remote_second';
    select 'dbr.remote.call', 'sp_DBR_show_remoteVendedor';

    SELECT .........

    but remote_data_tmpVendedor is empty. If i associate to a button (report)
    select 'dbr.remote.prepare', 'Server_Madrid', 'remote_data_tmpVendedor', 'sp_DBR_remote_second';
    select 'dbr.remote.call', 'sp_DBR_show_remoteVendedor';
    work.

    thanks

    Posted 4 months ago #
  6. myDBR Team, Key Master

    Not sure what you mean by stikethrough in the code.

    In the code:

    select 'dbr.remote.prepare', 'Server_Madrid', 'remote_data_tmpVendedor', 'sp_DBR_remote_second';

    Will tell myDBR to call the remote server Server_Madrid's sp_DBR_TPVReport_MultiVentaMadrid-report and to store the result in remote_data_tmpVendedor (you should use temporary table for this). The remote server call happens *after* the whole procedure sp_DBR_TPVReport_MultiVentaMadrid has been completed.

    After the remote server data is fetched to the remote_data_tmpVendedor-table, myDBR will call the 'sp_DBR_show_remoteVendedor'-report. It is there your remote data is available for use.

    --
    myDBR Team

    Posted 4 months ago #
  7. mfiorentino, Member

    Hi I can make the reports work correctly with remote call. The problem is that I need to make a remote call by returning the result in a selectList as a filter for a report and as seen from the video if I associate the remote call sp_DBR_call_remoteStation I do not return the stations

    https://drive.google.com/file/d/1nJ9q_JfIk7r3s6g05EGUzz6r8_BEqEsU/view?usp=sharing

    Thanks,
    Maurizio

    Posted 4 months ago #
  8. myDBR Team, Key Master

    Maurizio,
    unfortunately you cannot use reports as parameter queries. Parameter queries are direct SQL statements / procedure calls. To utilize the data from the remote server, save it to a local table and use that table in your parameter query.

    --
    myDBR Team

    Posted 4 months ago #
  9. mfiorentino, Member

    ok I understood this. I can do it with a table but how can I read the updated data every time I run the report? Do I have to create an external process that periodically updates the table data or is there another solution?I would like to read the updated table when run report

    thanks

    Posted 4 months ago #
  10. myDBR Team, Key Master

    You can use scheduled tasks to run the update in the background.

    Another alternative (requires some extra code) would be that you just run the remote call first and store the result in normal table. Then in the sp_DBR_showRemoteStation-report, you will trigger the call to your actual report via JavaScript.

    select 'dbr.button', 'no title', 'hideit';
    select 'dbr.report', 'sp_DBR_TPVReport_MultiVentaMadrid'; select 'result set'; select 'dbr.javascript', "$('.hideit')[0].click();";

    --
    myDBR Team

    Posted 4 months ago #
  11. mfiorentino, Member

    thanks

    Posted 4 months ago #

Reply

You must log in to post.