GL Trial Balance for Excel

This program lets you generate a report of account balances and detailed account activity that can be exported to Microsoft Excel.

The information is exported with correctly formatted numeric fields for figures instead of text. This allows you to perform calculations and apply formulas without needing to convert data.

Exploring

This program can be accessed from various locations in SYSPRO:

  • Program List > SYSPRO Reporting Services > General Ledger

  • Program List > General Ledger > Reports

Report programs

GL Trial Balance by Group

GL Trial Balance

Starting

You restrict operator access to activities within a program using the Operator Maintenance program.

Controls whether an operator can modify the SRS menu in any way.

Controls whether an operator can create or maintain a report form.

Controls whether an operator can create or maintain report themes.

Controls whether an operator can purge archived SRS reports.

This activity, together with the activity SRS View all archived items, controls whether an operator can view archived reports.

  • If neither of these two activities is enabled, then the operator can only view archived reports that they have created.

  • If this activity is enabled and the activity SRS View all archived items is disabled, then the operator can only view archived reports generated by members of the operator group to which the operator belongs.

  • If this activity is disabled and the activity SRS View all archived items is enabled, then this activity has no effect and the operator can view all archived reports.

Controls whether an operator can view archived reports, regardless of whether the SRS View operator group archived items activity is enabled.

Controls whether an operator can create, edit and delete report schedules.

Controls whether an operator can change the priority of reports in the queue (server-side printing).

Controls whether an operator can delete a report in the report queue (server-side printing).

Only reports run by the logged in operator can be deleted.

Controls whether an operator can view all company reports (server-side printing).

Controls whether an operator can purge reports from the report queue of a company (server-side printing).

This enables the Purge Manager button on the report queue toolbar, from where an operator can purge reports by date, operator or report status.

You can restrict operator access to programs by assigning them to groups and applying access control against the group (configured using the Operator Groups program).

  • Template account types are excluded from this report.
  • When exporting a report to Microsoft Excel, there may be an impact on speed because of numerically-formatted fields.

  • The date printed in the footer of each report refers to the system date when the report was generated (i.e. the company or temporary company date is ignored). In a client/server environment, the client system date is used.

Solving

This message is displayed if the e.net Browse doesn't exist for the table name displayed.

Use the e.net Browse Setup program to add the browse for that specific table.

This message appears when you try to process a report but the selected currency hasn't been defined as a valid alternate reporting currency.

Use the GL Alternative Currency Setup program (Program List > General Ledger > Setup to select a valid alternate reporting currency, or capture the required information using the Add button.

This message appears when you can't connect to an instance of the SQL Server Database Engine on a single server. There could be a number of reasons for this:

  • Incorrect SQL server name
  • Incorrect default settings
  • Incorrect firewall settings
  • The SQL Server Database Engine does not have permission to allow remote connections

Ensure that your SQL Server name is configured to match the name of the server instance under the Object Explorer pane of Microsoft SQL Server Management Studio.

  1. Press the Win+R keys to launch the Run dialog.
  2. Type services.msc.

  3. Select OK.

    This launches the Services window.

  4. In the right-hand pane, select the SQL Server (MSSQLSERVER) service from the list (MSSQLSERVER being the name of the SQL Server instance).
  5. Right-click the service and ensure that Start has been selected. The option won't be available if already selected.

To run Services, you need to run this program as an Administrator.

  1. Launch Microsoft SQL Server Configuration Manager.

    Select Yes when prompted with the User Account Control window. This opens the Microsoft SQL Server Configuration Manager window.

  2. In the left-hand pane, select the SQL Native Client 11.0 Configuration (32bit) drop-down.
  3. Select Client Protocols from the dropdown.

  4. Right-click the TCP/IP option and click Enable.

    If already selected, the status of this option displays Enabled.

  5. Select Properties for the TCP/IP option.

  6. Change the value in the Default Port field to 1433.

  7. Select OK.

  1. Launch Microsoft SQL Server Management Studio.
  2. Right-click the server name and select Properties.
  3. From the Server Properties window, select the Connections option.
  4. In the Remote server connections section, enable the Allow remote connections to this server option.
  5. Select OK.

SQL Server instances use port 1433 by default. Ensure that the Windows Firewall exception on the server for TCP ports 1433 is added so that SQL Server will run.

  1. Open Control Panel.
  2. Select Windows Defender Firewall.

  3. Select Allow an app or feature through Windows Defender Firewall.

    This launches the Allowed apps window.

  4. From the Monitoring drop-down, select Firewall.
  5. Check the list for Microsoft SQL Server Management Studio. If it is not listed, select Allow another app.
  6. Navigate to the location where Microsoft SQL Server Management Studio is stored and select OK.
  7. Select OK to close the Allowed apps window.

This message appears when you try to print a report, but haven't yet configured a printer for SYSPRO Reporting Services.

You need to configure your printer requirements:

  1. From the Print Setup screen (SYSPRO Ribbon bar > Setup > Print Setup) select the printer to which you want to print reports.

  2. Optionally, specify any preferences.

  3. Select Apply and exit.

The name of the selected printer is then displayed in the status bar next to the print icon.

User-defined journal source codes are displayed as part of the Detail list view within the GL Query program and are printed on the GL Trial Balance and the GL Trial Balance for Excel report.

PROV indicates that the entry was posted from a provisional journal.

System check numbers are only allocated at the time of printing the checks.

If you didn't select the Force check no setup option (Setup Options > General Ledger Integration > General Ledger Codes) then the ledger journal is created at the time of running the Execute Payment Run option (Payment Cycle Maintenance) which is before system check numbers are allocated (i.e. they won't be printed on the report).

