• Welcome, Guest. Please login.
 
July 16, 2019, 12:30:03 pm

News:

Welcome to the SQLitening support forums!


Recent posts

Pages 1 2 3 4 ... 10
11
>>Instead of listing debits and then credits, how about different reports just listing the table?

I have similar reports, the General Ledger Transactions Report and Journal Report.

But my final General Ledger Summary Report looks like the attached.
12
Yes, sorry.
13
>>I think there should only be one line counter.

I see what you mean now. But I can't see the linecounter variable. Do you mean the lineheight?
14
Instead of listing debits and then credits, how about different reports just listing the table?

DATE/CLIENT REPORT
DATE     CLIENT     TYPE           DEBIT       CREDIT    PAGE 1
------   ------     -------        ------      ------
6/6/19   1          Car            1000        1000   
                    Car            3000          50   
                    Car             400          20

6/7/19   2          Horse          1000        1000   
                    Horse          3000          50   
                    Horse           400          20




CLIENT/DATE REPORT
CLIENT   DATE       TYPE           DEBIT       CREDIT    PAGE 1
------   ------     -------        ------      ------
1        6/6/19     Car            1000        1000   
                    Car            3000          50   
                    Car             400          20

2        6/7/19     Horse          1000        1000   
                    Horse          3000          50   
                    Horse           400          20
15
I would take a look at it later today. Thanks.
16
I think there should only be one line counter.
Lines will print over themselves in this demo.
#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"
#INCLUDE "ddoc_p32.inc"
%DropTable = 1

MACRO WRAP(p1)  = WRAP$(p1,$SQ,$SQ)
MACRO UNWRAP(p1) = UNWRAP$(p1,$SQ,$SQ)
MACRO BindT(p1)  = slBuildBindDat((p1),"T")
GLOBAL gName,gAddress AS STRING
GLOBAL gbytes AS LONG

%vbBlack=&H0&:%vbRed=&HFF&:%vbGreen=&HFF00&:%vbYellow=&HFFFF&:%vbBlue=&HFF0000
%vbMagenta=&HFF00FF:%vbCyan=&HFFFF00:%vbWhite=&HFFFFFF:%vbGrey=&HC0C0C0:%LIGHT_SHADE=&HE0E0E0

FUNCTION PBMAIN () AS LONG 'fredrick10.bas
 slOpen "junk1.db3","C"
 IF %DropTable THEN
  slexe  "drop table if exists tblJournalEntry"
 END IF
 slexe  "create table if not exists tblJournalEntry(AccountName,AccountType,Date,Credit INTEGER, Debit INTEGER)"
 InsertData
 gName="Green line":gAddress = "Red line"
 'slOpen "meganetERPDB.db3","C"
 PrintReport
END FUNCTION

