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]

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.