Introduction to ADF Data Visualization Components - Graphs, Gauge, Maps, Pivot Table and Gantt

Purpose

In this tutorial, you use the DVT (Data Visualization Tool) components in Oracle ADF to build some graphical representations of data, such as Bar Charts, Gauges, Gantt charts and Geographical maps. You also explore the use of a pivot table.

Time to Complete

45 minutes

Topics

This tutorial covers the following sections:

	Overview
	Scenario
	Prerequisites
	Create a Master Detail Order Page
	Add a Bar Chart
	Add a Gauge
	Add a Geographic Map
	Create a Pivot Table
	Create a Gantt Chart
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

ADF Data Visualization components are a set of rich interactive ADF Faces components that provide significant graphical and tabular capabilities for analyzing data. After having performed the prerequisites section, this tutorial shows you how to use the ADF DVT components to graphically represent data.

Back to Topic List

Scenario

After building a master-detail page, you will experiment with some advanced features by adding graphical data to your application. You add new tabs to the page and use dedicated ADF DVT components. You will use a predefined application model, which is based on ADF Business Components.

Back to Topic List

Prerequisites

Before starting any section of the tutorial, you should:

	Download the Required Components and Start JDeveloper
	Install the Database Schema
	Upload the Startup Application
	Create a Database Connection

Back to Topic List

Downloading the Required Components and Start JDeveloper

In the following steps, you download JDeveloper 11g and other files required for this tutorial.

Back to Topic

Back to Topic List

Installing the Database Schema

In this part, you use a JDeveloper Application to install the schema in your database and populate the FOD schema with the corresponding data.

Back to Topic

Back to Topic List

Uploading the Starter Application

Before you create any component, you must first open the startup application. To do this, perform the following steps:

Back to Topic

Back to Topic List

Creating a Database Connection

You need to create a Database Connection to the FOD schema. To do this, perform the following steps:

1.	From the Menu Bar, select View | Database | Database Navigator.
2.	In the Database Navigator, right click BrowseEditApp and select New Connection from context.
3.	Enter a name for the connection, select the connection type, and enter the username and password. To follow along with this example, use FOD for the connection name, fod for the username and fusion for the password.
4.	Click Test Connection to confirm that you can connect. Click OK if the connection was successful.
5.	In the Application Navigator, open the Application Resources panel by clicking on it.
6.	In the Application Navigator, expand the Connections and Database nodes in the Application Resources panel to see the FOD connection you just created. You can expand the FOD node to view the database objects.

Back to Topic

Back to Topic List

Exploring the Startup Application

The startup application uses Orders, OrderItems and ProductsBase entity objects and their corresponding view objects. It also uses additional view objects that you discover when required to use them.

1.	Open the Application Navigator and expand the Model project nodes. Notice the existing Entities, Views, Links objects and the FODModule. The model project contains all the necessary components needled for the application.
2.	Right click the Model project node and select Project Properties from context.
3.	In the Project Properties dialog, select the Business Components node and in the Connection field, select the FOD connection from the available list. Then click the Update button
4.	In the Connection dialog, click the Test Connection button and make sure the operation is successful. Click OK to dismiss the dialog, then OK again.
5.	To test the module in the Business Component tester, in the Application Navigator, right-click FODModule and choose Run. Wait for the Browser window to open.
6.	In the Business Component Browser, double-click OrdersItemsOrdersFkLink1.
7.	Use the navigation buttons to browse through the Orders
8.	Close the Business Component Browser window.
9.	Collapse the Model project. Expand the ViewController project nodes. Notice the existing components that are created by default when creating a Fusion Web Application.

Back to Topic

Back to Topic List

Creating the Master Detail Order Page

The startup application uses Orders, OrderItems and ProductsBase entity objects and their corresponding view objects. It also uses additional view objects that you discover when required to use them.

