Debugging Microsoft Dynamics GP Word Templates Revisited

Couple days aback, I went through an issue were Microsoft Word templates were being rendered to an html file, causing Internet Explorer to launch instead - see Word Templates rendering report in Internet Explorer. In troubleshooting this problem, I engaged my good friend Rob Wagner over at Microsoft, who showed me a few more tricks for troubleshooting issues around Word Templates and template rendering.

Also, some time ago I covered a troubleshooting tip in my article Debugging Microsoft Dynamics GP 2010 Word Templates. Back then I looked at the TPELogging Dex.ini key, which creates a log file in the temp folder (%temp%) where templates are rendered and loaded by Microsoft Word. TPELogging is used to establish where possible exceptions could have occur while rendering the actual template into a Word document.

While working with Rob, I learned about another Dex.ini key that can assist with the troubleshooting process:

KeepTemplateTempFiles=TRUE

This Dex.ini key allows Dexterity to persists the template in the temp folder once the Microsoft Word document has been created. Now, to see how this is useful, you need to keep in mind a few things about how Word Templates really works in Microsoft Dynamics GP.

When a template is created under the Report Template Maintenance window, the actual template XML representation is saved in the system database, DYNAMICS, in the syReportTemplates table (in a SQL Server binary data type column). This is all good, because in theory, once you create your template and saved it away from the temp folder into, say, My Documents, then reimport that template into Microsoft Dynamics GP, there will never be a chance of you loosing the changes you made even if you delete the physical template document (the DOCX).

With that said, when you print a template 3 things happen - well, to summarize anyways:

1. The Report Writer engine generates an XML data file if the report was setup to use a template.

2. The Word Template code takes over and reads the actual XML template representation in syReportTemplates, and recreates the physical Microsoft Word template document in your local temp folder.

3. Through Microsoft Word and Open XML automation, the physical template is applied to the XML data file to create the final Microsoft Word document, this is, your invoice, purchase order, or any other document you have decided to generate using a template.

When the final document is delivered, the code performs some cleanup by removing the template file (leaving you with the actual output document).

The KeepTemplateTempFiles key allows the Word Templates code to persist the generated template (in step 2 above). Should you run into any issues, you could potentially open the template file to compare against the document you originally worked on.

A couple words of caution...

If you choose to enable KeepTemplateTempFiles, be sure to disable it once you have completed all troubleshooting activities, since each time you print a document to a template, you will obtain a new template file for that print run. Overtime, you can have a very full temp folder should you not set the key value to FALSE. By default, Microsoft Dynamics GP has this key value turned off when it's not found in the Dex.ini file.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Word Templates rendering report in Internet Explorer

I have been working on a Word Templates case for the past 2 weeks and just got to the bottom of the problem, hence I felt the need to pause and blog about it.

Background

A client of mine reported a couple weeks aback that they had created a template version of their purchase order and each time the purchasing staff attempted to print a purchase order, the output would automatically generate in Internet Explorer instead of Microsoft Word. As the title of this article suggests, the client is using Word Templates to generate a nice looking PO for their vendors.

HTML POP Purchase Order Blank Form

However, and to make matters more interesting, when they printed a standard sales invoice, SOP Blank Invoice Form, these would generate just fine in Microsoft Word.

Troubleshooting

In troubleshooting this process, we went through all the formal setup and steps to create a template in Microsoft Dynamics GP, with the added benefit that the client's purchase order form was not a modified report. I also outruled the Word Add-On for Microsoft Dynamics GP being an issue, since the sales invoices were being created just fine.

I made sure the client was not accidentally selecting HTML as the default output. As previously mentioned, when the purchase order was printed, it would automatically go to Internet Explorer. We also selected a File output and the Microsoft Word file would automatically get an html extension following the docx; for example, POP Purchase Order Blank Form.docx.html.

Printing Purchase Order Document

When printing the purchase order, one thing stood out in the printing process... the Report Type drop-down list would automatically default to Standard -- not to Template as would be expected -- and the client would have to manually select Template before clicking Ok to print. Not so, when printing sales invoices.

We also checked the REPORTS.DIC dictionary file, by accessing Report Writer, to ensure the purchase order report was not modified. Effectively, this was not the case. Puzzled by this situation, I called my good friend Rob Wagner over at Microsoft and explained what was going on. In formulating an approach to address the issue, Rob added a key to the Dex.ini - more on this tomorrow - that would allow him to debug what was happening when the template was generated.

