Working with Multiple Datasources and External Data

Although a reportico project is linked to a single database, it is possible to pull in data from other datasources, but this requires some degree of coding to fetch the data and make it available to Reportico.

There are two cases where you may wish to involve another datasource in your project

- populating selection criteria from another datasource for use in your main query

- joining data from another datasource in with your main query to present in your report output

Criteria Selection Box from Another Data Source

Reportico provides in the run.php file the ability to provide criteria list entries. Look in this file for the text “user_parameters” and you will be in the right area.

There are two ways both involving the user_parameters parameter.

The easiest but less performance efficient way is to pass in an array containing the criteria selection elements. This can be a straight hardcoded array, or an array generated from some code which pulls from another database, api or anything that returns an array. If you are using a function that does not run near instantly, it is better to use the function method specified below, because the run.php is executed on every click/web call in reportico.

For example you can provide a list of countries as an array. Important! You must specify keys to the index. The keys are what are passed in with the query, the array values ae what the user selects from and sees in drop down lost boxes, check boxes etc. This is an example :-

  $q->user_parameters["country"] = [ "values" => [ "UK" => "United Kingdom", "AUS" => "Australia", ...... ] ];

Then you need to create a criteria in your report which links the array in. You must use the Custom List criteria type for this, for example:-

{{:country_criteria.png?400|

Then finally bring the criteria into your main query as normal…the criteria name above is “country” so that is what is specified in the where clause below:-

{{:criteria_sql.png?400|

Criteria Selection Box from a function

The above solution provides poor performance so it is better to pass references functions that generate the criteria data. This requires use of the user_parameters and user_functions Reportico paramters.

Add the name of the function to the user_parameters array. For example, for a list of countries

  $q->user_parameters["country"] = [ "function" => "getcountries" ];

Then add to the user_functions array in run.php the actual getcountries definition :-

  $q->user_functions = [ "getcountries" => function() { 
                                $countries = ... PHP code to fetch countries from somewhere..;
                                return $countries; } ];
  
  

Then create the criteria item the same as above using the custom list option of {USER_PARAM,country} to refer to the country parameter, and then add it into the main SQL in the usual way.

Linking in External Data into the Main Query

For this you have to use the Custom Source Code option. See the page Custom Source Code for details of how to add this in.

You need to put in here code to open your remote datasource, probably via a PDO driver, fetch data from it and build a temporary table from it. You can then link this temporary table into the main query using a standard JOIN command.

So for example your custom code might be as follows:- Note the $_pdo is a reference to your project database so you can use it to build temporary tables.

  // Create a temporary table ..
  $sql = "CREATE TEMPORARY TABLE t_temp_countries ( country_code INTEGER, country_name CHAR(100) )";
  $_pdo->query($sql);
  

  // Extract
  $remotedb = new PDO('sqlsvr:database=yourdb;host=127.0.0.1', 'user', 'password');
  $sql = 'SELECT code, name FROM countries ORDER BY name';
  foreach ($remotedb->query($sql) as $row) {
      $insertsql = "INSERT INTO t_temp_table VALUES ( {$row["code"]}, '{$row["name"]}' )"; 
      $remotedb->query($insertsql);
  }

AS this code is executed before the main report query, you can refer to it in the main query. For example if you wanted a list of customers joined with the country name they live in, from the remote database above, you can use a main query like this :-

  SELECT name, address, country_name
  FROM customers
  JOIN t_temp_countries ON t_temp_countries.country_code = customers.country_code
  • working_with_multiple_datasources.txt
  • Last modified: 2018/11/03 17:31
  • by admin