1.	In the Application Navigator right click the ViewController node and select New from context.
2.	In the New Gallery, click the Current Project Technologies tab, then select the Web Tier | JSF category and select the JSF Page item. Click OK.
3.	Set the File Name field to OrdersAndStocks. Click OK.
4.	The new page opens up in the Design editor.
5.	From the Component Palette, using the ADF Faces | Layout library, drag and drop a Panel Tabbed component onto the page.
6.	Select the showDetailItem and in the Property Inspector using the Common node, type MDOrders in the text field.
7.	In the Application Navigator, expand the Data Controls accordion and the FODModuleDataControl node.
8.	Drag the OrdersView1 node onto the visual editor, and drop it onto the Page. From the Create context menu, select Forms --> ADF Read-only Form.
9.	In the Edit Form Fields dialog, check the Include Navigation Controls check box., select all the fields except the following: OrderId, OrderDate, OrderStatusCode, OrderTotal, and CustomerId. Then click Delete . Click OK.
10.	In the Data Controls panel, expand the OrdersView1 node. Drag the OrderItemsView2 node onto the lower part of the form.
11.	From the Create context menu, select Tables --> ADF Read-only Table.
12.	In the Edit Table Columns dialog, select both Row Selection and Sorting. Select all the columns except the following: LineItemId, Quantity, UnitPrice, and ProductName. Click Delete . Then click OK.
13.	With the new table selected, in the Property Inspector, using the Box tab in the Style node, enter 100 Percent for the Width . For the Height enter 100 Pixel. Select the Common tab and type OrdItem as the Id.
14.	The page should look like this:
15.	Click Save All icon on the JDeveloper menu bar, or select File | Save All.
16.	Right click within the page and select Run from context, or click the Run button to run the page.
17.	The page loads up in your browser.
18.	Experiment the Navigation buttons.
19.	Try the sorting facility on any of the detail columns.
20.	Close the browser window. At this point you have a working page, you are now going to add graphical components such as bart chart, gauge, gantt chart, geographic info and create a Pivot Table.

Back to Topic List

Add a Bar Chart to the Page

In this section, you start adding a basic Bar chart based on warehouse information to the OrdersAndStocks page, then you enhance the bar chart characteristics with more sophisticated rendering.

	Create the Basic Bar Chart
	Refine the Basic Bar Chart

Back to Topic List

Creating the Basic Bar Chart

Based on the view WarehouseStockLevelsView, you add a bar chart to the MDOrders tab in the OrdersAndStocks page to render for each product of a given order the corresponding stock level in each warehouse location. The WarehouseStockLevelsView view is linked to the OrderItemsView via the productId attribute.

Back to Topic

Back to Topic List

Refining the Basic Bar Chart.

In this section you enhance the bar chart adding some animation features.

Back to Topic

Back to Topic List

Using a Gauge for Stock Levels

In this section, you add a tab to the page that displays the stock level using a gauge representation. Then you add a button that creates an Excel type file based on the displayed data.

	Displaying Stock Levels Using a Gauge
	Adding an 'Export to Excel' Button

Back to Topic List

Displaying Stock Levels Using a Gauge

Based on the view WarehouseStockLevelsView, you add a Gauge graph reflecting the stock level in each warehouse

