Inject parameters into complex SQL queries

In some cases, such as when your restriction includes SQL functions, you need to inject a simple value into the custom SQL. For example, suppose you had this restriction in your view (.axvw) file:

<restriction
type="sql"
sql="${sql.yearOf('cal_date')} &gt;= ${parameters['yearStart']}
AND ${sql.yearOf('cal_date')} &lt;= ${parameters['yearEnd']}" />

To inject the “yearStart” and “yearEnd” values into the restriction, you must pass them from the client-side JavaScript as parameters, rather than as complete restrictions.

To do so, use the optional parametersForDataSources argument to the openPaginatedReportDialog() method:

View.openPaginatedReportDialog(
paginatedReportUrlParameters, restrictionsForDataSources
[,parametersForDataSources] [, x] [, y] [, width] [, height]).

Here is an example:

parametersForDataSources = { ‘yearStart’: valueStart, ‘yearEnd’: valueEnd } ;
View.openPaginatedReportDialog(
'ab-my-paginated-report.axvw', null, parametersForDataSources ) ;

The program will expand the parameter in any dataSource restriction or SQL Query in which the parameter name appears.

Some of your views may have different parameters used in different dataSources. In this case, differentiate the parameters using different parameter names:

parametersForDataSources = {
‘dsForBl_dateStart’: valueStartBl, ‘dsForWo_dateStart’: valueStartWo } ;
View.openPaginatedReportDialog(
'ab-my-paginated-report.axvw', null, parametersForDataSources ) ;

The parameters are just a billboard within the execution context of the view. From this execution context, views and paginated reports pull the parameters that appear in each dataSource. If a parameter is present but does not exist in one of your dataSources, that dataSource will just ignore it.