Article

Sequel Tips and Tricks: Quickly Calculate Percentages in Host Tables & Host Reports

Take the shortcut to easy host table and host report calculations.
IBM i
Do more with Sequel.
Posted:
March 28, 2017

In any type of company—from banking to distribution—you need to look at day-to-day, monthly, and even yearly transactions. You also need to keep track of customers, inventory, and sales information. 

With Sequel Data Access, you can quickly build reports and tables to summarize information, including percentages.

On the surface, it doesn’t seem like a percentage calculation would be difficult to create. When the values exist on the same record/row, the percent is easy to get.

The challenge is calculating the percent of a sub-total or grand total because the total values aren’t available at the detail record.

There are two approaches to creating percents of totals with Sequel. We will look at both a host table and host report with an example of sales representatives and their sales regions.

Looking for quick tips? Keep reading!

Want to go more in-depth? Attend training >

Let’s start with the view.

View:  RGN_SALES is a prompted view asking for the YEAR as input at run-time, which allows the user to select the information they need. The results of the view shown below summarize the data to sales representative within region, and are a great starting block. 

Let’s add to this, after building the following view we will then take the next step to add in totals and percentages using a host table (example 1) and a host report (example 2).

 

SELECT
 

 

region, salesrep, SUM(unitsales) NAME(SUMUNITSAL) LEN(11,2) EDTCDE(J$),
SUM(revenue) NAME(SUMREVENUE) LEN(11,2) EDTCDE(J$),
SUM(cost) NAME(SUMCOST) LEN(11,2) EDTCDE(J$), "&&yr" NAME(syr) COLHDG("Sales Year")

 FROM sequelex/datamart
GROUP BY region in("West", "Midwest", "South") and year=&yr
HAVING SUMUNITSAL<>0 or SUMCOST<>0
ORDER BY region Asc

 

Variable:  &YR, a Number type with a length of 4,0 with a default value of sql(char(year(current date)))

 

Region Sales Table in Sequel

 

How to Create a Sequel Host Table

Sequel tables allow you to quickly summarize large transaction files, and they also do a great job when you need to add percent of a total on the detail line. For instance, if you wanted a percent for each sales region and rep against the grand total amount, you can simply edit the category of the host tables and change the "Percent" option to "Yes."

1. Build the Sequel Table

Using the RGN_SALES view from above, a quick and easy way to start building the table is the New Table Wizard. Just follow 4 simple steps, including: defining the Table Description, Dimension (REGION and SALESREP), Category (no fields selected—I left this blank for this example), and the Column Fields (Unit Sales, Revenue, and Cost).

Table Results: RGN_SALES view results now include the grand total.

 

Regional Sales Table in Sequel

 

2. Modify the Table to Include Percentages  

Within the table design window, right-click on the categories cell and select "Edit category." Change the percent parameter value from "No" to "Yes" as shown for each column:

 

Modify Sequel tables to include percentages

 

3. Sequel Table Results

Sequel tables can be run from Viewpoint, green screen (Sequel—i5/OS), and Sequel Web Interface. Users can quickly display or export the totals AND percentages for each region, sales rep and the Column field to Microsoft Excel.

Table results show the percent of the grand total:

 

Regional Sales Host Table in Sequel

 

How to Create a Sequel Host Report

The host report allows you to take the view RGN_SALES to another level. Just like the Table Wizard, the 4-step Report Wizard can quickly and easily create a detailed report with sub-totals and grand totals over the RGN_SALES view.

The report gives us the flexibility to see the detail of the sales representative, and calculate the region percent of total and sub-total break.  

With the following example, we will need to go beyond just the initial report and build additional calculations to compute percentages based on the grand total. To do so, we must first build a view to create a work file with the grand totals for each Unit, Revenue, and Costs total:

1. Summarized View

Build a summarized view to export the grand total to a work file.

View: RGN_SALEGT – A summarized view for the grand total for all regions. 

 

SELECT

 

SUM(unitsales) NAME(SUMUNITSAL) LEN(11,2) EDTCDE(J$), SUM(revenue) NAME(SUMREVENUE) LEN(11,2) EDTCDE(J$), SUM(cost) NAME(SUMCOST) LEN(11,2) EDTCDE(J$), WDATA("&&yr") NAME (syr) COLHDG("Sales Year")

FROM

sequelex/datamart
WHERE region in("West", "Midwest", "South") and year=&yr
HAVING SUMUNITSAL<>0 or SUMCOST<>0

 

