Dashboard Page with Advanced Queries

This example shows how to extend the dashboard page from the previous tutorial by creating and utilizing queries that join multiple tables and views.

Description

Attribute	Description
Layout Page File	Dashboard.html
Properties File	Dashboard.xml
Code Generation Tags Used	GEN:TableStatistic
Other	Queries

Generated Page

Layout Page File

<table cellpadding="5" style="border-collapse: collapse" border="1" bordercolorlight="#C0C0C0"

bordercolordark="#C0C0C0">

<td colspan=”3”><p align="center">Dashboard<,/p></td>

</tr>

<td>Total Products</td>

</tr>

<td>Available Products</td>

</tr>

<td>Discountinued Products</td>

</tr>

<td>Total Orders</td>

</tr>

<td>Total Orders 1996</td>

</tr>

<td>Total Orders 1997</td>

</tr>

</table>

</div>

Procedure

This procedure shows how to use queries to create more complex dashboard elements. We will create two queries by joining the Orders table and the Order Subtotals view. We will then limit each of the queries to return totals for orders placed in 1996 and 1997.

Note: The Order Subtotals view does not contain an Order Date field. In order to get the total for a specific date range, we must join the two tables together and further select the records based on order date.

Step 1: In the Application Explorer tab, select Project, New Query… to create a query. Provide a name to the query such as Orders 1996.

Tag Property	Setting
Query name	Orders 1996

Step 2: You can now limit the columns from the two tables to the specific columns needed by us to make this query more efficient. This ensures that the data transferred between the database and the application is kept to a minimum. From the Orders table, select the OrderID and OrderDate fields. From the Order Subtotals table, select the OrderID and Subtotal fields.

Tag Property	Setting
Query name	Orders 1996
Tables and views	Order Subtotals Orders
Fields	Orders.OrderID Orders.OrderDate Order Subtotals.OrderID Order Subtotals.Subtotal

Step 3: Join the Orders table with the Order Subtotals view by specifying selection criteria where

Orders.OrderID is equal to Order Subtotals.OrderID

The selection criterion joins the two tables and creates a virtual table in the database with columns from the two tables.

Step 4: Add additional selection criteria to limit the joined virtual table by orders whose order date is in 1996. The first criteria will ensure that the order date is on or after Jan 1, 1996. The second selection criterion specifies the order date is before Jan 1, 1997.

Tag Property	Setting
Query name	Orders 1996
Tables and views	Order Subtotals Orders
Fields	Orders.OrderID Orders.OrderDate Order Subtotals.OrderID Order Subtotals.Subtotal
Selection criteria	Orders.OrderID is equal to Order Subtotals.OrderID Orders.OrderDate is greater than or equal to ‘1/1/1996’ Orders.OrderDate is less than ‘1/1/1997’

Step 5: Repeat this process by creating a new query called Orders 1997 by joining the same two tables, and adding selection criteria to limit the orders to be within 1997.

Tag Property	Setting
Query name	Orders 1997
Tables and views	Order Subtotals Orders
Fields	Orders.OrderID Orders.OrderDate Order Subtotals.OrderID Order Subtotals.Subtotal
Selection criteria	Orders.OrderID is equal to Order Subtotals.OrderID Orders.OrderDate is greater than or equal to ‘1/1/1997’ Orders.OrderDate is less than ‘1/1/1998’

Step 6: Set the properties for the TotalOrders1996 code generation tag.

Tag Property	Setting
Code generation tag	TotalOrders1996
Table, view or query	Orders 1996
Display Field	Order Subtotals.OrderID
Statistic Type	Count

Step 7: Set the properties for the TotalOrders1996Amt code generation tag.

Tag Property	Setting
Code generation tag	TotalOrders1996Amt
Table, view or query	Orders 1996
Display Field	Order Subtotals.Subtotal
Statistic Type	Sum This shows the total amount of orders placed during 1996.

Step 8: Repeat the same process for the other two code generation tags.

Step 9: Build and run the application to view the page.