Using Parameters in SQL Query
Our sample projects and report templates can help you learn the basics of working with our products.
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
To send a request to the PHP server side and handle the response is sufficient to use the static
You can use the parameters in the SQL query as expressions, enclosing them in curly braces:
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
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();
};