Results of RGN_SALEGT view:  A single record with the grand total for all regions.

 

Calculating Grand Totals in Sequel

 

2. Build a Cartesian Join View

A Cartesian Join or Cross Join joins every row of one file to every row of another file by purposely not specifying join criteria. In this step we’ll be joining detailed data with the exported summarized grand total.

Use the RGN_SALES view to add in the grand total database work file RGN_GRNDTT, but do not ‘link’ them together. We are building a Cartesian Join1 view that will be used by the Sequel report.

The first file references the region and sales representative information, and the 2nd file has the summarized grand totals. Within Viewpoint (VP), depending on your VP settings, you may receive a warning message “File RGN_GRNDTT is not referenced in the JOIN specification." Simply click "OK" to bypass the message.

 

SELECT

 

 

 

 

region.datamart, salesrep.datamart, SUM(unitsales) NAME(SUMUNITSAL)
LEN(11,2) COLHDG("UNIT SALES") DTCDE(J$),
SUM(revenue) NAME(SUMREVENUE) LEN(11,2) COLHDG("REVENUE") EDTCDE(J$),
SUM(cost) NAME(SUMCOST) LEN(11,2) COLHDG("COST") EDTCDE(J$),
"&&yr" NAME(syr) COLHDG("Sales Year"), sumunitsal.rgn_grndtt NAME(GrndUnit)
COLHDG("Grand Total " "of Units"), sumrevenue.rgn_grndtt NAME   
(GrndRev) COLHDG("Grand Total" "of Revenue"), sumcost.rgn_grndtt NAME(GrndCost)
COLHDG("Grand Total" "of Cost")

FROM sequelex/datamart,heath/rgn_grndtt
WHERE region in("West", "Midwest", "South") and year=&yr
GROUP BY region,salesrep,syr,GrndUnit,GrndRev,GrndCost
HAVING SUMUNITSAL<>0 or SUMCOST<>0
ORDER BY region Asc

 

View results for Rgn_Sales including three additional columns from the work file with the grand total amounts on each record:

 

Using Cartesian Joins in Sequel

 

3. Report Layout

Go from the new Report Wizard layout to the modified layout with calculated fields.

When you select the options to summarize the units, revenue, and cost within the new Report Wizard the @@SUMUNITS, @@SUMERVEN, and @@SUMCOST calculated fields will be created. For this example, you will need to build three NEW calculated fields as shown below:

Report layout for RGN_SALERX:

Change Report Layout in Sequel

Sequel Report Wizard

 

Percent of total is calculated from the grand total amount brought in from the view (i.e. gssum_unit) and the total calculations from the report (i.e. sum(sumunitsal)). In the report, division by zero will automatically return zero so conditional logic is not required.

4. Automation

Automate the process using a Sequel script.

Even though this example now has two steps: building the work file and then running the report, the steps can be streamlined to run the report on demand, or schedule the report to run every Monday or at month end. With a SEQUEL Script a multiple step process can become a single request to the user or job scheduler with no further interaction.   Using the view and report listed above; let’s build the script:

Sequel script:

The following is a very simple script, just two commands to build the grand total work file and then to display the report results.

/* Regions Sales Representative REPORT with Percent of Totals*/
/*Build the grand total work file and display the report results */

EXECUTE VIEW(HEATHBI/RGN_SALEGT) OUTFILE(HEATH/RGN_GRNDTT) SETVAR((&YR &YR))
REPORT REPORT(HEATHBI/RGN_SALERX) SETVAR((&YR &YR)) OUTQ(*NONE)

 

5. Run the Script

Depending on the user’s needs and/or requirements, the script can be run from an i5/OS command line, scheduled, a PC using Sequel Viewpoint, or from a browser or mobile device with Sequel Web Interface.

Report results with percentages of unit sales, revenue, cost per region, and the grand total:

 

Easily Generate Reports with Percentages in Sequel

Building reports with the hard calculations, like percents of totals, should not be feared or avoided.

With the Sequel host tables, you can simply flip a flag to add percents at the summarized level based on the total amount, and if you want to see the percents of total at a sub-total break, you can create a Sequel host report with a few additional calculations.

Plus Sequel host reports and host tables provide flexibility with formatting in order to highlight the percentages you need most. 

 

Take Your Sequel Tables and Reports to the Next Level

Sequel training helps you do more with the software. You'll gain expert tips and advice for getting the most out of your host tables, host reports, and so much more. 

Not using Sequel yet?