FUNCTION PrintReport() AS LONG

  LOCAL PageHeightMaximum AS SINGLE
  LOCAL iHandle,EndCode,TheBin,TheStyle,DefaultZoom,NoDialog,x,pagenum,fontsize,fontstyle,defaultcolor AS LONG
  LOCAL zOutputFile,zDocTitle,zDefaultFont AS ASCIIZ * 64
  LOCAL lineheight,LeftMargin AS SINGLE  'height deleted
  LOCAL AssetsHeight AS SINGLE
  LOCAL EquityHeight AS SINGLE
  LOCAL RevenueHeight AS SINGLE
  LOCAL ExpensesHeight AS SINGLE
  LOCAL NetProfitHeight AS SINGLE
  LOCAL szExpensesAccount AS ASCIIZ * 200
  LOCAL szExpensesAmount AS ASCIIZ * 100
  LOCAL szRevenueAccount AS ASCIIZ * 200
  LOCAL szRevenueAmount AS ASCIIZ * 100
  LOCAL szEquityAccount AS ASCIIZ * 200
  LOCAL szEquityAmount AS ASCIIZ * 100
  LOCAL szAssetsAccount AS ASCIIZ * 200
  LOCAL szAssetsAmount AS ASCIIZ * 100
  LOCAL sName,sAddress, sFrom, sTo AS STRING
  LOCAL dTotalRevenue, dTotalExpenses, dNetProfit, dTotalCredit, dTotalDebit AS DOUBLE

  sName="Green line":sAddress = "Red line"
  sFrom = "01-01-2019"
  sTo  = "28-05-2019"


  PageHeightMaximum = 10.1  'inches

  defaultzoom = %DDOC_ZOOM75
  zdefaultFont = "Courier New"
  TheStyle = %ddoc_Portrait
  fontsize = 8
  TheBin = %DDOC_Bin_Auto '7
  'topmargin= 1.6    'not in use
  lineheight = .1666 '1/6  'each line height
  LeftMargin = 0.5



  zdoctitle = "Mega-Net ERP - General Ledger Summary Report"
  DefaultZoom=%DDOC_ZOOMFIT + %DDOC_VIEWBUILD
  'DefaultZoom=%DDOC_ZOOM75  + %DDOC_VIEWBUILD
  'DefaultZoom=%DDOC_ZOOM100 + %DDOC_VIEWBUILD

  ihandle = dpStartDoc(0,_
                      zDocTitle, _
                      zoutputfile,_
                      %DDOC_INCH,_
                      %DDOC_PAPER_A4,_
                      %DDOC_SYSTEM_DEFAULT, _
                      TheBin,_
                      DefaultZoom&)
  IF ihandle < 1 THEN
    SELECT CASE ihandle
      CASE %DDOC_NOHANDLES  : ? "no file handles",,"dpStartDoc"
      CASE %DDOC_BADFILE    : ? "bad file name",,  "dpStartDoc"
      CASE %DDOC_FILEEXISTS
        ?  zoutputfile + " already exists so will delete document",,"dpStartDoc"
        KILL zoutputfile
        IF ERR THEN ? "Could not delete file",,EXE.NAME$
      CASE ELSE:      ?  "Error code" + STR$(iHandle),,"dpStartDoc"
    END SELECT
    EXIT FUNCTION
  END IF


  GOSUB PrintHeading

  'Revenue
  dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Courier"
  slSel "Select AccountName, AccountType, Date, SUM(Credit) as CreditSUM From tblJournalEntry " & _
  "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Revenue' GROUP BY AccountName"
    DO WHILE slGetRow()
      szRevenueAccount = slFN("AccountName")
      szRevenueAmount = FORMAT$(VAL(slFN("CreditSUM")), "#,.00")
    GOSUB PrintRevenueDetailLine
    'IF height => PageHeightMaximum THEN
    IF RevenueHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Arial"
    END IF
  LOOP
  SLEEP 200

  'Expenses
  dpFont ihandle, %DDOC_FONTNORMAL, 9, %vbBlack, "Courier"
  slSel "Select AccountName, AccountType, Date, SUM(Debit) as DebitSUM From tblJournalEntry " & _
  "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Expenses' GROUP BY AccountName"
  DO WHILE slGetRow()
      szExpensesAccount =  slFN("AccountName")
      szExpensesAmount = FORMAT$(VAL(slFN("DebitSUM")), "#,.00")
    GOSUB PrintExpensesDetailLine
    'IF height => PageHeightMaximum THEN
    IF ExpensesHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
    END IF
 LOOP
  SLEEP 200

  'Assets
  dpFont ihandle, %DDOC_FONTNORMAL, 5, %vbBlack, "Courier"
  LOCAL s AS STRING
  slSel "Select  AccountName, AccountType, Date, SUM(Debit) as DebitSUM From tblJournalEntry " & _
  " WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Assets' GROUP BY AccountName"
  DO WHILE slGetRow()
      szAssetsAccount = slFN("AccountName")
      szAssetsAmount = FORMAT$(VAL(slFN("DebitSUM")), "#,.00")
      GOSUB PrintAssetsDetailLine
    'IF height => PageHeightMaximum THEN
    IF AssetsHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
    END IF
  LOOP
  SLEEP 200

  'Equity
  dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Courier"
  slSel "Select AccountName, AccountType, Date, SUM(Credit) as CreditSUM From tblJournalEntry " & _
  "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Equity' GROUP BY AccountName"
    DO WHILE slGetRow()
      szEquityAccount = slFN("AccountName")
      szEquityAmount = FORMAT$(VAL(slFN("CreditSUM")), "#,.00")
    GOSUB PrintEquityDetailLine
    'IF height => PageHeightMaximum THEN
    IF RevenueHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Arial"
    END IF
  LOOP
  SLEEP 200

  'Total Credit
  slSel "Select AccountName, AccountType, Date, SUM(Credit) as TotalCredit From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "'"
  IF ISTRUE slGetRow() THEN
    dTotalCredit = VAL(slFN("TotalCredit"))
  END IF
  slCloseSet

  'Total Debit
  slSel "Select AccountName, AccountType, Date, SUM(Debit) as TotalDebit From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "'"
  IF ISTRUE slGetRow() THEN
    dTotalDebit = VAL(slFN("TotalDebit"))
  END IF
  slCloseSet

  GOSUB PrintTotalsDetailLine

  'Revenue
  slSel "Select AccountName, AccountType, Date, SUM(Credit) as TotalRevenue From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Revenue'"
  IF ISTRUE slGetRow() THEN
    dTotalRevenue = VAL(slFN("TotalRevenue"))
  END IF
  slCloseSet
  'GOSUB PrintHeading

  'Expenses
  slSel "Select AccountName, AccountType, Date, SUM(Debit) as TotalExpenses From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Expenses'"
  IF ISTRUE slGetRow() THEN
    dTotalExpenses = VAL(slFN("TotalExpenses"))
  END IF
  slCloseSet
  'GOSUB PrintHeading

  dNetProfit = dTotalRevenue - dTotalExpenses

  GOSUB PrintNetProfitDetailLine

  endCode = %DDOC_END_VIEW + %DDOC_END_DELETE
  dpEndDoc iHandle, EndCode