Manual check numbers will have been allocated and will therefore be printed on the report.

For entries posted from a sub-module, the date against the transaction is the date when the transaction was posted to the General Ledger. The sub module transaction date is indicated on the journal report of the relevant sub-module.

This is printed on the GL Journal Report, GL Trial Balance by Group and GL Trial Balance by Group and can be viewed using the GL Query program.

This is printed on the GL Trial Balance, GL Trial Balance by Group and GL Trial Balance for Excel reports and can be viewed using the GL Query program.

The figures in the report exported to Microsoft Excel displays in a text format instead of a numeric format when using the GL Trial Balance and GL Trial Balance by Group programs. Because Microsoft Excel interprets these figures as alphanumeric fields you cannot perform calculations and apply formulas to these fields.

This also applies to figures in the format defined against the Use 1000 separator option in the Numeric formatting drop-down in the Setup Options program (Setup Options > Company > Options).

Therefore, if this option is enabled, the report returns figures as is (e.g. 1,235.55).

The GL Trial Balance for Excel report prints figures in a pure numeric format so that when the report is retrieved into Microsoft Excel, the values can be easily totalled.

Using

  • Accounts Payable check numbers are printed in the Reference column of the report if checks have been allocated.

  • An ***On hold*** entry is displayed in the report if the Account on hold option is enabled against the ledger code in the General Ledger Codes program.

  • Hash characters are displayed in a column where the width is too narrow to display the value. Expand the column to correctly view figures.

Referencing

Field Description

Report sequence

This indicates the sequence in which to generate the report.

Selecting Ledger or Description enabled the G/L code selection field. Selecting Report Index 1 or Report Index 2 enables these selection fields respectively.

Reporting currency

Indicate the currency in which you want to generate the report. The currency selected must be a valid alternate reporting currency.

If no value is supplied then the local currency is used by default.

This field doesn't apply when generating a report for Statistical account types.

Report type

This prints a list of ledger account codes, balances and detailed entries against the account codes for the current year, or any prior year for which detail and summary history is held on file.

This prints a list of ledger account codes and balances for any single period in the current year or prior year for which summary history is held on file.

If this option is selected, then only the List inactive accounts, Print report and group summary pages and the Include account summary totals options are available in the Other Options tab.

Year selection

This indicates whether you want to print the trial balance for the current or previous financial year for the periods selected.

If you selected the Prior Year option then you must specify the year.

Prior year

This lets you provide the previous financial year for which you want to view the trial balance.

This field is only available if the Prior year option is selected in the Year selection.

