This example shows how to use the SQL data sources with parameters in the report. First, you need to add the Stimulsoft libraries and scripts, required for the component to work. All code should be added in the
<head> block of the HTML page:
<?php
require_once 'vendor/autoload.php';
?>
...
<?php
$js = new \Stimulsoft\StiJavaScript(\Stimulsoft\StiComponentType::Viewer);
$js->renderHtml();
?>
Next, in the
<script> block, create and configure an event handler:
<script type="text/javascript">
<?php
$handler = new \Stimulsoft\StiHandler();
$handler->renderHtml();
Next, create the viewer with the necessary options and define the
onBeginProcessData event. If value set to true, this event will be passed to the server-side event handler:
$options = new \Stimulsoft\Viewer\StiViewerOptions();
$options->appearance->fullScreenMode = true;
$viewer = new \Stimulsoft\Viewer\StiViewer($options);
$viewer->onBeginProcessData = true;
Next, create and load a report. The
loadFile() method does not load the report object on the server side, it only generates the necessary JavaScript code. The report will be loaded into a JavaScript object on the client side:
$report = new \Stimulsoft\Report\StiReport();
$report->loadFile('reports/SimpleListSQLParameters.mrt');
$viewer->report = $report;
?>
Finally, render the necessary JavaScript code and visual HTML part of the component, and close the
</script> block. The rendered code will be placed inside the specified HTML element:
function onLoad() {
<?php
$viewer->renderHtml('viewerContent');
?>
}
</script>
...
<body onload="onLoad();">
<div id="viewerContent"></div>
</body>
Finally, process SQL data source parameters on the server-side in the
onBeginProcessData() method. In this method, you can check and change all connection and query parameters, these values will not be passed to the client side. By default, all server-side events are located in the
handler.php file:
$handler->onBeginProcessData = function ($args)
{
// You can change the connection string
if ($args->connection == 'MyConnectionName')
$args->connectionString = 'Server=localhost;Database=test;uid=root;password=******;';
// You can change the SQL query
if ($args->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 ($args->dataSource == 'MyDataSourceWithParams') {
$args->parameters['Parameter1']->value = 'TableName';
$args->parameters['Parameter2']->value = 10;
$args->parameters['Parameter3']->value = '2019-01-20';
}
// Values for 'SimpleListSQLParameters.mrt' report template
if ($args->dataSource == 'customers') {
$args->parameters['Country']->value = "Germany";
}
// You can send a successful result
return StiResult::success();
// You can send an informational message
//return StiResult::success('Some warning or other useful information.');
// You can send an error message
//return StiResult::error('Message about any connection error.');
};
In the screenshot below you can see the result of the sample code:
