How To Realign Trial Balance Summary Modified Report Columns

Microsoft Dynamics GPI was recently asked to assist the Support team to look into an issue with the Trail Balance Summary modified report. The problem was that when sent to screen or printer the report was fine, but when exported to Tab Delimited file, the columns didn’t match up with the totals at the bottom.

Trial Balance Summary on Screen shows as aligned;

Trial Balance Summary - Screen Output

But the Trial Balance Summary tab delimited file in Excel shows the totals out of alignment with the columns of data;

Trial Balance Summary - Tab Delimited Output

This is actually an easy one to fix in Report Writer. To modify the report, open Report Writer (Microsoft Dynamics GP menu » Tools » Customise » Report Writer) and click the reports button. Scroll down the list of Original Reports and locate the Trial Balance Summary and click Insert >> to move the report into the Modified Reports list;

Microsoft Dynamics GP Report Writer - Reports

Select the new Modified Report and click Open to open the Report Definition window and click Layout;

Report Definition

To realign the columns, select the Static Text tool in the Toolbox;

Report Writer Toolbox

What we need to do is place three pieces of static text into the report. The screenshot below shows the first piece of static text added (identified by the nine blue squares. The static text is actually a single space character which Report Writer will not let you insert by itself (the first character of any static text cannot be a space but you can force this by inserting a non-space character first, then the space, then delete the non-space character.

This will move the Accounts label and every subsequent field one cell to the right in Excel.

The red squares show where the other two blank pieces of static text need to be placed (which can be done with copy and paste) to align all of the other columsn up accurately;

Microsoft Dynamics GP Report Writer - Report Layout - Trial Balance Summary

Once you’ve made the changes, hit the cross in the upper right corner of the Report Layout: Trial Balance Summary window and click Save;

Microsoft Dynamics GP - Do you want to save the changes to this report layout?

Click Ok on the Report Definition window and switch back to Microsoft Dynamics GP (File » Microsoft Dynamics GP).

Newly created Modified Reports are not accessible to users until security access is granted. this is done in the Alternate/Modified Forms and Reports window (Microsoft Dynamics GP menu » Tools » Setup » System » Alternate/Modified Forms and Reports).

Select the relevant ID (in this example I only have one security ID which will need updating) and select the Product of Microsoft Dynamics GP and a Type of Reports. In the list expand Financial » Trial Balance Summary and select Microsoft Dynamics GP (Modified) and click Save;

Alternate/Modified Forms and Reports

Now when the Trial Balance Summary report is produced, the Screen Output will be no different as the static text we added was simply spaces;

Trial Balance Summary - Screen Output

But the tab delimited file in Excel will show the totals aligned correctly with the relevant data;

Trial Balance Summary - Tab Delimited Output

The same principle applies to any of the other reports which are out of alignment, but the only one I was asked to fix was the Trial Balance Summary.

For ease, if you want the Microsoft Dynamics GP 2010 R2 version, you can download it below.

[wpdm_file id=4]

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you’d like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.

Your Name (required):
Your Email (required):
Enquiry message:

What should we write about next?

Your Name (required) –
Your Email (required) –

3 thoughts on “How To Realign Trial Balance Summary Modified Report Columns

Leave a Reply

Your email address will not be published. Required fields are marked *