InetSoft Technology: Execute a Query in a Report Script

The following sections explain how to execute a query in script and save the results to an array, and also how to modify an element's data binding to utilize a different query.

Running a Query

Normally, you will bind a query to a report element using the 'Data Binding' dialog box. The query that you bind will execute automatically when the report is generated, and the data returned by the query will populate the associated element.

In some situations, however, a report element must contain results from more than one query. For example, a summary table may display summary data generated from different queries. One way to do this is to construct a “master query” that provides all the desired aggregates, and then bind this single query to the element. However, this kind of query might prove very complex and difficult to write.

An easier way to bind multiple queries is to run the individual queries using script. You can then bind results from each distinct query to the individual table cells that you want to display those results. The following sections examine how to do this.
demo icon
View a 2-minute demonstration of InetSoft's easy, agile, and robust BI software.

Running a Query from Script

To run a query defined in the query registry (query.xml) or any available Data Worksheet, use the runQuery() command. parentReport.Title or parentReport['Title']

Dynamically Modifying the Parent Report

(If possible, use Report Designer to bind queries to elements. See Query Performance Considerations. Note: Run­Query does not retrieve data from Data Models.)

Scripts in a bean can dynamically access elements and variables in the parent report scope. For example, you could develop a “toolbar bean” that contains different chart type “buttons”. When the user clicks a button, this changes a chart in the parent report to the selected type. To do this, you could add an onClick Handler script to each button, such as the following:

// Run a query:
var q = runQuery('total sales');

// Run a Data Worksheet:
var q = runQuery('ws:global:NewCustomers');

In either case, the runQuery() results are returned as a two-dimensional array, whose first row contains the column headers.

You can access the query values using standard array indexing, and assign them to a table cell or text element:

// Assign first column of frst data row to a table cell:

Table1.table[1][1] = q[1][0];

Query parameters (if any) can be passed as part of the runQuery() call.

For example, to pass the report 'start_time' parameter to the query as 'start_time' and pass the current date/time as 'end_time':

var q = runQuery('total sales', [['start_time',
    parameter['start_time'],['end_time', new Date()]]);

Read how InetSoft was rated #3 for implementation in G2 Crowd's user survey-based index.