EXIT FUNCTION

PrintHeading:
  dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 13, %vbGreen, "Arial"
  dpText ihandle, 4.25, 1.1, %DDOC_CENTER, "" & sName
  dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
  dpText IHandle, 4.25, 1.3, %DDOC_CENTER, "" & sAddress
  dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
  dpText IHandle, 4.25, 1.5, %DDOC_CENTER, "General Ledger Summary Report"
  dpText IHandle, 4.25, 1.8, %DDOC_CENTER, "Date Range: " & sFrom  & " To  " & sTo
  dpLine IHandle, .5, 10.3, 8, 10.3, 1, %vbBlack
  'dpText IHandle, .5, 10.6, %DDOC_LEFT, "Total Debit: " & FORMAT$(dTotalDebit, "#,.00")
  'dpText IHandle, 3.5, 10.6, %DDOC_LEFT, "Total Credit: " & FORMAT$(dTotalCredit, "#,.00")
  dpLine IHandle, .5, 11.0, 8, 11.0, 1, %vbBlack
  dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 11, %vbBlack, "Arial"
  RevenueHeight = 2.5
  dpText IHandle, .5, RevenueHeight, %DDOC_LEFT, "Revenue: "
  ExpensesHeight = 3.5
  dpText IHandle, .5, ExpensesHeight, %DDOC_LEFT, "Expenses: "
  NetProfitHeight = 6.5
  dpText IHandle, .5, NetProfitHeight, %DDOC_LEFT, "Net Profit: "
  AssetsHeight = 7.5
  dpText IHandle, .5, AssetsHeight, %DDOC_LEFT, "Assets: "
  EquityHeight = 9.0
  dpText IHandle, .5, EquityHeight, %DDOC_LEFT, "Equity: "
  RevenueHeight+= lineheight +.05 'looks a bit better
  ExpensesHeight+= lineheight +.05 'looks a bit better
  AssetsHeight+= lineheight +.05 'looks a bit better
  EquityHeight+= lineheight +.05 'looks a bit better
  'NetProfitHeight+= lineheight +.05 'looks a bit better
RETURN

PrintRevenueDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, RevenueHeight,%ddoc_left,szRevenueAccount
  dpText iHandle,7.0, RevenueHeight,%DDOC_DECIMAL,szRevenueAmount
  RevenueHeight+= lineheight