To Rob's surprise, adding the Dex.ini key had no effect nor did it produce the debug file he was expecting. Then, the magical question came up... "Does you client have any third party products or add-ons installed that affect the POP module?" and the answer... "Yes, Project Accounting". This is when it all became clear to both of us where this was going.

As it turned out, the client is also using Project Accounting. However, the user entering the POs has no access to the Purchase Order Entry alternate window - hence was seeing the standard Microsoft Dynamics GP Purchase Order Entry window. However, according to security settings, the user had access to the alternate POP Purchase Order Blank Form report.

Access to alternate version of POP Purchase Order Blank Form

When the user prints the PO from the standard Purchase Order Entry window, GP would attempt to print the Project Accounting alternate purchase order report, but could not find a corresponding template.

Rob proceeded to explain that this behavior was by design. In essence, when a template cannot be found for an alternate version of an original Microsoft Dynamics GP report, the Dexterity code behind the Word Template functionality, will automatically render the report to HTML. Since html files are usually tied to Internet Explorer, this would cause that application to launch automatically.

The first tell-tell sign of a problem as described above lies in the Report Destination window defaulting the Report Type drop-down list to Standard, even though a template "exists" for the report being printed. In reality, the template you are seeing in Report Template Maintenance is the core Microsoft Dynamics GP report template and not the template for the alternate dictionary, in this case Project Accounting.

Solution

If using a third party product or add-on that contains an alternate versions of a core Microsoft Dynamics GP report, you will need to ensure that the template that is created corresponds to that dictionary or remove security to the alternate report.

However, and as luck would have it, I could not create a new template based on the existing POP Purchase Order Blank Form in the Microsoft Dynamics GP dictionary, because the two reports use a slighly different data set - the PA alternate report uses additional fields and tables not used by the standard report - but that's a tale for another time. In the mean time, I hope you find this article useful.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Management Reporter 2012

Management Reporter 2012 Release Themes

Most of you know me to be a fairly technical guy, so it must be quite an event when I get excited about a financial reporting product. My excitement stems from a couple of things I saw in an MVP Product Group Interaction session yesterday with the Dynamics Corporate Performance Management team:

• Management Reporter 2012 is simply THE BEST darn financial reporting product produced to date by Microsoft. This release revolves around 4 core themes: Interactive Report Viewing, Deeper Dynamics ERP Integration, Report Collaboration, and Design Flexibility. I highlighted a few of the capabilities in my March 8, 2011 Corporate Performance Management Roadmap article.

• Among the things I can say for now, and from a technical perspective, this release removes its dependency on Internet Information Services (IIS), allowing it to run in native mode. If you are familiar with SQL Server 2008 Reporting Services, you can now begin to imagine how this will improve Management Reporter's reports delivering capabilities and efficiency.

Honestly, I wish I could just spill it all out right now... but that would make for a lot of angry folks over at Microsoft, but you don't have to wait on me. The Dynamics Corporate Performance Management team over at Microsoft already began releasing information about specific features in Management Reporter 2012, and the following are links to what they have posted so far:

• Start Learning how Management Reporter 2012 can make a difference in your business
• Rolling Forecast Headers - Management Reporter 2012 Feature Highlight
• Viewing dimension codes and descriptions - Management Reporter 2012 Feature Highlight
• Report group navigation changes - Management Reporter 2012 Feature Highlight
• Comments - Management Reporter 2012 Feature Overview
• Distributing reports to multiple locations - Management Reporter 2012 Feature Highlight

From what I have seen so far, the Comments feature will make a lot of accounting folks happy, but the ability to distribute reports to multiple locations at once will certainly make a lot of IT folks happy. From me to you... there's not one reason, at least in my mind, why you need to continue your love affair with FRx. The new Management Reporter 2012 blows off the FRx 6.7 hat off by a long shot.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

New Article on MSDynamicsWorld: Where Do the Microsoft Dynamics GP Experts Turn for Answers?

My new article is out over at MSDynamicsWorld! It's been a long time coming, but finally, and per the insistence of Jason Gumpert, I was able to get to it - Jason has his ways for sure :-)

This time around my article, Where Do the Microsoft Dynamics GP Experts Turn for Answers? A Preamble to GPUG DayONE, looks the Microsoft Dynamics GP application's dependency on the overall Microsoft technology stack and guides you on how you can find answers to the growing number of questions arising from this interdependence. In truth, the article serves as an invitation to come see David Musgrave and I talking about this subject at the next GPUG DayONE opening event for Microsoft Dynamics Convergence 2012 in Houston, TX - see Microsoft Dynamics Convergence 2012 sessions now published.