1.	Back in JDeveloper, on the OrdersAndStocks.jspx page in the editor window, right click the within the MDOrders tab and from context select Insert after Show Detail Item --> Show Detail Item.
2.	In the Property Inspector, using the Common node, set the Text to StockLevels
3.	Click the StockLevels tab, then open the Data Controls accordion and select FODModuleDataControl | OrdersView1 | OrderItemsView2 | WarehouseStockLevelsView1. Drop it onto the StockLevels pane
4.	Select Tables --> ADF Read-only Table.
5.	In the Edit Table Column dialog, select Row Selection and Sorting check boxes. Delete the following attributes: WarehouseId, Address1, PostalCode. Click OK.
6.	In the Property Inspector, for the table component, using the Box tab in the Style node, set the width to 100 percent and the Height to 600 pixels.
7.	Using the Behavior node, set the Partial Triggers property to ::OrdItem.
8.	In the Component Palette, select the from the ADF Data Visualization, select the Gauge component and drop it onto the Quantity column. In the Create Gauge dialog, choose the Status Meter category and select the Horizontal Status Meter with Thresholds. Click OK.
9.	In the Structure pane, right click the outputText #{row.Quantity} and delete it.
10.	In the Structure pane, select the dvt:gauge component and in the Property Inspector set the following properties: for the Common node set the Gauge type to STATUSMETER. Next to the Value Field select Expression Builder from the list. In the Expression Builder dialog, expand the JSP Objects | Row and double click QuantityOnHand to shuttle it in the Expression pane. Click OK.
11.	Select the Gauge Data tab and type 0 for the MinValue and 1000 to the MaxValue assuming for our simple example that no stock level is ever greater that 1000 for a single product in a warehouse.
12.	Select the Style node and type 100 Percent for the Width and 80 Pixel for the Height.
13.	Select the af:column above the Gauge component in the Structure pane and in the Property Inspector, for the Appearance node set the Width to 200.
14.	In the next few steps, you use the thresholds for the gauge, allowing you to specify in our example minimum and maximum stock level indicators. In the Structure pane expand the gauge | ThresholdSet component.
15.	Notice that 3 thresholds exist inside the Threshold Set.
16.	Select the first Threshold component in the Structure pane. In the Property inspector set the id field to t00, the ThresholdMaxValue to 30 which is the minimum stock level alert we want to set, and type mini for the Text.
17.	Select the second Threshold component in the Structure pane. In the Property inspector set the id field to t01, the ThresholdMaxValue to 700 which is the optimum stock level we want to have, and type maxi for the Text.
18.	Select the the third Threshold component in the Structure pane. In the Property inspector leave the ThresholdMaxValue empty. In the Advanced node set the id field to t02.
19.	In the Structure pane right click the tickLabel component, and select Insert inside dvt:tickLabel --> Number Format from context.
20.	In the Property Inspector, for the Number Format tag, using the Scale tab, set the ScaleFactor property to SCALEFACTOR_NONE
21.	In the Structure pane select the Metric Label component. Using the Common node, set the Position property for the Metric Label to LP_INSIDE_GAUGE_RIGHT.
22.	In the Structure pane right click the metricLabel component, and select Insert inside dvt:metricLabel --> Number Format from context.
23.	In the Property Inspector, for the Number Format tag, using the Scale tab, set the ScaleFactor property to SCALEFACTOR_NONE
24.	The Page should look like this:
25.	Click the Run button to run the Page.
26.	Click the StockLevels tab to display the corresponding stock levels with the Gauge. The stock level gauge shows how a product is doing in a warehouse regarding to the minimum and maximum alert levels. Move your mouse over a gauge to display the corresponding quantity.
27.	You can experiment switching from the MDOrders tab to the StockLevels one using a different order.
28.	Close your Browser window.

Back to Topic

Back to Topic List

Adding an 'Export to Excel' button

You add a button to your page that exports displayed data into an Excel type file.

Back to Topic List

Creating a Geographic Map

Working on the same view previously used for warehouses located in the US, using the spatial functionality of Oracle, you add a map to a new tab in the existing page, indicating the geographical location of the related warehouses.

1.	Back to JDeveloper, in the OrdersAndStocks page, right click within the StockLevels tab and select Insert after Show Detail Item --> Show Detail Item
2.	In the Property Inspector using the Common node, change the Text to Location. Using the Style | Box tab set the Width to 100 Percent - the Height to 600 Pixel.
3.	Click the Location tab, then in the Data Controls panel, expand the OrdersView1 | OrderItemsView2 nodes. Select the WarehouseStockLevelsView1 node and drop it on the page in the Location tab.
4.	From the Create context menu, select Geographic Map -> ADF Map and Point Theme.
5.	In the Create Geographic Map dialog, click New to create a new Map configuration.
6.	If needed type mapConfig1 as the Id, then for the MapViewer URL field select tapViewer from the list and click the Edit button. In the Create URL Connection dialog, enter the following URL information: URL: http://elocation.oracle.com/mapviewer Click the Test Connection button. The connection should be successful. Click OK
7.	Repeat the previous step for the Geocoder URL field using the geo value and click Edit. Provide the following URL information:URL: http://elocation.oracle.com/geocoder/gcserver Click the Test Connection button. Connection should be successful. Click OK. Back on the Map Configuration dialog, click OK.
8.	The world map displays. Enter -100 as Starting X and 40 as Starting Y to focus on north America. Then move up the zoom cursor to zoom by 2. Click Refresh. Click OK.
9.	The Create Map Point Theme Binding dialog comes up. For Address, pick US (street, city, state, zip) and choose the following fields: Street Address1 City City State StateProvince Zip Code PostalCode Label WarehouseName Under Point Data specify:· Data Quantity on Hand Label Quantity Click OK to insert the map.
10.	In the Structure window, select the dvt:map component. In the Property Inspector, on the Behavior node, set the PartialTriggers property to ::OrdItem (the id of the table).
11.	On the Appearance node, set the MapZoom to 3.
12.	To add a toolbar to the map, allowing zooming, area selection, and other functionalities, from the Component Palette, select the Toolbar component and drag and drop it underneath the dvt:map component.
13.	In the Create Map Toolbar dialog, select map as the map id (the name of the map component). Click OK.
14.	The page should look like the following:
15.	Click Save All icon on the JDeveloper menu bar, or select File | Save All.
16.	Click the Run button to run the OrdersAndStocks.jspx page. Select an Order with an Order Item for which the product is stored in several locations.
17.	Click the Location tab to see a map indicating where the warehouses are located geographically for this product. Move the window in the incrustation to select another area on the map.
18.	Reduce the bottom right window incrustation. Try the toolbar buttons for example click the Zoom in button Click within the map to zoom in.
19.	Click the mouse pointer icon in the toolbar and then click on the red spot of your choice to pop up a window showing the location name and the quantity. Close the popup window.
20.	Experiment other functions like selecting the Area button and click the Rectangular selection button, then click within the map to select a rectangular area on the map.
21.	The surface of the selected area is calculated and displayed.
22.	In the toolbar, click View and select Information Panel.
23.	Position your mouse over a location on the map to get the geographical coordinates, in terms of longitude/latitude.
24.	In the toolbar, click the distance icon .
25.	Double click on a first spot on the map, then double click on another spot of the map to get the distance between both locations.
26.	Close the Browser window.

