Using Parameters in the SQL - based Document Data Sources.

Since the version 6.2 you are able to use parameters in SQL-based document data sources.

It is easy and should not bring any troubles. And below we would like to explain what is needed to do to create a query with parameters.

Let’s use the OdbcDataSource as a sample. 

  1. Create the OdbcDataSource, specify its name and the ConnectionString:



  2. Open the editor for the parameters collection:



  3. Add any required number of parameters. Specify the parameters name, ParameterType and  DefaultValue. Besides, in the Expression field you can specify an expression that calculates parameter’s value. Please note that an expression is calculated before data source initialization, so if the data source has been already initialized, changing the parameter’s values will not influence the process of data obtaining. The better option would be to get data source parameter value from report parameters. It will easily allow passing the data to a query from a client. 



  4. When all required parameters are added you can specify a query that uses these parameters. Please note that the odbcDataSource uses positional parameters, in other words, it is needed to put the question mark in the place where you need to get parameter value. For the SqlDataSource it is needed to specify parameter name with the @, for example: @parameter.

Besides, the editor checks the use of parameters. In case you specify too many positional parameters or specify a named parameter which is not included into the collection, you will get the corresponding warning message (the warning appears when you mouse over the name) when editing a query text:



 
In addition, you can set parameter values from the code in scripts. When doing this it is needed to specify the value for the Value property. For example, this way you can set a value of the maxUnitPrice parameter of the Products data source in the GenerateScript of a document:

(Document.DataSources["Products"]
  as PerpetuumSoft.Reporting.Data.OdbcDataSource)
   .Parameters["maxUnitPrice"].Value = 20;

Thus, the use of parameters gives additional functional value to the data sources of a document allowing you to avoid writing complex and ugly code to execute a parameterized query. And the ability to use scripts for setting an expression to calculate parameter value allows setting the required parameter value very easy and fast.

Add Feedback