Using Parameters in SQL Query
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::createHandler() function which adds all necessary JavaScript code on a Web page.
designer.onBeginProcessData = function (event, callback) {
<?php StiHelper::createHandler(); ?>
} You can use the parameters in the SQL query as expressions, enclosing them in curly braces.
SELECT id, name
WHERE id = {Variable1}
ORDER BY name {Variable2}
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.php file - just add the value for the parameter to the $event->parameters collection. Also you can correct the SQL query and connection string in this event.
$handler->onBeginProcessData = function ($event) {
$database = $event->database;
$connectionString = $event->connectionString;
$queryString = $event->queryString;
$event->parameters["Variable1"] = 10;
$event->parameters["Variable2"] = "ASC";
return StiResult::success();
};