Back to Topic List

Create a Pivot Table

Based on the View you just created, you add a new page to the page flow diagram and then create the Pivot Table within the new page.

1.	Back to JDeveloper, expand the Model | Application Sources | oracle.fod.model and double click the SalesPivotTable view to open it in the editor.
2.	In the editor click the Query tab, to display the SQL statement. Notice that the view summarize the Sales done by product category, product name and States for the US. SELECT category_translations.CATEGORY_NAME as "Category", products_base.product_name as "Product", addresses.state_province as "State", sum(order_items.QUANTITY * order_items.UNIT_PRICE) as "Sales" FROM products_base, product_categories_base, category_translations, orders, order_items, addresses WHERE products_base.product_id = order_items.product_id and products_base.CATEGORY_ID = product_categories_base.CATEGORY_ID and product_categories_base.CATEGORY_ID = category_translations.CATEGORY_ID and orders.SHIP_TO_ADDRESS_ID = addresses.ADDRESS_ID and orders.order_id = order_items.ORDER_ID and addresses.COUNTRY_ID = 'US' GROUP BY category_translations.CATEGORY_NAME, products_base.product_name, addresses.state_province order by category_translations.CATEGORY_NAME, products_base.product_name
3.	Click the OrdersAndStocks tab to open the page, right click within the Location tab and select Insert after Show Detail Item --> Show Detail Item.
4.	In the Property Inspector using the Common node, change the Text to PivotTable.
5.	Click the Pivot Table tab, then In the Data Controls panel drag the SalesPivotTable1 data control on the page.
6.	From the Create context menu, choose Tables -> ADF Pivot Table.
7.	The Create Pivot Table Binding dialog comes up. Drag Category and Product to the row edge. Drag State to the column edge, and Sales to the data area at the intersection row/column. Click Next.
8.	in the Data Values, enter the label Sales, then select the following Attribute Display Values from the drop down list: Attribute Attribute Display Value State State Category Category Product Product Click Next.
9.	In the configure drilling pane, select Insert Drilling option, the Before Children choice and Enable the drill path Category to Product. Click Next.
10.	In the Configure aggregation pane, click the Add button . The Sales Attribute is added to the Attribute-level Overrides with the Sum function.: Select the Category Totals tab. Select the Add button , then select the Attribute Category and enter the Total Label – Total Category. Click Next.
11.	In the Configure sorting pane, select the Add button , then select the Attribute: Category and Sort Attribute: Product. Click Next.
12.	The Preview pane displays the sample design. Click Finish to insert the PivotTable into the page.
13.	In the Structure window, select the dvt:pivotTable component. In the Property Inspector, on the Style | Box tab, set the Width property to 100 Percent.
14.	Click Save All icon on the JDeveloper menu bar, or select File | Save All.
15.	Click the Run button to run the page.
16.	The OrdersAndStocks page opens up in the browser, click the PivotTable tab. Notice the Total Category row.
17.	Expand some Categories to view the Product details:
18.	Test the Pivot functionality, for example, select the Product column and move it underneath the State row.
19.	The new Table displays state sales per product. Notice the new totals.
20.	Pick the Product row and put it back to its original location. You can experiment other column/row moves. For example pick the State row and move it next to the Product column to display product sales per states. Notice that you can also swap layers - e.g. swap Category with State, by dropping one layer on top of the other.
21.	Close your browser window.

