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$), |
| 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)))
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.
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:
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:
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.
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) |
| 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:
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:
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:
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.