As the saying goes: be there or be square!

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Microsoft Dynamics Convergence 2012 sessions now published

Ok, they have been published for a few days now. However, I wanted to bring your attention to the sessions that David Musgrave and I will be presenting. This time we will present a GPUG DayONE breakout session, UBGP18 - Where do the Experts Turn for Answers, running Sunday, March 18, 2012 from 1:30 PM - 3:00 PM.

We will also have the distinct opportunity to present 2 Support Debugging Tool sessions - twice! That's it, you will have the opportunity to see each of our sessions in a repeat in case you get to miss any the first time around. I still don't have the details on the exact dates and times our sessions will be running yet, but will publish as soon as they become available. For now, here's what we have:

CSGP19

CSGP23

I am really excited about our new sessions showing some really cool advanced Support Debugging Tool sessions. This is an advanced level session, with lots of cool stuff from the new Build 16. You won't be disappointed!

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

1099 reports: the devil is in the details

It's certainly that time of the year and if you haven't sent out those 1099's you are running out of time. I was working on a case recently at one of my clients who was having all sort of issues printing 1099 reports. Apparently, reconciling their calendar year wasn't helping the issue either. It did not really matter what they tried - short of manually adjusting the 1099 amounts for each vendor - nothing seemed to make the correct numbers print on the report.

We reviewed all 10,000 vendor cards to make sure they had the proper 1099 type and box number selected - we caught a few mishaps here too.

After inspecting a few of the problem reports (and vendors), we narrowed down the issue to a number of payable invoices not having the proper 1099 amount and box type associated to the transactions. So to fix this issue, I wrote a few scripts which I hope you find useful.

Fix1099.sql

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

-- Update the 1099 amount, type, and box number on open payable records
UPDATE a SET a.TEN99AMNT = a.DOCAMNT
 , a.TEN99TYPE = b.TEN99TYPE
 , a.TEN99BOXNUMBER = b.TEN99BOXNUMBER
FROM PM20000 a INNER JOIN PM00200 b ON (a.VENDORID = b.VENDORID) 
WHERE (b.TEN99TYPE <> 1) AND (a.DOCTYPE IN (1, 4, 5));

-- Update the 1099 amount, type, and box number on history payable records
UPDATE a SET a.TEN99AMNT = a.DOCAMNT
 , a.TEN99TYPE = b.TEN99TYPE
 , a.TEN99BOXNUMBER = b.TEN99BOXNUMBER
FROM PM30200 a INNER JOIN PM00200 b ON (a.VENDORID = b.VENDORID)
WHERE (b.TEN99TYPE <> 1) AND (a.DOCTYPE IN (1, 4, 5));

-- Update the 1099 amount, type, and box number on history apply records
UPDATE a SET a.TEN99AMNT = a.APPLDAMT
 , a.DEFTEN99TYPE = b.TEN99TYPE
 , a.DEFTEN99BOXNUMBER = b.TEN99BOXNUMBER
FROM PM30300 a INNER JOIN PM00200 b ON (a.VENDORID = b.VENDORID)
WHERE (b.TEN99TYPE <> 1) AND (a.APTODCTY IN (1, 4, 5));

Once the scripts are executed against the company database, you can then proceed to reconcile the 1099 amounts for the current calendar year - Microsoft Dynamics GP | Tools | Utilities | Purchasing | Reconcile. Note that the update is restricted to invoices (1), returns (4), and credit memos (5) document types.

Once we went through all of the above (including making sure the vendor cards were accurate), the reports printed fine, with the correct amounts, and in the correct boxes.

A few things to remember:

1. Execute all of the above procedures in a test environment, prior to executing in production. To create a test company from your production company follow the steps outlined in Microsoft Support KB article 871973.

2. The above steps and scripts specifically solved the issues with my client environment. You may have specifics within your environment that may prevent these scripts from being effective - I can't think of anything, but then again, test!

3. See the disclaimers on the right frame of this blog.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

The Support Debugging Tool on Twitter!

As you know by now - hopefully you do - Build 16 of the Support Debugging Tool (SDT) has been officially released - see Support Debugging Tool Build 16 released over at Developing for Dynamics GP for release notes and new features.

However, you may have noticed I have not posted anything about it (don't worry, I am still friends with Musgrave). The reason for the absence of posts on the new SDT build obays to a few reasons:

1. I am going to release a series of video blogs showing the new features on The Dynamics GP Blogster YouTube channel, and as you can imagine, these things take time.

2. I have been thinking of some creative ways to continue to push the SDT to the Microsoft Dynamics GP community as a whole, not just Build 16.

As it relates to the second reason/excuse/goal, and given the adoption of social media throughout the Microsoft Dynamics GP community - check out Social Media for Microsoft Dynamics Partners eBook by MVP Mark Polino and Jon Rivers (twitter: @jon_rivers) if you still don't get the social media drift - I thought it was now time for the Support Debugging Tool to have it's own Twitter hash tag. So please welcome #MSDYNGPSDT.

If you have any questions, comments, or want to relay any experiences with the Support Debugging Tool on Twitter, please use the #MSDYNGPSDT hash tag. Also, don't miss some really cool upcoming news about the tool, which will be available via Twitter.

Disclaimer: I am not allowed to say anything yet, otherwise I will be unfriended by Musgrave on Facebook and that's worse than him not talking to me in real life.

For more resources on the Support Debugging Tool visit the Support Debugging Tool Portal (http://aka.ms/SDT) over at Developing for Dynamics GP.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Management Reporter: Server Error in Application "MANAGEMENT REPORTER"

Just recently, I fielded a question on an issue that was happening with a Management Reporter installation. The partner reported getting the following error (shown in the picture) when attempting to open the Management Reporter URL address: Server Error in Application "MANAGEMENT REPORTER".

The error also indicated an HTTP Error 403.14 - Forbidden. The Web server is configured to not list the contents of this directory. Typically, when you attempt to navigate to the server URL hosting Management Reporter, at the default port 4712, you can expect to receive a blank page. A blank page simply means that all Management Reporter application services are running just fine.

Scrolling through the error page, the following additional information reveals that a default document is not configured for the requested URL, and directory browsing is not enabled on the server.

In doing some research, I came across Microsoft Support KB article 942062 which explains the issue being related to Directory Browsing and Default Document not being enabled on IIS. The article provide the steps to resolve both.

With the increased reliance on the Microsoft application stack, a lot of errors that on the surface may appear to be Dynamics GP or Management Reporter related, are simply the reflection of a problem with the stack's configuration. When troubleshooting these initial problems it's always recommended to look at the Event Viewer and do some extra research in the Microsoft Support database.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Another reason to attend Microsoft Dynamics Convergence 2012... Daughtry!

If you are a fan of good rock music, then here's another reason for you to attend Microsoft Dynamics Convergence 2012 in Houston, Texas... Daughtry will be live at the Convergence Reception.

Daughtry - from left to right: Robin Diaz (Drums), Josh Steely (Guitar), Chris Daughtry, Josh Paul (Bass), and Brian Craddock (Guitar)

Daughtry is an American rock band formed and fronted by American Idol Season 5 finalist Chris Daughtry in 2006. Their self-titled debut album was released in 2006. The disc reached number one for two non-consecutive weeks on the Billboard Top 200, sold more than four million copies and was named the number one selling album of 2007 by Billboard. Their album is also the fastest-selling debut rock album in Soundscan history. The first single from the album, "It's Not Over", was the eighth most played song across all formats on U.S. radio in 2007, and their second single from the album, "Home", was the tenth most played song in the U.S. of 2007. The band's second album, Leave This Town, was released in July 2009. The first single, "No Surprise," was released on May 5. "No Surprise" was written by Chris Daughtry with Chad Kroeger and Eric Dill. The band's third studio album, Break the Spell was released in November 2011.

On a personal note, I had a chance to see Daughtry opening for Bon Jovi at the Phillips Arena in Atlanta in Spring of 2008 and I have to say, these guys are superb artists and entertainers.

What are you waiting for? Register for Convergence now and don't miss the fun! Also, make sure you brush up on your Daughtry lyrics.

And now I leave you with one of my favorite Daughtry songs, "Over You" - and a question that's been lingering in my mind... how did this guy not win American Idol Season 5?

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Microsoft Dynamics GP Technical Conference 2012 dates announced!

It's official! The Microsoft Dynamics GP Technical Conference 2012 will be held in Fargo, ND from September 12 -14. As you know,  I have been a speaker and presenter at the previous Tech 2011 and Tech 2009 and, while not official yet, I will be looking to present at the this year's Technical Conference as well. The announcement came earlier today via Pam Misialek over at the Inside Microsoft Dynamics GP blog.

Disclaimer: The banner above is not an official Microsoft Tech Conference 2012 banner and was rendered based on the current Fargo Developer Conference site banner

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/