RETURN

PrintExpensesDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, ExpensesHeight,%ddoc_left,szExpensesAccount
  dpText iHandle,5.0, ExpensesHeight,%ddoc_decimal,szExpensesAmount
  ExpensesHeight+= lineheight
RETURN

PrintNetProfitDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,4.5, NetProfitHeight,%ddoc_left,FORMAT$(dNetProfit, "#,.00")
  NetProfitHeight+= lineheight
RETURN

PrintAssetsDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, AssetsHeight,%ddoc_left, szAssetsAccount
  dpText iHandle,5.0, AssetsHeight,%DDOC_DECIMAL, szAssetsAmount
  AssetsHeight+= lineheight
RETURN

PrintEquityDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, EquityHeight,%ddoc_left, szEquityAccount
  dpText iHandle,7.0, EquityHeight,%DDOC_DECIMAL, szEquityAmount
  EquityHeight+= lineheight
RETURN

PrintTotalsDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  'dpText iHandle,LeftMargin, AssetsHeight,%ddoc_left,szAssets
  dpText IHandle, 0.5, 10.6, %DDOC_LEFT, "Total: "
  dpText IHandle, 5.0, 10.6, %DDOC_DECIMAL, FORMAT$(dTotalDebit, "#,.00") & " Dr"
  'dpText IHandle, 5.7, 10.6, %DDOC_LEFT, "Total Credit: "
  dpText IHandle, 7.0, 10.6, %DDOC_DECIMAL, FORMAT$(dTotalCredit, "#,.00") & " Cr"

  AssetsHeight+= lineheight
RETURN
END FUNCTION

FUNCTION mySQLiteDate(sDate AS STRING) AS STRING
    'From dd-MM-yyyy To yyyy-MM-dd

    FUNCTION = MID$(sDate,7,4) & "-" & MID$(sDate,4,2) & "-" & MID$(sDate,1,2)

END FUNCTION

SUB InsertData
  LOCAL x        AS LONG
  LOCAL acctnum  AS LONG
  LOCAL numaccts  AS LONG
  LOCAL numrecs  AS LONG
  LOCAL counter  AS LONG
  LOCAL bytes    AS LONG
  'gbytes = 0
  numaccts  = 99
  numrecs    = 100
  DIM s(1 TO numaccts * numrecs) AS STRING
  slexe "begin exclusive"
  FOR acctnum = 1 TO numaccts  'account
    FOR x =1 TO numrecs        'insert this many into account
      INCR counter
      s(counter) = bindt(USING$("Acct #",acctnum)) + bindt("Assets") + bindt("2019-05-28") + bindi(STR$(acctnum)) + bindi(STR$(acctnum))
      gbytes+=LEN(s(counter))
    NEXT
  NEXT
  slexebind "insert into tblJournalEntry values(?,?,?,?,?)",JOIN$(s(),""),"V5"
  slexe "end"

  counter = 0
  REDIM s(1 TO numaccts * numrecs) AS STRING
  slexe "begin exclusive"
  FOR acctnum = 1 TO numaccts  'account
    FOR x =1 TO numrecs        'insert this many into account
      INCR counter
      s(counter) = bindt(USING$("Acct #",acctnum)) + bindt("Revenue") + bindt("2019-05-28") + bindi(STR$(acctnum)) + bindi(STR$(acctnum))
      gbytes+=LEN(s(counter))
    NEXT
  NEXT
  slexebind "insert into tblJournalEntry values(?,?,?,?,?)",JOIN$(s(),""),"V5"
  slexe "end"

END SUB
17
Hello CJ,

This is what I came up with using slSEL. Just what I wanted. I think slSEL is more suitable for a report like this. Thanks a million.

#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"
#INCLUDE "ddoc_p32.inc"

%vbBlack=&H0&:%vbRed=&HFF&:%vbGreen=&HFF00&:%vbYellow=&HFFFF&:%vbBlue=&HFF0000
%vbMagenta=&HFF00FF:%vbCyan=&HFFFF00:%vbWhite=&HFFFFFF:%vbGrey=&HC0C0C0:%LIGHT_SHADE=&HE0E0E0

