Report Writer within Microsoft Dynamics GP can be a very powerful tool when modifying standard GP reports. Recently, I came upon a more difficult request which required some research and table linking. The specific request in my case is now included in standard reporting out newer versions of GP.
The issue overall however, is common. We need to add an element of data to a report. The only table that includes the data has a many to one relationship to the source table of the report.
The first step is to locate the proper table that needs to be added to the report. The source tables are listed by clicking on the ‘Tables’ button from within the Report Definition window. Often, we can double click on an existing table and the system will already have a list of Related Tables that you can choose from. When our table is not there, we should manually add it.
In order to create the new relationship, click on the Tables button on the main page of Report Writer. Select the table and then click on Relationships. Select the table you are linking and establish the appropriate primary key links. Within this window, you will find, that you are unable to edit the keys available for the tables. Usually, this is okay as it makes it much easier to link tables under standard circumstances.
In my case, this new link brought in the information I needed, but duplicated other sets of data from within the body of the report. That is when I utilized the table restrictions from within the Report Definition window. While doing my research of which table would be best to link, I determined a primary key that I could establish that would make my results a one to one relationship. From within this Restrictions window, I was able to set up another field to link, which resolved the issue.
These days, Smartlist Designer and other out of the box tools, allow us to write SQL views for ease of reporting. For Report Writer, we sometimes have to be a bit more creative!
To learn more about Microsoft Dynamics GP, contact us at [email protected].