This example shows how to use the SQL data sources with parameters in the report. All SQL data are processed on the server side using PHP adapters. For their connection to the JavaScript side, the onBeginProcessData event is used. This event is triggered when the report engine requires data.

To send a request to the PHP server side and handle the response is sufficient to use the static StiHelper::init() function which adds all necessary JavaScript code on a Web page:
<?php
	StiHelper::init('Using Parameters in SQL Query Handler.php', 30);
?>

You can create variables with the same names in the report. In this case, when you view the report, the SQL parameters in curly brackets will be replaced with the values of variables. If you want to replace parameter values on the server-side using PHP code, you do not need to create report variables. For this, please correct the $handler->onBeginProcessData event in the handler file - just add the value for the parameter to the $args->parameters collection. Also you can correct the SQL query and connection string in this event:
$handler->onBeginProcessData = function ($args) {
	// Current database type
	$database = $args->database;
	// Current connection name
	$connection = $args->connection;
	// Current data source name
	$dataSource = $args->dataSource;
	// Connection string of the current data source
	$connectionString = $args->connectionString;
	// SQL query string of the current data source
	$queryString = $args->queryString;	
	
	// You can change the connection string
	/*
	if ($connection == 'MyConnectionName')
		$args->connectionString = 'Server=localhost;Database=test;uid=root;password=******;';
	*/
	
	// You can change the SQL query
	/*
	if ($dataSource == 'MyDataSource')
		$args->queryString = 'SELECT * FROM MyTable';
	*/
	
	// You can change the SQL query parameters with the required values
	// For example: SELECT * FROM @Parameter1 WHERE Id = @Parameter2 AND Date > @Parameter3
	/*
	if ($dataSource == 'MyDataSourceWithParams') {
		$args->parameters['Parameter1']->value = 'TableName';
		$args->parameters['Parameter2']->value = 10;
		$args->parameters['Parameter3']->value = '2019-01-20';
	}
	*/
	
	// SimpleListSQLParameters.mrt report template
	if ($dataSource == 'customers') {
		$args->parameters['Country']->value = "Germany";
	}
	
	// Returning the result of the event to the client-side
	return StiResult::success();
};

In the screenshot below you can see the result of the sample code:

Using Parameters in SQL Query

By using this website, you agree to the use of cookies for analytics and personalized content. Cookies store useful information on your computer to help us improve efficiency and usability. For more information, please read the privacy policy and cookie policy.