FUNCTION PBMAIN () AS LONG 'fredrick10.bas

 slOpen "meganetERPDB.db3","C"
 PrintReport
END FUNCTION

FUNCTION PrintReport() AS LONG

  LOCAL PageHeightMaximum AS SINGLE
  LOCAL iHandle,EndCode,TheBin,TheStyle,DefaultZoom,NoDialog,x,pagenum,fontsize,fontstyle,defaultcolor AS LONG
  LOCAL zOutputFile,zDocTitle,zDefaultFont AS ASCIIZ * 64
  LOCAL lineheight,LeftMargin AS SINGLE  'height deleted
  LOCAL AssetsHeight AS SINGLE
  LOCAL EquityHeight AS SINGLE
  LOCAL RevenueHeight AS SINGLE
  LOCAL ExpensesHeight AS SINGLE
  LOCAL NetProfitHeight AS SINGLE
  LOCAL szExpensesAccount AS ASCIIZ * 200
  LOCAL szExpensesAmount AS ASCIIZ * 100
  LOCAL szRevenueAccount AS ASCIIZ * 200
  LOCAL szRevenueAmount AS ASCIIZ * 100
  LOCAL szEquityAccount AS ASCIIZ * 200
  LOCAL szEquityAmount AS ASCIIZ * 100
  LOCAL szAssetsAccount AS ASCIIZ * 200
  LOCAL szAssetsAmount AS ASCIIZ * 100
  LOCAL sName,sAddress, sFrom, sTo AS STRING
  LOCAL dTotalRevenue, dTotalExpenses, dNetProfit, dTotalCredit, dTotalDebit AS DOUBLE

  sName="Green line":sAddress = "Red line"
  sFrom = "01-01-2019"
  sTo   = "28-05-2019"


  PageHeightMaximum = 10.1  'inches

  defaultzoom = %DDOC_ZOOM75
  zdefaultFont = "Courier New"
  TheStyle = %ddoc_Portrait
  fontsize = 8
  TheBin = %DDOC_Bin_Auto '7
  'topmargin= 1.6    'not in use
  lineheight = .1666 '1/6  'each line height
  LeftMargin = 0.5



  zdoctitle = "Mega-Net ERP - General Ledger Summary Report"
  DefaultZoom=%DDOC_ZOOMFIT + %DDOC_VIEWBUILD
  'DefaultZoom=%DDOC_ZOOM75  + %DDOC_VIEWBUILD
  'DefaultZoom=%DDOC_ZOOM100 + %DDOC_VIEWBUILD

  ihandle = dpStartDoc(0,_
                       zDocTitle, _
                       zoutputfile,_
                       %DDOC_INCH,_
                       %DDOC_PAPER_A4,_
                       %DDOC_SYSTEM_DEFAULT, _
                       TheBin,_
                       DefaultZoom&)
  IF ihandle < 1 THEN
    SELECT CASE ihandle
      CASE %DDOC_NOHANDLES  : ? "no file handles",,"dpStartDoc"
      CASE %DDOC_BADFILE    : ? "bad file name",,  "dpStartDoc"
      CASE %DDOC_FILEEXISTS
        ?  zoutputfile + " already exists so will delete document",,"dpStartDoc"
        KILL zoutputfile
        IF ERR THEN ? "Could not delete file",,EXE.NAME$
      CASE ELSE:      ?  "Error code" + STR$(iHandle),,"dpStartDoc"
    END SELECT
    EXIT FUNCTION
  END IF


  GOSUB PrintHeading

  'Revenue
  dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Courier"
   slSel "Select AccountName, AccountType, Date, SUM(Credit) as CreditSUM From tblJournalEntry " & _
   "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Revenue' GROUP BY AccountName"
    DO WHILE slGetRow()
      szRevenueAccount = slFN("AccountName")
      szRevenueAmount = FORMAT$(VAL(slFN("CreditSUM")), "#,.00")
    GOSUB PrintRevenueDetailLine
    'IF height => PageHeightMaximum THEN
    IF RevenueHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Arial"
    END IF
  LOOP
  SLEEP 200

  'Expenses
  dpFont ihandle, %DDOC_FONTNORMAL, 9, %vbBlack, "Courier"
  slSel "Select AccountName, AccountType, Date, SUM(Debit) as DebitSUM From tblJournalEntry " & _
  "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Expenses' GROUP BY AccountName"
  DO WHILE slGetRow()
      szExpensesAccount =  slFN("AccountName")
      szExpensesAmount = FORMAT$(VAL(slFN("DebitSUM")), "#,.00")
    GOSUB PrintExpensesDetailLine
    'IF height => PageHeightMaximum THEN
    IF ExpensesHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
    END IF
 LOOP
  SLEEP 200

  'Assets
  dpFont ihandle, %DDOC_FONTNORMAL, 5, %vbBlack, "Courier"
  LOCAL s AS STRING
   slSel "Select  AccountName, AccountType, Date, SUM(Debit) as DebitSUM From tblJournalEntry " & _
  " WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Assets' GROUP BY AccountName"
  DO WHILE slGetRow()
      szAssetsAccount = slFN("AccountName")
      szAssetsAmount = FORMAT$(VAL(slFN("DebitSUM")), "#,.00")
      GOSUB PrintAssetsDetailLine
    'IF height => PageHeightMaximum THEN
    IF AssetsHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
    END IF
  LOOP
  SLEEP 200

  'Equity
  dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Courier"
   slSel "Select AccountName, AccountType, Date, SUM(Credit) as CreditSUM From tblJournalEntry " & _
   "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Equity' GROUP BY AccountName"
    DO WHILE slGetRow()
      szEquityAccount = slFN("AccountName")
      szEquityAmount = FORMAT$(VAL(slFN("CreditSUM")), "#,.00")
    GOSUB PrintEquityDetailLine
    'IF height => PageHeightMaximum THEN
    IF RevenueHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Arial"
    END IF
  LOOP
  SLEEP 200

  'Total Credit
  slSel "Select AccountName, AccountType, Date, SUM(Credit) as TotalCredit From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "'"
  IF ISTRUE slGetRow() THEN
     dTotalCredit = VAL(slFN("TotalCredit"))
  END IF
  slCloseSet

  'Total Debit
  slSel "Select AccountName, AccountType, Date, SUM(Debit) as TotalDebit From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "'"
  IF ISTRUE slGetRow() THEN
     dTotalDebit = VAL(slFN("TotalDebit"))
  END IF
  slCloseSet

  GOSUB PrintTotalsDetailLine

  'Revenue
  slSel "Select AccountName, AccountType, Date, SUM(Credit) as TotalRevenue From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Revenue'"
  IF ISTRUE slGetRow() THEN
     dTotalRevenue = VAL(slFN("TotalRevenue"))
  END IF
  slCloseSet
  'GOSUB PrintHeading

  'Expenses
  slSel "Select AccountName, AccountType, Date, SUM(Debit) as TotalExpenses From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Expenses'"
  IF ISTRUE slGetRow() THEN
     dTotalExpenses = VAL(slFN("TotalExpenses"))
  END IF
  slCloseSet
  'GOSUB PrintHeading

  dNetProfit = dTotalRevenue - dTotalExpenses

  GOSUB PrintNetProfitDetailLine

  endCode = %DDOC_END_VIEW + %DDOC_END_DELETE
  dpEndDoc iHandle, EndCode