Group selection

Indicate the ledger groups for which you want to generate the report.

GL code selection

Indicate the ledger codes for which you want to generate the report.

This section only is available if the Report sequence field is defined as Ledger or Description.
Report Index 1

This lets you generate the report according to a selective range of report index 1 codes, as defined for each ledger code in the General Ledger Codes program.

These codes can be used for accumulation purposes (within the Report Writer) and sequencing (within the GL Trial Balance and GL Financial Reporting programs).

For example:

If the ledger code is: 100-AAA-400 you could define your financial index 1 as AAA-400-100 and the financial index 2 as 400-100-AAA where:

  • 100 refers to the branch.
  • AAA refers to the cost center.
  • 400 refers to the expense type.
This section only is available if the Report sequence field is defined as Report Index 1.

Report Index 2

This lets you generate the report according to a selective range of report index 2 codes as defined for each ledger code in the General Ledger Codes program.

These codes can be used for accumulation purposes (within the Report Writer) and sequencing (within the GL Trial Balance and GL Financial Reporting programs).

For example:

If the ledger code is: 100-AAA-400 you could define your financial index 1 as AAA-400-100 and the financial index 2 as 400-100-AAA where:

  • 100 refers to the branch.
  • AAA refers to the cost center.
  • 400 refers to the expense type.
This section is only available if the Report sequence field is defined as Report Index 2.

Account types

This lets you indicate the account types to include in the report.

If you select the Statistical account type, then all other account types are automatically excluded from the report.
GL Analysis

This prints analysis entries for the ledger codes against which GL analysis information was captured. Analysis entries to include in the report can be indicated on the Analysis Options tab.
Field Description
Period selection

This lets you indicate the period selection for which you want to generate the trial balance.

The Period selection doesn't apply to a Summary report.

This generates a trial balance for the current period only.

The Ledger period selection is disabled.

This generates a trial balance for the current ledger year-to-date.

The Ledger period selection is disabled.

This generates a trial balance for a range of periods or a single period defined.

The Ledger period selection is available.
Ledger period

Indicate the start and end of the ledger period for which you want to generate the report. When printing a Summary report, only one ledger period can be defined.
Print interim closing balances

This prints the closing balance for each period within each ledger account.

This option only applies if you are printing a Detail report for the Current Year (as defined on the Report Options tab).
List inactive accounts

Indicate whether you want to print accounts where the opening and closing balance is zero for the periods selected.

If you selected the Detail option at the Report Type field of the Report Options tab ,then the report also lists accounts that have no detail transactions in the periods selected.
Print summary page for current period

If you're generating the trial balance for the current period or the current Year-to-date then this prints an account type breakdown at the end of each group defined against the ledger codes.

The company totals printed on the summary page are always as at the current ledger period.

The forced balance account is excluded from the totals printed on this report.

This option is exclusive to the GL Trial Balance by Group report and won't apply if you select Range at the Period selection field and Current year at the Year Selection field.
New page per ledger code

Indicate whether you want to print each ledger code on a new page.

This only applies when running a Summary report.
Field Description

Analysis number

This indicates the selective range of analysis numbers to return. This number is assigned to the GL analysis entries using the Capture GL Analysis Entries program.

Code 1 to 5

This indicates the selective range of analysis codes you want to return (within each of the five analysis types) on the report.

Select a standard or user-defined theme you want to apply to the report.

You can't define a report theme if the Disable theme selection option is ticked SYSPRO Reporting Services toolbar > Setup > Setup Options.

This enables the Email Options function which lets you define the parameters required for emailing the report.

The Office Automation and Messaging module is not required for emailing documents from SYSPRO Reporting Services.

Selecting this option enables the Export Options function which lets you output the report to a specific format and save it to a specific location.

You can also export the report from the Report Preview screen.

Export formats can be page-based and record-based. Page-based formats typically preserve these properties as closely as possible, while record-based formats prioritize data rather than layout and formatting. Some record-based formats are only data-exchange formats (e.g. Adobe Acrobat PDF).

