To generate reports, the report generator allows using data from various SQL sources. Since pure JavaScript doesn’t have built-in methods for working with remote databases, this functionality is implemented using server-side Python code.

 

Modifying connection parameters and data retrieval

Working with SQL data sources does not require any additional actions; all data adapters are already connected and configured. If it is necessary to process parameters used for connecting to the data, the onBeginProcessData event of the report object is provided. This event can be triggered on either the client- or server-side. The event arguments will contain all necessary parameters for connecting to the SQL data source, as well as the SQL query parameters. Detailed descriptions of the available argument values can be found in the Report Engine Events section.

 

All connection parameters to the data source can be modified both on the client- and server-sides. For example, you may need to change the SQL query on the client-side and the connection string on the server-side:

 

app.py

 

from stimulsoft_reports.events import StiDataEventArgs

from stimulsoft_reports.report import StiReport

 

def beginProcessData(args: StiDataEventArgs):

  if args.connection == 'MyConnectionName':

       args.connectionString = 'Server=localhost;Database=test;uid=root;password=******;'

 

report = StiReport()

report.onBeginProcessData += beginProcessData

report.onBeginProcessData += 'beginProcessData'

report.loadFile(url_for('static', filename='reports/SimpleList.mrt'))

report.render()

 

 

report.html

 

<script>

  function beginProcessData(args) {

      if (args.dataSource == "MyDataSource")

           args.queryString = "SELECT * FROM ProductsTable";

   }

</script>

 

 

 

For each SQL data source, you can specify SQL query parameters, which are stored in the data source itself as a collection. This collection is also passed in the event arguments. It is an array of objects containing the parameter name, type, and value, for example:

 

app.py

 

from stimulsoft_reports.events import StiDataEventArgs

 

def beginProcessData(args: StiDataEventArgs):

   args.parameters = [

       {

           name: 'ParameterString',

           type: 752,

           typeName: 'Text',

           value: 'Text value'

       },

       {

           name: 'ParameterInt',

           type: 3,

           typeName: 'Int32',

           value: 20

       }

   ]

 

 

 

It is allowed to modify the values of query parameters, however, the type of the new value must match the type of the parameter being modified, for example:

 

app.py

 

from stimulsoft_reports.events import StiDataEventArgs

 

def beginProcessData(args: StiDataEventArgs):

  if args.parameters != None:

       args.parameters['Parameter1'].value = 'TableName'

 

 

 

Information

 

All SQL query parameter types will correspond to the available types of the database tables being connected to. More details about SQL query parameters are discussed in this section in the chapter Using Parameters in SQL Queries.

 

 

 

Thus, in the onBeginProcessData event, you can determine the database type, connection name, and data source name, as well as view and, if necessary, adjust the connection string and SQL query for data retrieval, and set the query parameter values. When modifying argument values on the server-side, the modified values will not be passed to the client-side, allowing the use of confidential data such as login and password in the connection string, table names, prefixes, etc.

 

 

Processing loaded data

To view or adjust the loaded data before connecting and generating the report, the onEndProcessData event of the report object is provided. The event arguments will include all necessary connection parameters to the SQL data source, as well as the query result, containing column names, column types, and data rows retrieved from the SQL source. A detailed description of the available argument values can be found in the Report Engine Events section.

 

The data object of the executed SQL query has the following structure:

 

app.py

 

from stimulsoft_reports.events import StiDataEventArgs

from stimulsoft_reports.report import StiReport

 

def endProcessData(args: StiDataEventArgs):

   args.result = {

       columns: ['id', 'username', 'phone'],

       types: ['int', 'string', 'string'],

       rows: [

           [1, 'Mario Pontes', '555-6874'],

           [2, 'Helen Bennett', '555-2376']

       ]

   }

 

report = StiReport()

report.onEndProcessData += endProcessData

report.onEndProcessData += 'endProcessData'

report.loadFile(url_for('static', filename='reports/SimpleList.mrt'))

report.render()

 

 

report.html

 

<script>

  function endProcessData(args) {

       args.result = {

           columns: ["id", "username", "phone"],

           types: ["int", "string", "string"],

           rows: [

               [1, "Mario Pontes", "555-6874"],

               [2, "Helen Bennett", "555-2376"]

           ]

       };

   }

</script>

 

 

 

The available properties of the data object are listed in the table:

 

Name

Description

columns

Column names of the SQL data source table.

types

Column types of the SQL data source table, converted to known types for the report generator.

rows

Data rows from the SQL data source, represented as an array of arrays of all table rows.

 

 

All data from the SQL query result can be adjusted, and in this case, after the event is completed, the report will be generated using the modified data.

 

 

Using parameters in an SQL query

If necessary, you can use parameters in an SQL query. To do this, add parameters to a special collection in the data source and set the required type and default value for each parameter. After that, the parameters can be used in the SQL query as follows:

 

SQL Data Source

 

SELECT * FROM @Parameter1 WHERE UserID = @Parameter2

 

 

 

All parameter values will be sent to the server-side as a separate collection and can be reviewed and modified before executing the SQL query. To access the parameter values, use the args.parameters collection in the onBeginProcessData event, for example:

 

app.py

 

from stimulsoft_reports.events import StiDataEventArgs

 

def beginProcessData(args: StiDataEventArgs):

  if args.dataSource == 'DataSourceWithParams':

       args.parameters['Parameter1'].value = 'TableName'

       args.parameters['Parameter2'].value = 10

 

 

Information

 

New parameter values in this collection will not be sent to the client-side, so it is permissible on the Python server-side to assign confidential data as values.

 

 

 

Using report variables as SQL parameters

It is possible to use a variable as an SQL parameter. To do this, set the property Allow using as SQL parameter in the report variable editor, after which it can be used in any SQL query. The syntax will be exactly the same as when using parameters in the data source.

 

Information

 

Such a variable will be included in the parameters collection only if it is used in the query. Parameters from the data source collection are always passed, even if they are not used in the query.

 

 

 

Escaping parameter values

All parameter values will be automatically escaped to prevent SQL injections and ensure query execution security. If escaping is not required and you control the security of parameter values yourself, automatic escaping can be disabled. To do this, set the escapeQueryParameters property to False in the event handler:

 

app.py

 

from stimulsoft_reports.report import StiReport

 

@app.route('/report', methods = ['GET', 'POST'])

def report():

   report = StiReport()

   report.handler.escapeQueryParameters = False

  if report.processRequest(request):

      return report.getFrameworkResponse()

 

 

 

After setting the specified property, using parameters becomes unsafe, and you must strictly control the values before executing SQL queries.

 

Information

 

Escaping applies only to SQL query parameters and variables used as parameters. If a variable is used as an expression, i.e., within curly braces, such as {VariableName}, escaping will not be applied in any case. A detailed description of how variables work can be found in the Working with Report Variables section.