EXIT FUNCTION

PrintHeading:
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 13, %vbGreen, "Arial"
   dpText ihandle, 4.25, 1.1, %DDOC_CENTER, "" & sName
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
   dpText IHandle, 4.25, 1.3, %DDOC_CENTER, "" & sAddress
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
   dpText IHandle, 4.25, 1.5, %DDOC_CENTER, "General Ledger Summary Report"
   dpText IHandle, 4.25, 1.8, %DDOC_CENTER, "Date Range: " & sFrom  & " To  " & sTo
   dpLine IHandle, .5, 10.3, 8, 10.3, 1, %vbBlack
   'dpText IHandle, .5, 10.6, %DDOC_LEFT, "Total Debit: " & FORMAT$(dTotalDebit, "#,.00")
   'dpText IHandle, 3.5, 10.6, %DDOC_LEFT, "Total Credit: " & FORMAT$(dTotalCredit, "#,.00")
   dpLine IHandle, .5, 11.0, 8, 11.0, 1, %vbBlack
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 11, %vbBlack, "Arial"
   RevenueHeight = 2.5
   dpText IHandle, .5, RevenueHeight, %DDOC_LEFT, "Revenue: "
   ExpensesHeight = 3.5
   dpText IHandle, .5, ExpensesHeight, %DDOC_LEFT, "Expenses: "
   NetProfitHeight = 6.5
   dpText IHandle, .5, NetProfitHeight, %DDOC_LEFT, "Net Profit: "
   AssetsHeight = 7.5
   dpText IHandle, .5, AssetsHeight, %DDOC_LEFT, "Assets: "
   EquityHeight = 9.0
   dpText IHandle, .5, EquityHeight, %DDOC_LEFT, "Equity: "
   RevenueHeight+= lineheight +.05 'looks a bit better
   ExpensesHeight+= lineheight +.05 'looks a bit better
   AssetsHeight+= lineheight +.05 'looks a bit better
   EquityHeight+= lineheight +.05 'looks a bit better
   'NetProfitHeight+= lineheight +.05 'looks a bit better
