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;

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.

Ian Grieve

About Ian Grieve

Ian is a Microsoft Dynamics GP certified consultant specialising in the delivery of Microsoft Dynamics GP projects and currently working for Perfect Image Ltd., a Microsoft Partner and VAR in the North East of England. Ian has worked with Microsoft Dynamics GP since 2003 and, over the nine years since, has dealt with all aspects of the product life-cycle from presales, to implementation, to technical and functional training, to post go-live support and subsequent upgrades and process reviews. In his spare time, Ian runs the azurecurve | Ramblings of a Dynamics GP Consultant blog dedicated to Microsoft Dynamics GP and related products.
This entry was posted in Dynamics, GP, Microsoft, Report Writer and tagged , , , , , , , , , , . Bookmark the permalink.

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

  1. Pingback: How To Realign Trial Balance Summary Modified … – azurecurve : Interesting Findings & Knowledge Sharing

  2. Pingback: How To Realign Trial Balance Summary Modified Report Columns | azurecurve – 12/3, DynamicAccounting.net | Partner Compete

  3. Pingback: How To Realign Trial Balance Summary Modified Report Columns | azurecurve - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community

Leave a Reply

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

*

* Copy This Password *

* Type Or Paste Password Here *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>