Depending on the format you select, the layout (e.g. object position, object size and relationships between these attributes and other objects) and formatting (e.g. font style, text color, text alignment, background color, etc) may not be perfectly preserved.

This exports (saves) your report with the current data without modifying the original report.

[Page-based]. The exported documents are intended for printing and redistribution. Acrobat format will export both layout and formatting in a manner that is consistent with how the report looks on the Preview tab.

Acrobat format embeds the TrueType fonts that appear in the document. (Non-TrueType fonts are not supported.)

This export format does not support Microsoft font-linking technology, which is used to provide support for some extended character sets such as Chinese HKCS. Therefore, the fonts used in the report must contain all of the required glyphs.

The following URL types are supported for hyperlinks: http:, https: and mailto:.

[Page-based]. You would typically select this if you wanted to create a report that wanted to publish on a website or email to a recipient. An HTML page containing the entire report is generated in the directory you specify. Once published or emailed, the report can be viewed without requiring SYSPRO programs.

This format preserves the layout and formatting of the report by using DHTML.

All the images in the report are saved externally and a hyperlink is inserted in the exported HTML output. Therefore, this export format can generate more than one file in the output.

[Page-based]. This converts your report contents into Excel cells on a page-by-page basis. Contents from multiple pages are exported to the same Excel worksheet.

The export program creates multiple worksheets to accommodate data if a worksheet becomes full and there is more data to export.

If a report object covers more than one cell, then the export program merges cells to represent a report object.

Microsoft Excel has a limit of 256 columns in a worksheet, so any report object (or part thereof) that is added to cells beyond 256 columns is not exported.

This export format retains most of the formatting, but it doesn't export line and box objects from your report.

The Export page header and page footers option lets you include the header and page footer information when exporting the report. Options include: None, Once per report or On each page.

The exported file contains text and drawing objects to represent report objects. Individual objects are placed in text frames.

This format is intended for use in applications such as fill-out forms, where the space for entering text is reserved as empty text objects.

Almost all of the formatting is retained in this export format. However, text objects can't be placed outside the left edge of the page (e.g. if you have text objects in your report that are placed before the left edge of the printable area, they will be pushed right - most noticeable in reports with wide pages).

This exports the report to a plain text file. For client-side reporting, you can specify the characters per inch and number of lines per page.

This is the location (full path and file name) to which you want to save the report.

The file name can't contain characters that are reserved in XML (e.g. ' : & ' > <).

This lets you define parameters for printing the report to a specific printer or PDF printer.

You can define the default paper size to use for your specific reports using the Default printing paper size option (SYSPRO Reporting Services toolbar > Setup > Setup Options).

This displays the report on the screen after processing all output options selected.

This option is selected by default if the Report Preview option is enabled from the Ribbon bar (SYSPRO Ribbon bar > Setup > Report Preview).

This option has no effect when using server side printing. Once the report is requested by the client, it is sent directly to the server for processing which enables the client to continue working or even to shut down. When a report has been processed, it must be opened from the SRS Report Queue.

Saves your selections for future runs of the report.

This field can be controlled using the Always archive report and Disable archive report options (SYSPRO Reporting Services toolbar > Setup > Setup Options).

Archiving reports lets you identify which reports were run, when they were run and by whom. This is also useful for customized SRS reports. In addition, reports are readily available for printing, reprinting or emailing.

This resets all the options on all the tab pages to the SYSPRO default selections.

This generates the report according to your selections.

At least one output method must be selected on the Output Options tab before you can select to process the report.

This emails the report.

This halts processing of the current report.

This exports the report to an Excel spreadsheet.

Option Description
Export to Excel options  

Constant column width

This fixes the width for all exported columns. If selected, enter the required value into the Constant column width field.

Column width based on objects in the

Select the part of the report on which you want to base the column width.

Export page header and page footers

This lets you include the header and page footer information when exporting the report.

Options include: None, Once per report or On each page.

This effectively lets you include details of the operator who created the report.

Create page breaks for each page

This adds the page breaks as designed in Crystal Reports. Don't select this option to add page breaks automatically in Excel.

Convert date values to string

This keeps the date formatting of the report in Excel.

