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
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
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
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
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
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
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
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
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
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
11. 

    mfiorentino, Member

    thanks

Reply

You must log in to post.