Help & Support – Creating a New Report

Sage 200 Reporting  –  Help:

Help and Support

Creating a New Report

You can start the creation of a new report by taking an existing report and then copy and rename it to enable it to be amended or;

You can start a new report by using the NEW option and select an appropriate report structure or;

You can start a new report by using the NEW option and select the BLANK option that will allow the selection of any database tables to build your own SQL Join structure. This BLANK method is useful if the report is relatively simple and requires few joins to related tables and you understand the relationship between the many Sage 200 tables.

Using the BLANK option will require all run time criteria to be defined as none will be automatically added.  The NEW report structure option will come with predefined criteria  

UNDERSTANDING THE SAGE 200 DATABASE (2015)

Click the link above to download this PDF document.

A useful way of understanding how the database table relate to each other is to use the report designer to open an existing report and then select REPORT – JOINS.

The join diagram shows how the tables are related but this view can also be used to show related table.  Sect a table and then right-click to display the short-cut menu. Choose the ‘Related tables’ option to view a list of table that can be joined to the highlighted table. Note, this feature only relates to the standard Sage 200 tables and will not display any third party tables.  Also, any related table currently within the join structure will not be in the drop down list.

When you switch this view to Raw Text Editor  the SQL query represented by the diagram is displayed to be manually updated. Note: the SQL text, copied below, shows that the SLCustomerAccounts table is the MAIN table from which all other joined tables can be linked.  The STATIC tables are not linked to any other table(s) 

[SLCustomerAccounts]

LEFT OUTER JOIN [SLFinanceCharges] ON SLCustomerAccounts.SLFinanceChargeID = SLFinanceCharges.SLFinanceChargeID INNER JOIN [SLOfficeTypes] ON SLCustomerAccounts.SLAssociatedOfficeTypeID = SLOfficeTypes.SLOfficeTypeID  INNER JOIN [SLCustomerLocations] ON SLCustomerAccounts.SLCustomerAccountID = SLCustomerLocations.SLCustomerAccountID  INNER JOIN [SLCustomerStatements] ON SLCustomerAccounts.SLCustomerAccountID = SLCustomerStatements.SLCustomerAccountID  INNER JOIN [SLPostedCustomerTrans] ON SLCustomerAccounts.SLCustomerAccountID = SLPostedCustomerTrans.SLCustomerAccountID  INNER JOIN [SYSAccountTypes] ON SLCustomerAccounts.SYSAccountTypeID = SYSAccountTypes.SYSAccountTypeID  INNER JOIN [SYSCountryCodes] ON SLCustomerAccounts.SYSCountryCodeID = SYSCountryCodes.SYSCountryCodeID  INNER JOIN [SYSCurrencies] ON SLCustomerAccounts.SYSCurrencyID = SYSCurrencies.SYSCurrencyID  INNER JOIN [SYSPaymentTermsBases] ON SLCustomerAccounts.SYSPaymentTermsBasisID = SYSPaymentTermsBases.SYSPaymentTermsBasisID  LEFT OUTER JOIN [SLCustomerAccounts] AS [SLHeadOffice] ON SLCustomerAccounts.AssociatedHeadOfficeAccountID = SLHeadOffice.SLCustomerAccountID  LEFT OUTER JOIN [SLCustomerLocations] AS [SLHeadOfficeLocation] ON SLHeadOffice.SLCustomerAccountID = SLHeadOfficeLocation.SLCustomerAccountID  INNER JOIN [SYSDocTransmissionMethods] AS [SLStatementTransMethod] ON SLCustomerStatements.SYSDocTransmissionMethodID = SLStatementTransMethod.SYSDocTransmissionMethodID  INNER JOIN [SLAgedTransactions] ON SLPostedCustomerTrans.SLPostedCustomerTranID = SLAgedTransactions.SLPostedCustomerTranID  INNER JOIN [SLAllocationTrans] ON SLPostedCustomerTrans.SLPostedCustomerTranID = SLAllocationTrans.SLPostedCustomerTranID  INNER JOIN [SLRevalAllocationTrans] ON SLPostedCustomerTrans.SLPostedCustomerTranID = SLRevalAllocationTrans.SLPostedCustomerTranID  INNER JOIN [SLAllocationHeaders] ON SLAllocationTrans.SLAllocationHeaderID = SLAllocationHeaders.SLAllocationHeaderID  INNER JOIN [SYSTraderRevalAllocTypes] ON SLRevalAllocationTrans.SYSTraderRevalAllocTypeID = SYSTraderRevalAllocTypes.SYSTraderRevalAllocTypeID  INNER JOIN [SLAllocationTypes] ON SLAllocationHeaders.SLAllocationTypeID = SLAllocationTypes.SLAllocationTypeID

STATIC JOIN [SYSCompanies]

STATIC JOIN [SYSCompanyLocations]

STATIC JOIN [SLSettings]