Here is a common tale: Your boss Mr. Rubert Lumbergh, Vice President of Operations at Home Town Bank, asks you to provide him with a spreadsheet showing new accounts that were opened, accounts that were closed, and delinquent loans from the day before. He wants these every day. You need to start each morning creating these Excel® spreadsheets before you can do anything else. You manually run through the SEQUEL Views creating spreadsheets if there is data to be shown. If there is no data for a particular day, you send him an email message. Wouldn’t it be great if you could have these files created for you, or have the messages sent in one easy step?
What about Ms. Ann B. Closure, VP of Sales? She needs access to the orders that were entered and the invoices that were generated for yesterday. She needs this access available every day, but at time of her choosing. Sometimes she needs details while other times she needs summary records by sales type. You have already created the SEQUEL Views, but she wants just one desktop icon as well as the ability to select detail or a summary.
Did you know that both of these scenarios can be handled easily with SEQUEL Scripting?
What is SEQUEL Scripting?
Scripting is a process that allows users to run multiple SEQUEL and system commands together. Scripts allow for much of the same capability of Command Language Programs (CLP) without the need for programming knowledge or a compiling of the objects.
Using SEQUEL Scripts:
- Multi-steps processes can be run from a single click. A SEQUEL Script can be run from the ViewPoint Explorer, a green screen command line or interface, a browser with SEQUEL Web Interface, job scheduler, or another program.
- Variables from one run-time prompt are available to every command in the script.
- Variables are available for command parameters as well as SEQUEL values.
SEQUEL Scripts allow much of the same functionality as a CL program (CLP) without compiling the source code. Scripts can be run from a dashboard button, shortcut, or scheduled on a job scheduler such as ROBOT/Schedule. You can set up:
- Conditional Expressions
- Code Loops
- Variable Modification
- Internal Variable Definition
- Calculation Expressions
- Substring Expressions
- Concatenation Expressions
Examples
With a combination of SEQUEL commands (to create files) and ESEND commands (to email), a script like the following, will solve Mr. Lumbergh’s tasks easily. Using IF-THEN-ELSE logic and the CONDSQL function together, the script will check for the existence of the required data and take the appropriate action.
It is a requirement of the CONDSQL command (conditional SQL) to return only “1” for true or “0” for False when testing the condition. The command conditionally searches for data that is filtered with the WHERE clause condition. If it finds data, the condition is true or “1” and the next lines of the script are processed. Otherwise it is false “0” and the ELSE action is taken.
In the example above, the Excel files are created with yesterday’s date as part of the name by using a ‘No Prompt’ variable and an SQL-derived date value.
Since there is no user intervention required, this SEQUEL Script can now be placed on a job scheduler and set to run overnight or each morning, whichever fits best into your system schedule.
The concept is similar for the second scenario featuring Ms. Closure. You need to create two-to-four views for order details, order summaries, invoice details, and invoice summaries. (Depending on the user’s needs and the data, you may be able to get both order and invoice information in one result set.) As with the first script, only yesterday’s sales and invoicing information is needed, and the same no-prompt date technique can be used in the views. Then you can reference the views with the appropriate commands in the script. Once again, conditional logic can be a powerful ally.
The example above will display two separate SEQUEL views. The command for COND is dependent on input from the user. A selection of “Y” will run the detail views. A selection of “N” will display summary views. Below is the variable definition with a Values list of “Y” or “N”.
For the VP of Sales, she can have a shortcut to run the script, and see the requested information any time. SEQUEL offers great output flexibility, giving the user the ability to have an action button on a dashboard to run the script.
With some minor changes to the scripting commands (replace DISPLAY with SCRETURN and a change to a Script View), the user can have the same flexibility of options they have with a SEQUEL View. (See the May 2013 edition of SEQUEL Connections for more on Script Views.)
SEQUEL Scripts can make your month-end, or year-end processing easier by combining all of the steps into one SEQUEL object. And with conditional logic added, your analysts can have more options from one place. Request your hands-on demo of SEQUEL and start your free trial today!