4.1.3 Query Example
Assume that you need to create a table containing sales summary information: Total sales volume, product with highest volume, and customer with the highest volume. There are several possible ways that you can design this table:
• Write a stored procedure to return the three unrelated aggregates as the result of a single query, and bind the table to this query. However, designing such a stored procedure is awkward and requires advanced knowledge of database programming. Therefore, this approach is not recommended.
• Use a Data Worksheet to compute the three desired aggregates, and merge these into a single Data Block. Then bind the table to this Data Block. (See the Data Mashup for details).
• Design three simple queries to return the three desired aggregates. Use script to independently run the three queries, and then explicitly assign the results to corresponding table cells.
Walkthrough
The following example illustrates the third approach. Assume that you have defined the following three simple queries in Style Studio:
• 'total sales' – returns total sales volume
• 'top product' – returns product with highest volume
• 'top customer' – returns customer with the highest volume
Follow the steps below to create the table that displays these values:
1. Create a new table with three rows and two columns.
2. Select each of the three cells in the left column, and enter the following strings into the cells:
a. “Total Sales”
b. “Top selling product”
c. “Highest Volume Customer”
3. Right-click the table, and select 'Script' from the context menu. This opens the Script Editor for the table.
4. Enter the following script. This runs each query in turn, and assigns the result to the corresponding table cell.
var sales = runQuery('total sales');
var product = runQuery('top product');
var customer = runQuery('top customer');
table[0][1] = formatNumber(sales[1][0], "$#,###.00");
table[1][1] = product[1][0];
table[2][1] = customer[1][0];
Note: Set your Repository location to examples/docExamples/script. See Configuring and Selecting a Repository in Getting Started for instructions.
See the summary.srt report in the examples/docExamples/script directory for a similar example.
Figure 4. Query Example (summary.srt)

See Also
Data Tables, for further information about scripting tables.
Table Element, in Report Design, for information on creating a table.
Formula Tables, for more advanced use of query data in tables.
| << 4.1.2 Query Performance Considerations | © 1996-2013 InetSoft Technology Corporation (v11.4) | 4.2 Binding Queries >> |