You need data to make decisions. But the data that helps you make decisions or solve problems is often waiting below the surface.
No matter what industry you’re in, your users need an easy-to-use tool that allows them to reach detailed information behind the scenes.
That's where Sequel Data Access comes in. Sequel provides two types of drill-downs—dynamic and application—that enable users to analyze critical information on the fly.
Not using Sequel yet? See how Sequel will help you >
With dynamic drill-down, users can select a record from a summarized result set, then select one field or several from a list of fields to drill into. Drill-down through a Sequel application provides greater flexibility to drill to nearly anywhere with greater control by selecting a pre-defined path.
But why would you want to use a defined path drill down?
Access Underlying Information with Sequel Applications
A Sequel drill-down application is a collection of Sequel objects (views, tables, reports, and scripts) and client requests, along with the option to store the drill-down options in a file.
The application is stored as a Sequel object (SQLAPP) on the System i. This makes it easier for both technical and non-technical users to drill into the underlying critical information and PC applications.
For example... if you have a view that simply lists customers, a Sequel application lets you “drill into” a single customer from this view to see a list of open orders, past order history, a document, or PC application.
The application can be run from the Viewpoint Explorer display and the browser using Sequel Web Interface with a simple double-click. The view or table defined as the starting point object will run first. Then the option file defined in the application will determine the options you will have to drill through the data into the secondary objects or client requests. Variables defined in the view become the key to passing the value from one level to the next.
Note: drilling into objects and applications on PCs is only available through Viewpoint.
Example: The following application will display the results of V1CUSTSALE view. The user will then be able to drill into a client request or V2ORDERV; from the V2ORDERV view results, you can drill into V3ORDLINE. The user can also drill into the PC client application from V1CUSTSALE when running the application from Viewpoint.
Figure 1
Designing Views with Parameters
Let’s create the above application so you can see how easy it is. We’ll create and save three simple views using the files from the SEQUELEX library. The Application Wizard will tie everything together, along with a PC Client request.
In this example, we are building “explicit” drill-down options and using variables to define the drill-down path the user will see.
By defining an explicit drill-down option, you are linking the objects together (i.e., customer number (CUSNO) from V1CUSTSALE view passes to customer number (&CUSNO) in V2ORDERV view) by defining a command to run the drill action.
One benefit of an explicit drill-down with variables is the ability to define which Sequel objects can drill into which objects (a predefined path) and what values are passed to the secondary objects. Explicit links are required to run applications from Sequel Web Interface.
Step 1: Build the Sequel Objects (Sequel Views)
View V1CUSTSALE: Sales for all customers in all regions
SELECT cusno, cname, ctype, regon, cstte, czipc, crlim, amtdu
FROM sequelex/custmast
ORDER BY cusno Asc
View V2ORDERV: Customer orders
SELECT ordno, cuspo, shipv, trmds, orval EDTCDE(N), curln COLHDG("Order" "line" "count")
FROM sequelex/ordhead
WHERE cusno=&cusno
Variable: &CUSNO - Number with a length of 6,0
View V3ORDLINE: Displays order line entries by order
SELECT descp.partmast LEN(23), quano.ordline EDTCDE(1), quans.ordline EDTCDE(1), actsp.ordline
COLHDG ("Actual" "Unit" "Price") EDTCDE(1), quano*actsp NAME(amount) LEN(9,2)
COLHDG("Extended" "Item" "Value") EDTCDE(N$)
FROM sequelex/ordline,sequelex/partmast INNER JOIN prdno.ordline=prdno.partmast
WHERE ordno=&ordno
Variable: &ORDNO – Number with a length of 6,0
Using the Application Wizard
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 on Application).
Step 2: Start the Application Wizard
- Describe your application.
- 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.)
- 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 them.
- Click the Open button after you have made your selections.
Figure 2
- Next, select the starting point object for the application (V1CUSTSALE) and click Finish to complete the wizard process (see Figure 3).
Figure 3
Creating Explicit and Implied Links
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 implied link automatically, based on the similar field names and variable names in the views.
The implied link between V1CUSTSALE and V2ORDERV exists because the CUSNO (customer number) field is used on the select clause of V1CUSTSALE and the V2ORDERV has a variable named &CUSNO. And the implied link between V2ORDERV and V3ORDLINE exists because of a similar relationship between the ORDNO (order number) field and &ORDNO variable.
To build an explicit link (see Figure 4), right click on V2ORDERV and V3ORDLINE and select Create Option. Define a filter name of V1CUSTSALE and V2ORDERV respectively, referencing the top (parent) view. When you press enter, the dash line should change into a solid line (see Figure 1) indicating an explicit link.
Figure 4: Create the option and define the filter. The Application Builder will do the hard work of defining the command string for you.
To add a client request, right click anywhere in the white space of the drill-down application wizard window, select Add Option, and define the client request as:
Client Requests: PC Commands (see image below)
-
Weather by zip code
- VPSHELL CMD('http://www.weather.com/weather/local/&&czipc')
Click OK to accept the Option and then save the application.
Running the Application from a User Standpoint
Now you can run the application like any other Viewpoint object by double-clicking it.
When you run the application, the summary total view results (V1CUSTSALE) are displayed. (See the top layer of the illustration below.)
To drill into the top layer, right click on a customer number (or press the white arrow with a blue circle 
from Sequel Web Interface) or select the record and the drill-down option. Then select the customer orders option to display all the customer activity for that customer. (See the middle layer of the illustration below.)
To drill into one of the PC client requests, right click on any of the records, and choose Get Weather for Zip Code to display the current weather request on the zip code or select Calculator to bring up an active calculator.
To explore detailed information for a specific customer’s orders (records from customer orders), right click an order number and select Line Item by Order to display the detailed products ordered. (See the lower layer of the illustration.)
Figure 5: Drilling into detailed information one step at a time.
Take the challenge! Add options to drill into a PC calculator and documents stored in the Integrated File System (IFS). If you need help, please contact our excellent support team to build something like:
Now you have the foundation you need to start building your own Viewpoint drill-down applications and giving users access to the detailed data. When you’re no longer limited to summary information, you can start using your data to find the answers you need.
Make the Most of Sequel
Explore training sessions for opportunities to build on your Sequel knowledge from drill-down to dashboards. Or, if you're not using Sequel yet, request a demo to see drill-down in action.