Switch to: V12V11V10V9V8V7V6V5

JavaScript Examples

Archives

Description

These examples demonstrate the usage of the embedded JavaScript engine for solving the few specific tasks.

You can use this project to answer your HOW TO questions. If you will not find here what you want, please contact us and we will try to enhance the examples or the reports engine itself.

Parameter Preprocessing

Report: Report_Transactions_Period

Demonstrates how the report parameters can be preprocessed and transformed in the JS-script into other parameters to change the source query.

The query of report:

SELECT * FROM tblTransaction WHERE fldDate > DATEADD( NOW(), $P(pDateUnit), $P(pUnitsCount) )

In this example, report expects 'Period' parameter to be set by the user of the report. It is declared as 'Interactive', so if you run it from the Valentina Studio, you will be asked to set the value:

Note: In your ADK code you should get the value from the user and assign it to parameter yourself otherwise the default value will be used.

Initial parameter is transformed into two non-interactive parameters of the query - pDateUnit and pUnitsCount in the pre_build script of the report:

var period = report.parameterValue( 'Period' );
if( period == 'Week' )
{
	report.setParameterValue( 'pDateUnit', 'week' );
	report.setParameterValue( 'pUnitsCount', -1 );
}
else if( period == 'Month' )
{
	report.setParameterValue( 'pDateUnit', 'month' );
	report.setParameterValue( 'pUnitsCount', -1 );
}
else if( period == 'Quarter' )
{
	report.setParameterValue( 'pDateUnit', 'month' );
	report.setParameterValue( 'pUnitsCount', -3 );
}
else if( period == 'Six Months' )
{
	report.setParameterValue( 'pDateUnit', 'month' );
	report.setParameterValue( 'pUnitsCount', -6 );
}
else if( period == 'Year' )
{
	report.setParameterValue( 'pDateUnit', 'year' );
	report.setParameterValue( 'pUnitsCount', -1 );
}

Resulting report for Month:

Partial Sum Calculation

Sometimes it is necessary to calculate the partial sum of previous rows for each row.

Note: Without the use of JavaScript engine it is possible to achieve only with writing custom SQL expressions, that are executed for each row.

The intermediate partial sum can be stored either as the parameter value or the value of report object property.

So there are two ways to perform this task, they are described below.

V1 - Partial Sum As Parameter

Report: Report_Transactions_Partial_Sum_V1

Partial sum is accumulated in the pPartialSum integer parameter in the Report Body pre_place script:

var sum = report.parameterValue( 'pPartialSum' );
sum += report.cursor.columnValue( 'fldAmount' );
report.setParameterValue( 'pPartialSum', sum );

It is printed to report via an expression field added to report with appropriate text: $P(pPartialSum).

Resulting report:

V2 - Partial Sum As Report Property

Report: Report_Transactions_Partial_Sum_V2

Initial value of the property is set in the report pre_build script:

report.partial_sum = 0;

Partial sum is accumulated in the pre_place script of the Report Body:

report.partial_sum += report.cursor.columnValue( 'fldAmount' );

To output the partial sum an expression is used as in the firs variant. But its text is empty in design mode and filled during runtime in its pre_place script:

report.controls.expPartialSum.label = report.partial_sum;

Resulting report:

String Formatting

Report: Report_Transactions_String_Formatting

Usually, the values of fields are formatted either in source query of the report or using the format property of the control.

But in the case if it is impossible to write formatting SQL expression or format property doesn't give the desired result, the JavaScript engine can be used.

In the example database, the account numbers are stored without any delimiters. A bit of JavaScript code makes it more readable.

Main formatting function is defined in the pre_build script of the report:

report.format_card_number = function( value )
{
  var v = value.replace( /\s+/g, '' ).replace( /[^0-9]/gi, '' )
  var matches = v.match( /\d{4,16}/g );
  var match = matches && matches[0] || '';
  var parts = [];
 
  for( i = 0, len = match.length; i < len; i += 4 )
    parts.push( match.substring( i, i + 4 ) )
 
  return parts.length ? parts.join( '-' ) : value;
}

This function is used in the Report Body pre_place script to assign values to expressions:

report.controls.expFmtSender.label = report.format_card_number( report.cursor.columnValue( 'fldAccSender' ) );
report.controls.expFmtReceiver.label = report.format_card_number( report.cursor.columnValue( 'fldAccReceiver' ) );

Resulting report: