My last post was about integrations and what to consider for that in terms of testing and planning. Today's post is about reporting. The previous posts are linked below for what I've covered thus far in the series!

Posts in the series

  1. Series intro
  2. Process overview
  3. Know your environment (part 1)
  4. Know your environment (part 2)
  5. Know your environment (part 3)
  6. Know your environment (part 4)

Types of reporting

In my "part 3" of knowing your environment, I talked at a high level about types of reporting tools that could be in the environment. Those were:

  • Financial Reporting and/or Budgeting/Planning
  • General reporting tools
  • Business Intelligence tools

I will add the out-of-the-box Report Writer tool to this list too, since there will be reports to test/consider in the reports dictionaries.

If the reporting tool(s) used are specifically integrated with Dynamics GP, which is often the case for Financial Reporting and some Budgeting/Planning tools, these applications need to be installed in the test environment for testing, and the reports themselves may need updating. By "specifically integrated", I'm referring to applications like Management Reporter which are connected at an application level to Dynamics GP. Individual reports may not need to be "repointed" to a new server or database, but depending on the old and new version of GP, changes may be needed to the reports themselves if the underlying data changes.

Otherwise, in my experience, most other types of reports may have something that needs updating (i.e., the connection to the data). Permissions to database objects may need to be re-granted if not using a broad-brush approach such as giving everyone "db_datareader" permission.

My typical approach is to identify the tools used first - list the applications involved. Next, identify the reports in those tools. This can be tough, but starting with where the tool(s) are installed may help narrow things down (i.e., find the authors of the reports to start with). Common reporting tools outside of Report Writer (the built-in GP reporting tool for things like posting reports) are SSRS (SQL Reports), Crystal Reports, Power BI, and Tableau to name a few.

When I review a customer’s SQL server, I also look for logins unrelated to the GP users themselves, some of which may indicate external reporting of some sort. If there are Active Directory users or groups in there that don't make sense, that could be for access to reporting using Windows-based authentication. If there are random SQL logins, there could be a login that is hard-coded into connection strings on reports or a login that users use to authenticate.

Here are some general considerations, but not all of these things apply to all types of reporting tools. Many of my suggestions refer to scenarios where there will be a new SQL server after the upgrade.

  • Reports contain connection strings or queries or database/server settings. For testing, that means updating a copy of the reports to point to the new location and possibly at go-live, redoing the same thing again (or saving the copies used in testing, if the upgrade test server is the new production server).
  • Reports can contain custom code in addition to the connections to the database which, depending on the code, may have additional database connections. Excel reports may have VBA code with connection strings. SSRS reports can utilize code behind the scenes as well.
  • Reports may need to be redeployed with revised settings. Tools like SSRS may require editing where they were authored, such as Report Builder, SSDT or Visual Studio. Some changes may be able to be made on the SSRS site itself, but some may not.
  • Watch for ISV products within Dynamics GP that point to external reporting tools. There are ISV products that can be used to replace an out-of-the-box report like an invoice or PO with a "nicer looking" one built in SSRS or Crystal (Liaison Software and GP Reports Viewer are two such ISV products I’m aware of). That functionality may mean there is a pathname or URL associated with that in the configuration somewhere that may need updating.
  • If SSRS or Excel Reports have been deployed from within Dynamics GP, if they have not been modified since deploying them, redeploying after the upgrade may be the best option instead of moving or copying to a new server. Those that have been modified may need to be remodified or deployed separately.
  • There are many possibilities for custom reports or custom "refreshable Excel" workbooks which I haven't even mentioned. Start with users, asking them what reports they use regularly, if any of them are "refreshable" etc. Find the people in the organization that author or create reports to help determine where the reports are and what needs to be upgraded.
  • I'm not familiar with Tableau but if utilizing Power BI and have published reports to the service and have scheduled refresh, that implies a data gateway is installed somewhere that is pointing at the data too, which may need repointing.
  • Some organizations have a centralized data warehouse instead of the reports and tools pointing directly at the GP SQL server. In those instances, how the data warehouse is refreshed needs to be looked at to update connections in SSIS packages or however, it's obtaining its data.
  • Make sure the users know where the reports are that need to be tested, and if the software, URL or reports they use are moved or in different places for the upgrade. Users often have bookmarked things like that and may, by default, use their bookmarks and think they are testing the “upgraded” report. One way to mitigate this is to encourage users to look specifically for their test data in report testing, since if they can’t find that data, then they may be using the wrong report.
  • For Report Writer and testing modified reports, this is a great time to review *if* the report was modified at all and if it is needed. Many times I have seen “modified reports” in reports dictionaries that someone created simply by clicking "Modify" in the screen output window only to end up in Report Writer unexpectedly when they have access to Report Writer.

Summary

Reporting is another category of things it’s hard to provide detailed instructions for an upgrade but hopefully, some of the items above help to understand what is in the environment and how to plan for the upgrade.

After the upgrade, once all is working, the easiest way to find out what was missed is to shut down the old SQL server or stop the services. At that point with both reports and integrations, users attempting to use an old link or path, or running a report that wasn't upgraded will know in a hurry vs it "seeming" to work but not pulling new data.

There will be one more "catch-all" to conclude the "Know your environment" part of this series. Stay tuned!