Report Writer and the Word Templates in Microsoft Dynamics GP: Just Say No

Sad cat

If you installed KB 5000822 for Windows Server, one of the things that was affected was the reporting in Dynamics GP.

Again.

This is going to continue to happen in the future with both Windows and Office updates. The fix for now? Uninstall the updates.

No. No, no, no, no, no. That’s not a fix.

Instead of rolling the dice with future updates (and removing security updates!), why not futureproof your reporting by using SQL Server Reporting Services (SSRS) — for BOTH internal reporting and the customer/vendor facing forms.

Some of the built-in reports you’re never going to replace or modify — especially on the financial side: trial balances, GL account detail reports, checkbook registers. These will be just fine.

But for the modified reports and ALL of the forms, using SSRS means these reports just always work. No holding your breath during GP upgrades with the modified forms and reports, no strangeness with the Word templates, no surprises after Patch Tuesdays.

Added bonus #1: since the data to load the report comes from a SQL view or stored procedure, you have easy access to all of the data in Dynamics GP by just adding tables or using an existing stored procedure/creating a new stored procedure.

Added bonus #2: you can schedule any report to email automatically — both internally and to your customers/vendors — and it is really, REALLY easy to set up. Remember when we could email customer forms through Business Portal many, many years ago?!

Here is what you need:

– A SQL view or stored procedure
– A report template in Visual Studio
– An SSRS server
Dynamics Report Manager from Rockton Software (there’s others, but this is what I use and recommend)

Step 1: SQL View or Stored Procedure

Dynamics GP SQL query

This is the easiest way to collect all of the data you need. You can use the built-in views to start or make your own!

I’ve created my own views to use for the forms and other reporting (SSRS reports, SmartLists) that combine everything together:

– All of the data the forms and reports need.
– Extra tables/views for all of the custom data captured and third-party products.
– The out-of-the-box views in the GP company database (for the drilldown links).

These master views are used for everything. I will be including them in the next blog post.

Step 2: Report Template in Visual Studio

Dynamics GP form template using Visual Studio 2015

Using Visual Studio 2015, you can create or edit existing templates. Use the SSRS reports that come with as a start — it is WAY easier than starting from scratch.

In the next blog post, I will include all of the customer and vendor forms I’ve created/modified/updated for all these years. These are all based on SSRS SOP forms Jennifer Ranz created when she worked for Microsoft and presented at a session at Convergence (THAT was a long time ago!)

WARNING: These are extremely customized, so they aren’t going to work right out of the box. You’ll need to do some modifications for the data you need. They cover all of the major modules within GP: Payables Management, Receivables Management, Sales Order Processing, Returns Management in Field Service. Some of the forms use the tables from Rockton Software’s Auditor, EthoTech’s Commission Plan and the views from eOne’s Extender.

Step 3: SSRS Server

SQL Server Reporting Services

You should already have this running, especially if you deployed the out-of-the-box SSRS reports for GP. If you don’t, there are a number of great articles out there on how to install and configure it. Best of all, it comes with your SQL Server licensing! YAY!

Step 4: Dynamics Report Manager from Rockton Software

Rockton Software Dynamics Report Manager

I have been using this add-on for years and it just gets better and better. You can:

– Automatically close the Report Writer report when it pops up on the screen and load the SSRS replacement.
– Create pick lists of many reports to run at once.
– Schedule any report to email automatically WITHOUT WORRYING ABOUT MFA.
– Create items in the Additional menu of any screen to run a report on-demand.

Since you can essentially replace ANY report with an SSRS version of the same report OR create your own SSRS reports linked to ANY screen in GP, this will pay for itself in no time at all.

It’s a big lift and a lot of work, but can (and should) be done! Short term pain, long term gain.

Part 2 will focus on replacing Report Writer reports and Word Templates. Click to read it!

Part 3 will show some examples of adding reporting to windows in GP.

Part 4 will show the automatic emailing capabilities.

Let’s do this!

Leave a Comment

Your email address will not be published.