Back to Topic List

Create a Gantt Chart

In this section, you add a new tab to the page, for a Gantt Chart. Based on two existing views OrderShippingSummary and OrderShippingDetails linked with a master detail relationship, you add a new tab to the page and then create the Gantt diagram within the new page.

1.	Back to JDeveloper, expand the Model | Application Sources | oracle.fod.model node and select the OrderShippingSummary View Object.
2.	In the Editor, click the Query tab to see the SQL statement. This query retrieves for each person amongst all orders, the oldest order date and the most recent shipped date. The query looks like the following: SELECT persons.PERSON_ID, persons.FIRST_NAME as "First Name", persons.LAST_NAME as "Last Name", min(orders.ORDER_DATE) as "Order Date", max(orders.ORDER_SHIPPED_DATE) as "Shipped Date", concat('Summary', '') as "Task Type" FROM orders, persons WHERE orders.CUSTOMER_ID = persons.PERSON_ID group by persons.person_id, persons.FIRST_NAME, persons.last_name
3.	Expand the Model | Application Sources | oracle.fod.model node and select the OrderShippingDetails View Object.
4.	In the Editor, click the Query tab to see the SQL statement. This query retrieves for each person all of the orders, giving the detail information of the OrderShippingSummary. The query looks like the following: SELECT persons.PERSON_ID, orders.ORDER_ID, orders.ORDER_DATE as "Order Date", orders.ORDER_SHIPPED_DATE as "Shipped Date", persons.FIRST_NAME as "First Name", persons.LAST_NAME as "Last Name", concat('Normal', '') as "Task Type" FROM orders, persons WHERE orders.CUSTOMER_ID = persons.PERSON_ID
5.	Double click the FODModule to visualize the existing relationship between OrderShippingSummary and OrderShippingDetails .
6.	Back to the OrdersAndStocks page, right click within the PivotTable tab and select Insert after Show Detail Item --> Show Detail Item
7.	In the Property Inspector using the Common node, change the Text to OrdersPlanning.
8.	Click the OrdersPlanning tab, then In the Data Controls panel select OrderShippingSummary2 and drop it onto the page.
9.	From the dynamic menu select Gantt --> Project
10.	In the Create Project Gantt dialog, in the Tasks tab select the following values from the drop down lists: Task Id PersonId Task Type TaskType Start Time OrderDate End Time ShippedDate These values specify that a summary task duration is created for each person, going from the earliest ordering date up to the latest shipped date, amongst all individual orders. Select the PersonId attribute and click the Delete button .
11.	Now we create subtasks showing for each individual order the specific duration. Click the Subtasks tab in the dialog and select the following values from the drop down lists: Subtasks Accessor OrderShippingDetails Sub Task Id OrderId Sub Task Type TaskType Start Time OrderDate End Time ShippedDate Click OK.
12.	In the Property Inspector, for the Common tab change the Start Time and End Time periods to be two months before and one month after the current date (or the date you've installed the FOD schema). Notice that the dates indicated here won't probably match with the ones stored in your database. Your page should look like the following:
13.	Click the Run button to run the page.
14.	The page loads up in the browser Click the OrdersPlanning tab.
15.	The Gantt chart shows for each person/customer the total duration for all the orders.
16.	Click on one person + icon to expand the order details and visualize the state of each individual order.
17.	Enlarge the left pane to view more columns.
18.	Notice the toolbar created by default for Gantt charts. Click the Zoom out icon and notice the change in the legend above the tasks, substituting the column label from week to months and quarters.
19.	In the Menu click the View | Time Scale. You can choose the unit for the timing scale.
20.	Close your browser window.
21.	You've successfully performed this tutorial.

Back to Topic List

Summary

In this tutorial, you've seen how to create a tabbed JSF Page and use some ADF DVT graphical features.

	Create a Master Detail page
	Add a Bar Chart
	Add a Gauge Chart
	Add Geographical Info
	Create a Pivot Table
	Create a Gantt Chart

Back to Topic List

Place the cursor over this icon to hide all screenshots.