Show gridlines

This adds grid lines to the report in Excel.

Page range

Select the pages you want to export.
Option Description
Export to Excel options  

Constant column width

This fixes the width for all exported columns. If selected, enter the required value into the Constant column width field.

Column width based on objects in the

Select the part of the report on which you want to base the column width.

Options

 

Export object formatting

This exports the report formatting to Excel (i.e. ensuring that the data types are maintained).

Export images

This exports images in the report to Excel.

Use worksheet functions for summaries

This converts report summaries to Excel functions (i.e. numbers with formulas).

Maintain relative object position

This keeps the objects in the report in the same position when exported to Excel.

Maintain column alignment

This keeps the alignment the same as the report.

Export page header and footer

This lets you include the header and page footer information when exporting the report.

This effectively lets you include details of the operator who created the report.

Simplify page headers

This exports only the last row of the page header section.

Show group outlines

This adds the lines that separate the report groups in Excel.

This exports the contents of the report as a text file. When you select this option, the report contents are displayed in a Notepad editor.

This applies a selection formula to the report to filter the data without re-running the report, making the report easier to read by only displaying certain records.

This option is not available for archived and scheduled reports.

Option Description

Record

Specify criteria for displaying records in the report.

Group

Specify criteria for displaying group data in the report.

Saved Data

This displays the report with the saved data. This applies to reports previously exported or reports which you designed and against which data was saved using the Save data report option.

Any changes made to the database after the report is saved doesn't affect the report (i.e. the program does not refer to the database when generating the report).

Clear all selection formulas

This clears any previously specified selection formulas (using the Select Expert option) for the current run of the program.

This queries the database to redisplay the report on the screen at the selected page.

This only applies when generating a report that extracts data directly from a database (i.e. not when using a business object to extract the data).

This exports the report (as well as sub-reports) to a comma-separated-values (CSV) file.

Each record in the file is typically a single line and each field in the record is delimited by a specific delimiter (e.g. a comma).

Option Description

CSV export options

 

Delimiter

Indicate the character you want to use to wrap around each field in the record. This defaults to " (double quotes).

Separator

Indicate the character you want to use to separate the fields or columns in a record. This defaults to , (comma).

Tab

Select this to use a Tab as the delimiter for separating the fields in each record. When you select this option, the Separator field is disabled.

Mode

Indicates what to do with the page and group sections of the report when exported.

Exports the report to the file to resemble the rpt format of the report.

Path

Indicate the location to which you want to export the report.

Report sections

 

Report and page sections

Select whether you want to write all report headers and footers and page headers and footers to the export file.

Isolate report or page sections

This writes all report headers and footers and page headers and footers to the export file, but isolates them in the file.

This creates a bigger file which resembles the rpt version of the report where everything is more spaced out and easier to read.

Otherwise the report and page headers and footers are included in each record of the file.

Group sections

 

Group sections

Select whether you want to write all group headers and footers to the export file.

Isolate group sections

This writes all group headers and footers to the export file, but isolates them in the file.

This creates a bigger file which resembles the rpt version of the report where everything is more spaced out and easier to read.

Otherwise the group headers and footers are included in each record of the file.

This exports the report to a specific format, location and enables you to specify the page ranges to export where applicable.

This saves the report to a specific location. This defaults to Crystal Reports (*.rpt).

When you export a single document, the default file name is created using the document type and the key field (e.g. customer number, invoice number, quotation number, purchase order number, etc). When you export a batch of documents, the last document number or key field is used as a reference in the file name.

This option lets you export reports individually (i.e. only the main report, or any of the sub-reports you are current displaying).

This outputs the report to a printer.

This lets you copy highlighted text to the clipboard.

This lets you view the parameters and parameter values entered for the report using the Crystal Report Designer.

This displays the values of the primary key sequence used in grouped reports (e.g. Inventory Valuation) in a tree view.

This lets you locate text within the report.

This lets you change the size of the report displayed on the screen. It has no effect on the printed size of the report.

This lets you close the currently displayed tab and is only applicable to reports that contain sub-reports (this doesn't apply to the Preview tab).