SEQUEL Connections

By offering easy data access and analysis plus custom reporting options—the keys to fast, informed decisions—SEQUEL Software has long had a strong presence in the world of retail sales. Recently, we interviewed Stacy Jensen, SEQUEL Software Sales Manager, for some insights into how SEQUEL helps customers handle the challenges of today’s highly competitive retail world.

Q: Stacy, based on your experiences, what are some of the bigger challenges facing the retail world today and tomorrow?

Stacy: I think there are a number of challenges, but two of the bigger ones are to stay competitive and retain existing customers. By staying competitive, I mean the ability to expand market share and differentiate yourself from your competitors.

As far as customer retention goes, retailers need to analyze purchasing history and optimize the customer’s experience. That includes the how and where of their buying habits—brick and mortar stores vs. online, for instance.

Q: How does SEQUEL address these challenges and why is it so popular?

Stacy: SEQUEL is a powerful data-gathering product that allows them to pull information quickly and effectively. Its custom reporting options are especially useful for forecasting trends.

Q: Do you have any specific examples of how customers take advantage of what SEQUEL offers?

Stacy: Some big retailers have internal IT departments and we’ve trained many of them on SEQUEL. They’re delighted when they discover how easy it is to create complex views (queries) and custom reports. One of our customers recently started using SEQUEL to manage their inventory system. Basically, when an existing or potential customer walks into one of their stores and requests an item, if it’s not in stock, the clerk can use a browser interface [the SEQUEL Web Interface] with a view (query) to locate the item throughout their entire inventory chain, fast.

That’s a big plus for customer retention—with that kind of information at their fingertips, the customer or potential customer isn’t likely to walk out the door to a competitor. And, it points out another great strength of SEQUEL: non-technical end users can run sophisticated queries by entering something as simple as a stock number.

Another retail customer created custom reports to help them analyze their sales data—things like the number of returns and their impact on individual store sales. Every company has slightly different needs, so the ability to customize reports and web and dashboard interfaces is critical.

Q: Stacy, each year, Island Pacific, a leading supplier of merchandising, store operations, and point-of-sale software solutions for specialty retail chains, sponsors a User Conference. You’ll be attending the 2012 Island Pacific User Conference [LINK HERE]. What opportunities does this present and what do you expect to learn from this event?

Stacy: SEQUEL software and Island Pacific software really combine nicely to create an integrated data access, analysis, and retail solution. Users get faster access to their data to forecast trends and make timely decisions based on their inventory and their customers’ needs.

The Island Pacific User Conference is a chance for retailers, product experts, and vendors to get together and discuss topics such as global retailing, while they share ideas in open forums to get the most out of their software. I see this as an opportunity to gather fresh, first hand information about the latest challenges that retail vendors face and their needs.

Afterwards, I hope to make more informed recommendations for our retail customers and potential customers, based on the insights I learn about the current state of the retail marketplace. I specifically want to find more ways that SEQUEL can help them access and present information to make more informed decisions. I really want to pull the Island Pacific and SEQUEL teams together to better help retail customers and entice prospects.

Q: Great—that sounds like a win-win for everybody. Thank you Stacy for your time and your insights into the world of retail.

Automate Month-End Reporting with SEQUEL Scripts

By Steven Smith, Technical Consultant

You can automate processes by using SEQUEL scripts to combine several commands into a single job stream (and include variables). If multiple SEQUEL objects in the script contain a prompt for variables, the SETVAR command lets you create a single prompt for the different variables.

Hint: This is especially useful for month-end reports where you want to pass the same date range to multiple reports.

Creating Two Simple Views with Variables

Let’s look at a sample script that displays two views that use variables for a beginning and an ending date. When the script runs, you’re prompted once for the date range.

Simple View 1: Test 1

Simple View 2: Test 2

Note: Because the field PDATE (in the WHERE clause) is a date data type, you need two ampersand (&) symbols within a pair of double quotes for the variable name.

Getting Started

1. On the Variables tab define the Prompt Type as DATE for both variables. Exit, and save the first view as TEST1 in the SEQUELEX library. Create and save the second view as TEST2 in SEQUELEX.

   

2. Select File\New\Script from the ViewPoint Explorer menu to start the script designer.
3. In the Script Designer, enter the three lines shown below to monitor for global messages (and prevent the script from ending if an error occurs), and display the two views.

   

   The SETVAR parameter contains pairs of variables. The first variable in the pair is the variable defined in the view. The second variable is defined in the script—its value is passed to the view variable at run-time (they don’t have to be the same name, but it’s less confusing this way).

   Note: We strongly recommended that you follow the syntax shown in the example for the second script variable—use two ampersands (&) and enclose with single quotes. This ensures substitution, no matter what the prompt type or data value is.

4. Define the variables by opening the Variables tab and specify DATE as the prompt type.

   

5. Exit and save the script.

When you run the script you’ll see only one prompt—passing the dates from the script to the view. Without SETVAR, you’d be prompted twice—once for the script and once for the view.

Bypass the Prompt Screen with Special Values

Sometimes you want to pass a variable to a SEQUEL view that is not supplied or seen by the user. For example, you might want to pass the job date or the system time into the title of the view, report, or table. This is especially useful with the SEQUEL Web Interface.

There are several special default values for the variables available in SEQUEL, including:

Use these keyword values on the Default parameter after you have defined a variable in the view. When you use the keyword with the special value *NOPROMPT (*NP, or *NOPMT) on the Prompt Text parameter, the value is passed into the view without user input.

Here is a simple prompted view to pass a variable job date (*JOBDATE) into the title of the view:

Getting Started

1. On the Variables tab, define the Type as DATE, the Prompt Text as *NOPROMPT, and the Default as *JOBDATE. (See the discussion of *NOPROMPT in the December 2011 issue of SEQUEL Connections).

   

2. To see the job date value in the title of the results, put the variable in the view description when you save it.

   

   When the view is run, you won’t be prompted for the date—it’s automatically inserted into the title.

   

   For a complete description of special keywords and *NOPROMPT, see Variable Specification in the SEQUEL ViewPoint User Guide.

   ---

   Q & A

   Q1: In a drill-down application, what is the difference between an implied and explicit link?

   A1:Implied links are dynamic and work only if the field from the view is named the same as the variable in the view used for drilling—the drill-down relationship is part of the application.

   Explicit links rely on a command saved in an option file and use the SETVAR parameter to pass the value from the first view to the variable of the second view.

   Q2: How can you create an explicit link in the Application Designer?

   A2: Right-click on the secondary view and choose Select Option… A window with the DISPLAY command syntax displays. Click OK to save the option in the option file associated with the application.

   ---

   IBM i Solution Edition for Help/Systems

   

   Purchase SEQUEL (or other software solutions from Help/Systems—Robot Automated Operations Solution; PowerTech IBM i security solutions; and Bytware antivirus and monitoring solutions for IBM i) and enjoy big discounts on training, services, and IBM POWER7 systems.

   For details, contact your local IBM Business Partner, or Doug Fulmer at doug.fulmer@helpsystems.com, or visit our IBM i Solution Edition web page.