Analyze Data with the Application Builder

For sound business decisions, you need easy-to-use tools to review data, analyze data, retrieve data, and present it in meaningful ways. You need to see supporting data, make the right decisions, and make them reliably. Before you can do this, you need a tool to help you access the data.

SEQUEL ViewPoint lets you build drill-down applications so you can move easily from summary to detail data. In other words, SEQUEL lets you see “the data behind the data.” You can group related data requests together and jump from one to the next—drilling up, down, and sideways. You also can use the data to drive external processes or populate variable information when launching other requests and applications, such as e-mail or Web sites.

Before SEQUEL’s dynamic drill-down feature became available, SEQUEL users created user-defined options to provide drill-down capabilities. Back then, technical staff who understood i5/OS and SEQUEL command syntax “hand” coded user-defined options.

Now the Application Builder makes it easier for both technical and non-technical staff to define options. It also generates “implicit” drill-down options automatically based on fields used in related views (see the illustration below) and allows you add, change, or delete options and objects.

Creating a SEQUEL Application
Let’s create an application so you can see how easy it is. We’ll create and save three simple views from the SEQUELEX library and then use the Application Wizard to tie everything together.

View D1 - Summary Total (Displays summary totals for all regions)

SELECT regon,

   SUM(amtdu) COLHDG("Amount" "Due") name(d1_amtdu),

   SUM(oropn) COLHDG("Open" "Orders") name(d1_oropn),

   SUM(highb) COLHDG("High" "Balance") name(d1_highb),

   SUM(payam) COLHDG("Payments")name(d1_payam)

FROM sequelex/custmast

GROUP BY regon

View D2 - Region Summary (Displays all customers in order, by region)

SELECT regon, cusno, cname,

   SUM(amtdu) COLHDG("Amount" "Due") name(d2_amtdu),

   SUM(oropn) COLHDG("Open" "Orders") name(d2_oropn),

   SUM(highb) COLHDG("High" "Balance") name(d2_highb), 

   SUM(payam) COLHDG("Payments") name(d2_payam) 

FROM sequelex/custmast

GROUP BY regon, cusno, cname 

View D3 - Customer Total (Displays detail order information for all customers)

SELECT cusno, ordno, curln, orval, invno 

FROM sequelex/ordhead

The goal in this example is to create a drill-down path from D1 to D2 to D3. Because the Application Builder generates “implicit” drill-down options based upon the data within the views, you do not need to define variables for any of the views or set up user-defined options.

If you run views D2 or D3 by themselves, the results contain information for all regions and/or customers. However, when you run these views through the sample SEQUEL application, the views show only results for the selected region or customer.

After you save the three views, start the Drill-Down Application Wizard by selecting File > New > SEQUEL Application from the ViewPoint Explorer menu (or select the New tab and click the SEQUEL Application icon).

The Wizard walks you through the steps to create an application.

1. Name your application.
    
2. Specify a SEQUEL option file to use for the application. The Application Wizard lets you browse a file/member list on your System i. (Note: The option file you specify will be associated with this application and will override any option files specified in a user profile when users run this application.)
    
3. Select the objects that you want to include in the application.

   

   • Click Browse to display a list of SEQUEL objects on your system.
   • Navigate to the library where you saved the three views you created earlier and highlight the views.
   • Click the Open button when you have made your selections.
      
4. Select the starting object for the application (D1) and click Finish to complete the Wizard process.

When the Application Builder flowchart displays, the links (lines with arrows) are dashed, not solid. Dashed lines indicate that the Application Builder has generated an implicit link automatically based on the similar field names in the views. The implicit link between D1 and D2 exists because of the REGON (region number) field; the implicit link between D2 and D3 exists because of the CUSNO (customer number) field.

Save the application.

Now you can run the application like any other ViewPoint object by double-clicking it.

When you run the application, the Summary Totals view results (D1) displays (see the top layer of the illustration below).

To drill into the top layer, click on a region number, and pick the Region Summary option to display all the customer activity within the region (middle layer of the illustration).

To explore detailed information for a specific customer, click the Customer Summary option on the middle layer to display detailed order information (lower layer of the illustration).

Now you have the foundation you need to start building your own ViewPoint Drill-Down Applications.

Contributed by Steven Smith, Technical Consultant