Including sub-totals with CVTDBFXL

Quite a common question to the helpdesk is ‘How can I include sub-totals in the excel spreadsheet?’

The CVTDBFXL command does not include functionality to calculate any form of totals.

CVTDBFXL with QUERY/400

One approach is to base your CVTDBFXL statement on a QUERY/400 query, which can include both detail and summary rows in the output. This is probably the simplest approach.

This example demonstrates how to use QUERY/400 to extract data from a database file with CVTDBFXL. It uses a query named SUBTOTAL. We are not explaining how to create a query here.

CVTDBFXL FROMFILE(*QRYDFN)

                TOSTMF(/SUBTOTALS.XLSX)

                STMFOPT(*REPLACE)

                QRYDFN(*LIBL/SUBTOTAL *NO *MIXED *SUBTOTAL)

                INCLFLD(STATE LSTNAM INIT CUSNUM BALDUE)

                HEADER(*AVAIL 2 *NO ‘Report with Sub-Totals’)

                EXCEL(*XLSX)

CVTDBFXL with SQL

Another approach would be to include totalling rows in the output using a SQL select statement. The example is using UNIONs to join the data rows to additional total rows.

I am using the Client Access sample database file QCUSTCDT in the QIWS library.

Personally, I always define SQL using a SQL source file, it gives you the ability to change the SQL without the need to re-compile any programs.

SQL source

Create a SQL source member called SUBTOTAL in QSQLSRC file.

SELECT STATE as SEQ, 0 as LVL, STATE, LSTNAM, INIT, CUSNUM, BALDUE

  FROM QIWS/QCUSTCDT        

UNION

SELECT STATE as SEQ, 1 as LVL, STATE, 'SUB-TOTAL' as LSTNAM, ' ' as INIT,         

       0 as CUSNUM, SUM(BALDUE)          

FROM QIWS/QCUSTCDT        

  GROUP BY STATE            

UNION

SELECT 'ZZ' as SEQ,  2 as LVL, '**' as STATE, 'GRAND TOTAL' as LSTNAM,                                    

       ' ' as INIT, 0 as CUSNUM,  SUM(BALDUE)          

FROM QIWS/QCUSTCDT        

  ORDER BY SEQ, LVL, CUSNUM     

In addition to the fields from QCUSTCDT two additional columns are created by the SQL, SEQ and LVL. SEQ is used to ensure the sub-totals are included with the relevant group of data records and LVL used to position them at the end of the group.

Convert DBF to excel command

The CVTDBFXL statement.

CVTDBFXL FROMFILE(*SQLSRC)                                 

         TOSTMF('/subtotals.xlsx')            

         STMFOPT(*REPLACE)                                 

         SQLSRC(*LIBL/QSQLSRC SUBTOTAL *SYS)      

         EXCLFLD(SEQ LVL)                                  

         DFNSTYLES((NOZERO *NO *NO *GENERAL *NONE *BOTTOM *DFT

                   *NO *AUTOFIT *ARIAL 10 *NO *NO *NO *AUTO

                   *NONE *AUTO *NONE *NONE *AUTO *FIXED *FIELD

                   *FMT *FMT *FMT *NO)

     (AMOUNT *NO *NO *GENERAL *NONE *BOTTOM *DFT

                    *NO *AUTOFIT *ARIAL 10 *NO *NO *NO *AUTO *NONE

                    *AUTO *NONE *NONE *AUTO *FIXED 2 *NO *NONE *FMT))

APYSTYLES((*FLDNAM CUSNUM NOZERO) (*FLDNAM BALDUE AMOUNT))                                           

EXCEL(*XLSX)

Notes:

1.       The two additional fields SEQ and LVL are excluded from the resulting excel spreadsheet.

2.       Style NOZERO is used to remove zeros from the CUSNUM column on the total rows.

3.       Style AMOUNT is used to format the values on the BALDUE column.

The generated excel