How-To: Use Templates for Data Deployment

June 27, 2016

In SEQUEL, templates give business users a structured environment to design views and get data—without IT needing to educate users on databases or systems and then hope that they find the right file or use the right field.

Templates are views pre-built by IT and power users that allow business users to follow a guided path at the time of design. By building a series of templates and letting the users get the data when they need it, IT significantly saves time previously spent pushing data to users.

The benefits of using templates are numerous!

They include:

  • Users have some control to determine fields, data filters, and the sorting of data. IT staff administers which users have access to template libraries.
  • Users do not have to be data experts because the template identifies the files and join relationship.
  • IT staff builds and changes templates easily.
  • Users can take advantage of ViewPoint’s options like graphing and quick sum, as well as download the data to PC files if needed.
  • User empowerment frees IT staff from creating ad hoc reports for users.

SEQUEL has offered templates as an option for several years. In SEQUEL version 11.3 (ShowCase 10.3), new features were added to give IT greater control over which users are limited to template options only and which users can expand on templates with ViewPoint.


Template Wizard

When a SEQUEL user is set up as a ‘template designer’ and chooses to start a new view they begin in the wizard. The template wizard, like most SEQUEL wizards, has four steps for the user.

Step 1 is to select a view to use as a template.

(Picture A)


In Step 2, the user selects the fields in the template view that they want included in their result set.

(Picture B)


Highlight one or more of the fields on the left pane. And use the arrow buttons in the center to select one or all of the fields. Use the green up and down arrows on the right pane to reposition the results fields.

Step 3 lets the user select the data they need by populating the filter. The wizard will add quotes for character fields so that the user can focus on the data.

(Picture C)


And finally, in step 4, the user selects how the results will be sorted when displayed.

(Picture D)


The wizard allows the user to go back at any step to review or change any of the selection. Once satisfied, click ‘Finish’ button to display the Actions tab.

(Picture E)


The Actions tab displays the same view options as the toolbar (Save, Display, Email, etc.) but on one central panel. If a user’s SEQUEL default is set to *LIMITED, the user can only click out of this panel to exit. If a user has other settings, they can see and change the definition on the Files & Fields tab and the SQL tab.


SEQUEL Default Settings

The SEQUEL default settings determine what a user can do in SEQUEL. A SEQUEL administrator must change or set user defaults so they become a ‘template designer’. Access the ViewPoint Administrator and select ‘Set defaults’. There are two fields in the user’s defaults that must be changed: template library and assistance level.

To assign a ‘Default template library’:

(Picture F)


This is the library where the user will be directed when they choose to design a new view. Any value other than *NONE will designate the users as a ‘template designer’.

The second setting in the user’s defaults is the one which controls whether they are allowed to modify the view they designed from the template.

On the ‘Other’ tab, set the users ‘Assistance level’:

(Picture G)


The assistance level setting of *LIMITED, when used in conjunction with the template library, prevents the user from leaving the Actions tab (above Picture E) for the SQL or Files & Fields tab to make changes to the definition they have created from the template view. It also prevents the user from designing a view definition from scratch. Notice in Picture H below that only existing view definitions are available for the user to choose as the desired template.

(Picture H)


A setting of *BASIC, *INTERMEDIATE, or *ADVANCED will allow the user to bypass the selection of a template view and create a view from scratch. Notice the first line in the template wizard in picture I below.

(Picture I)


Design SEQUEL Views As Templates

When designing a view to be used as a template for business users, there are no extra steps. However, there are some things to be aware of when building your template view.

  • The business user sees the list of template views by description. Give your views useful descriptions to make selection easier.
  • The template wizard uses the column headings for the user to select fields not the often cryptic field names. Take the time to make sure the column headings make sense to the user.
  • Field formatting is carried forward. Add dollar signs, add or remove commas, etc. in the template view so the user has what they need in the end result.
  • Create views with as many fields as the user may need. Remember, the template wizard allows users to select the fields and re-sequence them as needed. If the template contains fields the user does not need at this time, they simply do not select it from the list.

There are also a few limitations as to when SEQUEL allows a view to be used a template:

  • Prompted views cannot be templates. Run-time prompts allow the users to select the data they want to see. It is a powerful and flexible way to let the user decide. But prompted views cannot be used as templates. If they exist in the template library, they will not be displayed as a template option.
  • Views that are designed with the following cannot be used as templates:
    • WDATA: A function to hide a field but keep it in the definition
    • GROUP BY: Used with summary or grouping functions
    • UNION: Having more than one SELECT clause in the view definition
    • Derived field joins: Files linked by a user created field in view design

Where you save the view definition is up to the template view designer. However, the idea behind templates is to give the users (within a functional area) a manageable list of views to select the needed data. For example, a library can be created for Sales Template View. Any view intended to be a template can be placed here. Business users assigned to this library will only see views that relate to their area of data needs. Each user can, in theory, have their own library of template views. But this can be a management headache. Departmentalized structures, along with security of the view definition objects, allow better system control.


With SEQUEL, your data deployment options are various. When you choose templates as a deployment option, you can empower business users—without adding a constant burden on IT and power users to design every report. 

Get more out of SEQUEL with training


Related Products