RETURN

PrintRevenueDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, RevenueHeight,%ddoc_left,szRevenueAccount
  dpText iHandle,7.0, RevenueHeight,%DDOC_DECIMAL,szRevenueAmount
  RevenueHeight+= lineheight
RETURN

PrintExpensesDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, ExpensesHeight,%ddoc_left,szExpensesAccount
  dpText iHandle,5.0, ExpensesHeight,%ddoc_decimal,szExpensesAmount
  ExpensesHeight+= lineheight
RETURN

PrintNetProfitDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,4.5, NetProfitHeight,%ddoc_left,FORMAT$(dNetProfit, "#,.00")
  NetProfitHeight+= lineheight
RETURN

PrintAssetsDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, AssetsHeight,%ddoc_left, szAssetsAccount
  dpText iHandle,5.0, AssetsHeight,%DDOC_DECIMAL, szAssetsAmount
  AssetsHeight+= lineheight
RETURN

PrintEquityDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, EquityHeight,%ddoc_left, szEquityAccount
  dpText iHandle,7.0, EquityHeight,%DDOC_DECIMAL, szEquityAmount
  EquityHeight+= lineheight
RETURN

PrintTotalsDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  'dpText iHandle,LeftMargin, AssetsHeight,%ddoc_left,szAssets
  dpText IHandle, 0.5, 10.6, %DDOC_LEFT, "Total: "
  dpText IHandle, 5.0, 10.6, %DDOC_DECIMAL, FORMAT$(dTotalDebit, "#,.00") & " Dr"
  'dpText IHandle, 5.7, 10.6, %DDOC_LEFT, "Total Credit: "
  dpText IHandle, 7.0, 10.6, %DDOC_DECIMAL, FORMAT$(dTotalCredit, "#,.00") & " Cr"

  AssetsHeight+= lineheight
RETURN
END FUNCTION

FUNCTION mySQLiteDate(sDate AS STRING) AS STRING
     'From dd-MM-yyyy To yyyy-MM-dd

     FUNCTION = MID$(sDate,7,4) & "-" & MID$(sDate,4,2) & "-" & MID$(sDate,1,2)

END FUNCTION

18
Never mind. I think I know what to do.
19
Hello CJ,

>> %DDOC_DECIMAL

I must overlooked this. Thanks for bringing it to my notice. As I mentioned to you earlier I am making great progress in the report using post #7 as the base.
20
Aligning non-fixed length fonts is in the ddoc demo in Fancy text.
The TestP32.Bas demo needs a slight change in WinMain to compile with PbWin10.

BYVAL lpCmdLine    AS ASCIIZ PTR, _

Here is the line that should do what you want:
dpText hFile%, 4.25, 4.15, %DDOC_DECIMAL, "Finally, decimal aligned  $9.99"
Pages 1 2 3 4 ... 10