Reportman Writing SQL

Parent Previous Next

To write SQL is actually very simple, for example, “select data from table”,  where data is a valid field name (e.g. ScompanyName Company Name) in the table and table is a valid table name (e.g. Sysvars).

To call the company name and Fax number (as entered in the Setup → Company info screen (it is stored in the sysvars table)), you may simply write select SCompanyName CompanyName, SFaxNumber Fax from Sysvars.  

Select YOUR DATA FIELDS  from sysvars

An example of the SQL for the COMPANYINFO dataset is as follows:

SQL Text

Data on Screens


SCompanyName CompanyName,

BlobLogo Logo,

SAddress1 Address1,

SAddress2 Address2,

SAddress3 Address3,

SPostCode Postcode,

SPhoneNumber Telephone,

SFaxNumber Fax,

SEmailAddress EMailAddress,

SCompanyRegNo CompanyRegNo,

STaxRegNo TaxRegNo,











SInvoiceHeading InvoceHead,

SInvoicesMessage1 InvoiceMessage1,

SInvoicesMessage2 InvoiceMessage2,

SInvoicesMessage3 InvoiceMessage3,

SCreditNoteHeading CreditNoteHead,

SCreditNoteMessage1 CreditNoteMessage1,

SCreditNoteMessage2 CreditNoteMessage2,

SCreditNoteMessage3 CreditNoteMessage3,

SQuoteHeading QuoteHead,

SQuoteMessage1 QuoteMessage1,

SQuoteMessage2 QuoteMessage2,

SQuoteMessage3 QuoteMessage3,

SPurchaseHeading PurchaseHead,

SPurchasesMessage1 PurchaseMessage1,

SPurchasesMessage2 PurchaseMessage2,

SPurchasesMessage3 PurchaseMessage3,

SGoodsReturnedHeading SupplierReturnHead,

SGoodsReturnedMessage1 SupplierReturnMessage1,

SGoodsReturnedMessage2 SupplierReturnMessage2,

SGoodsReturnedMessage3 SupplierReturnMessage3,

SOrderHeading OrderHead,

SOrderMessage1 OrderMessage1,

SOrderMessage2 OrderMessage2,

SOrderMessage3 OrderMessage3,

SStatementMessage1 StatementMessage1,

SStatementMessage2 StatementMessage2,

SStatementMessage3 StatementMessage3,

SSellingPriceName1 SellingPrice1,

SSellingPriceName2 SellingPrice2,

SSellingPriceName3 SellingPrice3,

SAccountReportName1 LedgerRepGroup1,

SAccountReportName1 LedgerRepGroup2,

SDebtorReportName1 DebtorRepGroup1,

SDebtorReportName2 DebtorRepGroup2,

SCreditorReportName1 CreditorRepGroup1,

SCreditorReportName2 CreditorRepGroup2,

SStockReportName1 StockRepGroup1,

SStockReportName1  StockRepGroup2,

WRetainedIncomeID RetainedIncomeAccId,

WDebtorsControlID DebtorsControlAccId,

WCreditorsControlID CreditorControlAccId

from sysvars

Setup Company info

Setup Documents (Invoices)

Setup Documents (Credit notes)

Setup Documents (Quotes)

Setup Documents (Purchases)

Setup Documents (Supplier returns)

Setup Documents (Orders)

Setup Statements

Setup Stock information

Setup Groups

Control accounts linked to the books (default per Set of Books template selected or specified in the Create Set of Books (Advanced) option on the creation wizard.

In older versions of TurboCASH4 (before version Update) you will receive an error message “No available data to print” when you click on the Preview icon on the icon toolbar of the Report manager. To enable you to preview the report (document layout file) at any time while editing or adding objects to your report, you need to do the following few steps on the “Database connections and datasets” screen.  

To add a dataset, click on the icon. To create or edit a document in Reportmanager and test the document, you need an existing document number (or create a new invoice in TurboCASH5).

These steps are not necessary in TurboCASH4 3.0.1 or later versions, since TurboCASH5 will automatically find and use the latest Invoice.

1.Click on the icon.

2.On the New dataset screen, enter the alias (name) “Dummy” and click on the OK button.

3.Enter the following SQL text in the memo field:
               select max(Wdocid) from dochead

4.Click on the Show data button.

5.Remember the number displayed on the MAX column. This is the DocumentID number of the last document created in the Set of Books.  

6.Close the data screen.

7.Select the “Dummy” query and click on the to remove it.

8.Click on the Parameters button.

9.On the value field, enter the value of the document number.

10.Click on the OK button to close the Parameter definition screen.

After you have followed these setting and data configurations, you may select the DOCUMENTHEAD or any dataset and click on the Show data button to view the details of the Invoice.

Created with the Personal Edition of HelpNDoc: News and information about help authoring tools and software