How to 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.

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()]]);

More Articles About Reporting

9 Top Management Dashboard Examples - This project management marketing dashboard example aids in the monitoring of various marketing initiatives and the start and end times of various project stages. This type of dashboard helps digital marketing agencies track the progress of their consultants on various initiatives. A Gantt chart tracks the start and end date of various projects, giving a visual representation of project durations. Gantt charts now comes as a standard option in InetSoft's visualization engine...

Collection of Data Analytics Software Samples - Searching for the best data analytics software samples? InetSoft provides examples from its highly-rated, easy to use dashboard analytics software. This revenue analytics sample breaks down revenue by plan type, state, and date. A dropdown selector changes fields displayed in the bar chart, helping to cut down on the overall number of elements in the dashboard...

Convenience of Cloud Dashboards - InetSoft understands the importance of being able to share your business data securely with others. Cloud-based dashboards allow the flexibility of sharing and publishing interactive dashboards easily and quickly with colleagues, partners or customers. With cloud dashboards, you will never have to be stuck on a question wherever you are. Just simply login to the remote server, and view or edit, from anywhere, on any device. Cloud dashboards allow you to operate independently with complete security. Pull data from cloud data sources, Microsoft Excel, or sources on premise; Style Intelligence can integrate all of them seamlessly. Publish and share dashboards, as well as invite others to view and interact. Data is safe and only viewable and accessible by the author, and whoever is given permission to view...

Creating An Effective Ticket Tool Dashboard - With Style Intelligence, support agents can have an intelligent ticket tool dashboard to automatically categorize tickets and provide a first suggestion. This speeds up the whole process of support. The algorithms give this suggestion for a level of accuracy, and then the machine learning algorithm directly adds words to tickets based on the predictive category for the next agent. We're seeing the same trajectory in customer service with ticket classification, ticket routing, how to answer suggestions all the way to complete customer service dialogues. Like in the vehicle area, it doesn't have to be done in one giant leap. It can be done in concrete steps that provide value for customers in every step of the way...

Operations Support KPI: Stockout Rate - Evaluates the percentage of time when products are out of stock or unavailable for customers...

Preventive Action Software KPIs - Corrective and Preventive Action (CAPA) software is designed to help organizations manage and track the resolution of issues and incidents, as well as to implement measures to prevent their recurrence. The key performance indicators (KPIs), metrics, and charts displayed in CAPA software typically focus on various aspects of the corrective and preventive action process. Here are some examples: Number of CAPA Requests: This metric tracks the total number of CAPA requests initiated within a specific period. It provides an overview of the volume of issues and incidents being addressed by the organization...

Previous: Report Bean Example

Next: Query Performance Considerations