SEQUEL Design View

Template views make it easy for end users to get the information they need on their own. In the Sales Analysis example below, all the following complex logic is hidden from the end user:

Instead, the user sees only a selection list that contains a subset of easy-to-understand field names.

More on Templates

Versions of SEQUEL ViewPoint that support Templates will contain a library default for this function in the SEQUEL Design View defaults window. From any ViewPoint window select Options >SEQUEL Defaults, then click the Design View tab to display the Design options. Predefined SEQUEL template views may be stored within a specific library - enter the name of the library in this field.

Designing a View Based on a Template

A New View Wizard is displayed when a ViewPoint user starts the Design View process. The user may choose to create a view from scratch (top option) or may want to select a template. The templates listed in this dialog are from the Template library specified above. In the example below a template called DATASMART has been selected.

The next Wizard dialog (below) lets the end user double-click available fields from the left column into the selected fields in the right.

Here's what the end user DOESN'T see, in the shaded area below. This is the underlying Template view. ViewPoint greatly simplifies how the fields are presented to the user as shown in the example above. Note the following:

You can incorporate other useful items in a template, such as consolidating separate Month, Day, Year and Century fields into a single date data type field or include standardized calculations.

SELECT regdesc NAME(region) COLHDG("Sales" "Region"),
  cstte NAME(state) COLHDG("Customer" "State"),
  salname NAME(salesrep) COLHDG("Sales" "Representative"),
  CASE WHEN ctype="DS" THEN "Direct Sale" 
       WHEN ctype="CS" THEN "Special Contract" 
       WHEN ctype="MI" THEN "Distributor" 
       WHEN ctype="MM" THEN "Misc" 
       WHEN ctype="NA" THEN "OEM" 
       WHEN ctype="RT" THEN "Retail" 
       END LEN(16) NAME(custype) 
          COLHDG("Cutomer" "Type"),
  cname NAME(customer) COLHDG("Customer" "Name"),
  class NAME(itemclass) COLHDG("Item" "Class"), 
  descp NAME(item),
  yr NAME(YEAR) COLHDG("Year"),
  mnth/3.01+1 LEN(1,0) NAME(QUARTER) 
    COLHDG("Quarter" "Number"),
  CASE QUARTER WHEN 1 THEN "FIRST" 
               WHEN 2 THEN "SECOND" 
               WHEN 3 THEN "THIRD"
               WHEN 4 THEN "FOURTH" ELSE "UNKNOWN" 
               END COLHDG("Quarter " "Name"),
  mnth NAME(MONTH) COLHDG("Month " "Of" "Sale"),
  qty NAME(unitsales) COLHDG("Unit" "Quantity"),
  qty*actsp.01 LEN(9,0) NAME(revenue) 
     COLHDG("Total" "Revenue"),
  qty*stdc1.03 LEN(9,0) NAME(cost) 
     COLHDG("Total" "Cost")
FROM  sequelex/saleshist, sequelex/custmast, 
      sequelex/partmast, 
      sequelex/salmast, sequelex/regmast
INNER JOIN  prdno.saleshist=prdno.partmast 
       AND cusno.saleshist=cusno.custmast
       AND salno.custmast=salno.salmast 
       AND regon.custmast=regon.regmast

The template handles all the complex logic for the end user. Calculations, CASE logic, JOINs and other view criteria are completely hidden. Template views may contain JOIN criteria for as many files as you feel necessary. However, if the user does not select fields from a file named in the template, ViewPoint automatically drops unused files from the FROM and JOIN clauses in the new view in order to conserve system resources.

Let's continue with the next Wizard dialog below. This window lets the user enter conditional selection criteria. In this example the user is going to select customers with a Total Revenue value greater than a specified amount.

The next Wizard dialog lets the user specify a sort order for the results - in this case the results will be sorted from highest to lowest Total Revenue.

From here, the end user can display the results and save the new view under a new name. ViewPoint's Template function promises to open up AS/400 and System i data access for a whole new set of users while taking some workload off IT workers.

SEQUEL Templates

More on Templates

Designing a View Based on a Template