Small, medium, and large companies from various industries like banking, healthcare, education, and distribution all run a variety of Sequel requests to access and distribute data on a regular basis. Sequel scripts make it easy to link together multi-step processes rather than manually running a series of steps to accomplish a complex task.
With functionality similar to a CL program, Sequel scripts automate multi-step processes without needing to compile the source code. Scripts can be run from the Viewpoint and Sequel Web Interface explorers, as well as a dashboard button, a shortcut, or scheduled within a job scheduler such as Robot Schedule. They can be basic—running a series of commands that someone might run from a command line—or use run-time variables and run a series of commands that depend on user input for desired functions and output. Scripts allow multiple step processes to be linked together without the need to write a program. Simple or complex logic can be added to allow variable definition, loops, and conditional expressions—producing the reports you need and want.
Sequel scripts are incredibly customizable and allow you to do many things. Below are just three examples of how scripts allow you to:
- Dynamically pass today’s date into a filename
- Conditionally run a process based on user input at run time
- Pass values from a file for record selection
How to Dynamically Pass Today’s Date into a Filename
Do you have jobs that run daily, weekly, or monthly that produce the same report name? By using variables, Sequel can automate the file name creation and create a unique name each time the job runs. Reports with unique, dated filenames are easier to track and archive.
Part, or all, of any parameter of a command can be a variable. This allows the value to be changed at run time. Alternately, variables can be defaulted to expressions so the user will not be prompted to select a value at run time.
This example uses a traditional variable for the email address and will prompt the user at run time. The filename is dynamically derived from an expression of text and system date. When variables appear inside a command parameter that requires quotes, like RECIPIENT, two ampersands must be used.
Script 1:
/* Weekly Report -- emailed out -- attaches a unique file name */
SEQUEL/REPORT REPORT(SEQUELEX/TOPTENR) PCFMT(*PDF) TOSTMF('/home/sequel/&&FILENM') REPLACE(*YES) RECIPIENT('&&recip') EMLMSG('Top 10 Customer Balance Report')
/* Clean-up - delete the temporary IFS object */
RMVLNK OBJLNK('/home/sequel/&&FILENM')
Variables:
As a result, a weekly report will be emailed as a PDF document uniquely named “Trial_Balance-2014-07-21.pdf”. The following week will have a different, unique name “Trial_Balance-2014-07-28.pdf”.
Dynamically passing today’s date into your report filename allows you to easily organize and sort your files both in your email and on your PC, making sure you are referencing the right data for your needs.
Conditionally Run a Process Based on User Run-Time Input
Do you run a report where you might want detailed information one time and summary the next time? Do you need to select if you output your report to PDF or XLS? The following is a script that will prompt the user to define what type of data they desire: summary or detailed. Based on the user input, different reports will be run using IF/THEN/ELSE logic in the script.
Tip: For a script like this to work, a summary and detailed report must already exist. Based on the user selection within the script, one of the reports will run.
Script 2:
/* This script processes a view and emails the results. */
/* User selects what type of report to send out - detail or summary */
IF COND(&RPTTYPE = "Summary")
SEQUEL/REPORT REPORT(SEQ_LIB/CUSTORDRP1) PCFMT(*PDF) RECIPIENT('&&RECIP') EMLMSG('Customer Order SUMMARY Report')
ELSE
SEQUEL/REPORT REPORT(SEQ_LIB/CUSTORDRP2) PCFMT(*PDF) RECIPIENT('&&RECIP') EMLMSG('Customer Order DETAIL Report')
RETURN
ENDIF
The Variables:
At run time, the user is prompted for email address and type of report:
Pass Values from a File for Record Selection
Selecting records (or running specific commands based on a value in a control file) can save time and ensure the correct process is executed with the correct records selected. By using a control file, users will not be prompted for the internal value being passed to the variable of the record selection. In our example, we want to run a Sequel view with records for a single region. The region number is stored in a one record “control file”. The CHGVAR command uses the VALUESQL parameter and a Sequel statement to return a single record populating the variable at run time.
Tip: The internal variable ‘®ION‘ is used only within the script and is not defined in the variables tab.
Script 3:
/* Declare an internal variable */
DCL VAR(®ION) TYPE(*CHAR) LEN(3)
/* Change the variable value to the region value in the control file */
/* Note: To check the current control value, run: DISPLAY SQL('select * from webprod/controlf') */
CHGVAR VAR(®ION) VALUESQL('select int(regon) len(3) from webprod/controlf') SERVER(*SEQUEL)
/* Run the view passing in the control record*/
DISPLAY VIEW(SEQUELEX/DDCUSNO) TEXT('Customers from Region &®ion') SETVAR((®ON ®ION))
Enhancing scripts with conditional logic to control the record selection and distributed results, or bringing in a value from control file, provides users with streamlined reports. Scripts seamlessly provide a set of instructions that run Sequel reports, views, and tables the same way each and every time to produce and distribute customized results as needed. You can simplify complex multi-step processes while working in a graphical environment. More importantly, the end user only has to handle a single run-time request with a single click. Have questions about setting up scripts in your environment? Check out our how-to documents online or contact us.
Let's Get Started
Sequel data access software makes it possible for you, your business users, and your executives to quickly, securely access important data—making business intelligence easier than ever.
