Copying Report Formats between Reports... and a warning about Word Templates

There is an unsupported method of copying report formats between reports that is very commonly used (and even recommended by me), but we recently had a case that highlighted a problem with the technique. This blog post will discuss the method and the issue we have seen.

The Situation

The situation is very common and can have two variants. I will use the Sales Order Processing Reports for my example, here are the scenarios:

1. You have modified the SOP Blank Invoice Form to meet the requirements of your customer (either as the final document or for use with Word Templates). You now want to duplicate the format changes to other document types (Blank, Long, Other, Short, with options, etc.).
    
2. You have modified the SOP Blank Invoice Form to meet the requirements of your customer (either as the final document or for use with Word Templates). You now want to duplicate the format changes to the historical version of the report: SOP Blank History Invoice Form.

Next, we will look at how the report formats can be duplicated. 

The Method

Before we go into details, I must emphasize that this method is not officially supported as it required direct editing of Customization Maintenance packages.

Below are the steps to copy a report format from one report (Source Report) to another report (Target Report):

1. Ensure that both the Source Report and the Target Report have both been inserted into the Modified Reports list (right hand pane) in the Report Writer. The Source Report should already be there, inserting the Target Report makes one of the following steps easier.
    
2. From the Microsoft Dynamics GP application, select Microsoft Dynamics GP >> Tools >> Customize >> Customization Maintenance and export both reports separately as packages. You can locate the files on your Desktop for ease of access.
    
3. Open Notepad.exe and drag and drop the Target Report's package into the Notepad window.
    
4. Then copy the report naming line(s) to your clipboard.  For example:
    
   <Component Name="SOP Blank History Invoice Form" ProductId="0" Object="Report" >
   Report "SOP Blank History Invoice Form"
    
5. Drag and drop the Source Report's package into the Notepad window.
    
6. Select the report naming lines(s) and paste from your clipboard to replace the information.
    
7. Optional Step when changing report from Work to History: Perform the appropriate Find and Replace steps to update the tables used on the report:
    
   Find: SOP_HDR_WORK    Replace with: SOP_HDR_HIST
   Find: SOP_LINE_WORK    Replace with: SOP_LINE_HIST
    
   Tables which are used for Work and History do not need changing, such as SOP_LINE_CMT_WORK_HIST, SOP_Serial_Lot_WORK_HIST, sopUsrDefWorkHist.
    
8. Select File >> Save As and save the updated package file as a new file with the name of the Target Report plus "Updated". Keep the original Target Report's package file as a backup in case you need to restore it.
    
9. Back in Microsoft Dynamics GP, import the updated Target Report package via Customization Maintenance.
    
10. Adjust Alternate Modified Forms and Reports security settings to use the now modified version of the report. 

Note: You must also ensure that the Main Table (as shown on the Report Definition window in Report Writer) is the same for the two reports involved. If the main tables do not match, it is very unlikely that the target report will print successfully.

The Problem 

While the method above works extremely well for the Report Writer reports, we have found that reports updated using this method do not always work with Word Templates. If you go back to the original report, the Word Templates work, but with the modified report the Word Template does not always work. This is often noticed when the Historical versions of the reports fail to email.

Our case had the issue that the SOP Blank Invoice Form would print and email via Word Templates, but the SOP Blank History Invoice Form would print but not email. Research found a blog post by Andrew Hall from Touchstone in the UK (see below) that highlighted the issue.

To understand what is happening you need to understand a little about how Word Templates work. Word Templates are tied into the Section Breaks of a report. When the different sections print, events fire in Dynamics which are picked up by the Word Templates handling code and are used to capture details about the key fields upon which the section breaks are based. The code in Dynamics and the section breaks in the reports match so they both refer to the same field in the same table.

If you start changing the sections, or the fields upon which section breaks are based, the code and section breaks no longer match and Word Template functionality will start to fail.

The problem as highlighted in Andrew's blog post is that the Section Breaks in the Work SOP reports are subtly different from the matching Section Breaks in the History SOP reports. When we used the method above to copy the report format, we unintentionally broke the Word Template Email functionality by changing the Section Breaks.

Below are screenshots of the Dummy1 Header and the Back Order Footer from the Work Invoices that show that the SOP Number field is being captured from the Sales Transaction Work (SOP_HDR_WORK) table.

Dummy1 Header from SOP Work Invoice (SOP Number from SOP_HDR_WORK)

Back Order Footer from SOP Work Invoice (SOP Number from SOP_HDR_WORK)

Below are screenshots of the Dummy1 Header and the Back Order Footer from the original (unmodified) History Invoices that show that the SOP Number field is being captured from the Sales Transaction Amounts History (SOP_LINE_HIST) table, which is not the Sales Transaction History (SOP_HDR_HIST) that came across when we copied the format.

Dummy1 Header from SOP History Invoice (SOP Number from SOP_LINE_HIST)

Back Order Footer from SOP History Invoice (SOP Number from SOP_LINE_HIST)

As the report is not breaking on the correct field in the wrong table, the data passed to the Word Template code is incorrect (blank) and the Email functionality fails to work.

The Solution

The fix for this issue is to edit the Section Breaks for the History Report so that the Dummy1 Header and Back Order Footer use the SOP Number field in the Sales Transaction Amounts History (SOP_LINE_HIST) table again. 

The exact steps are listed in Andrew's Blog:

• Dynamics GP 2010 word templates (more required fields SOP Blank History Invoice)

The Warning 

The lesson learned here is to avoid changing section break tables and fields for reports that are used with Word Templates. If the Word Templates fail to work in any way, test with the original Report Writer report and see if that works. If it does, compare the table and field for each section break with the original report and make sure they match.

A good method would be to never remove fields or change section breaks. You can always hide fields or supress section breaks if you want to make changes. Also keep testing (and saving packages) as you modify the reports to make sure you have not broken anything. If you test as you go along, it will be easier to remember the last changes made so they can be backed out.

More Information

Below are some blog articles which discuss aspects related to this post:

• How to Copy SOP Invoice Formats in Report Writer
   
• Dynamics GP 2010 word templates and required fields to allow emailing or why won’t my modified word template email.
   
• Dynamics GP 2010 word templates (more required fields SOP Blank History Invoice)
   
• SOP Blank History Word Template does not Email

Thanks to Andrew Hall for his troubleshooting on this issue, hopefully I have clarified the reasons and a possible cause in duplicated reports.

David

22-Jul-2013: Add article from Dynamics GP Support and Services Blog.