How To Realign Trial Balance Summary Modified Report Columns

I 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;

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

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;

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

To realign the columns, select the Static Text tool in the 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;

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;

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;

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;

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

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]