Posts Tagged ‘Dynamics GP’

Dynamics GP Job Cost

The Dynamics GP Job Cost module allows you to easily link various types of Dynamics GP transactions together so that you can track job or project revenues and costs.

This module is very easy to use and will meet the requirements of a lot of users.

There are various Job Statuses that can be used to manage the jobs. Specific Job Statuses dictate what transaction types can be linked to the jobs. The “Released” status allows the most flexibility, so you may want to use that as you gain some experience with the module.

Here is a table that lists the types of transactions/documents that can be linked to a “Released” job:

Job Cost


This video shows this function in action:


Dynamics Hosting – Interactive Video

Have you moved your ERP system to the cloud yet? Dynamics hosting frees you from many of the headaches associated with running your system on-premise. There are benefits associated with security, scalability, operating costs, and IT operations.

As it turns out, people generally understand some of these benefits but still have some concerns. To help address these concerns we created an interactive video that lets you explore the benefits and issues associated with Dynamics hosting, on your own terms.

We identified three roles that we see most often. These include the following perspectives:

  • IT Director
  • Finance & Accounting
  • Dynamics Partner

Check out this new video. It may change the way you think of Dynamics hosting. Click on the link below and go to, “View Our Interactive Video” with Linda:

Hosting Dynamics


If you want to try out a hosted ERP system for your self you should check out the dynamics hosting trials available for Dynamics AX, NAV, and GP:

Dynamics Hosting Free Trial

Brilliant Insights on Dynamics GP Distribution and Manufacturing

Increase the value of your Dynamics GP system with just a small investment of your time.

You can get some of the best knowledge about Dynamics GP right here. Our best posts about Dynamics GP distribution and manufacturing are included in these brief ebooklets. You can download them right from this blog post.

Our consultants work with many different organizations and have seen Dynamics GP used in hundreds of different ways. These ebooklets contain their insights on how best to set up and use Dynamics GP.

Get them here.

Dynamics GP Distribution and Manufacturing

Dynamics GP Distribution and Manufacturing

Dynamics GP Distribution and Manufacturing

Dynamics GP Distribution and Manufacturing

All we need is your email address to send the link. Try it now.

If you want to try out Dynamics GP free for 30 days, start a trial here:

Dynamics GP Free Trial

Dynamics Great Plains 2013 finally means the death of FRx….or does it?

Accountants have been producing financial reports with FRx for almost twenty years but according to Microsoft the end is near. FRx is not supported with Dyn GP 2013.

Wait a minute, FRx is not supported on a 64-bit operating system as well but we were able to get that working…….hmmm

I wonder. I installed FRx and created an ODBC connection to a GP 2013, SP1 install that was using SQL Server 2012 just to see what happened. I started FRx and darn, I could not connect to the SQL server because of the version of SQL.  Hmmm… to my knowledge FRx does not check the version of SQL, it just uses the ODBC driver. I then created an ODBC driver using the SQL Server Native Client 10.0 (also known as the SQL 2008 driver) and low and behold, my trusty old FRx reports actually ran and looked correct!

Now, I haven’t tested every type of report and I am sure that eventually FRx will not work with some service pack or version of GP, but at least for now I have a little more time and so do all those faithful FRx fans.

Printing a different company name and address on your checks based on the location assigned to the checkbook – report writer fun

The default check stub form in GP assumes that you have purchased pre-printed check stock with your company’s information such as name and address pre-printed on the checks.  But, let’s say the check stock your bank sent you doesn’t have this information?  And let’s for a moment also say that you have multiple checking accounts and you want to print different information on each check based on which account you are printing checks from.  For example, one account might be for your LLC, and another might be for a trust account, and you want that information to print on the check, i.e. “Fabrikam LLC Operating Account” and another say “Fabrikam LLC Trust Account”

This seems like a simple idea, but it can get a little complicated.  GP assumes that you purchased pre-printed check stock with that information printed on the check, and so you have to actually customize the check form to print the company name and address information assigned to a specific checking account.

I’m going to show you how, so roll up your sleeves and take a deep breath as I walk you through the process.  There are a lot of moving pieces here, so pay close attention, if you miss a step then it may not work as intended.

First, we’ll go through the setup part on the GP side.  You’ll go into your checkbook in GP and assign an address to it, by going to Cards –> Financial –> Checkbook.  Open up the checkbook you want to assign an address to.  Notice the Company Address ID is missing:


Click on the hourglass next to company address ID, and select an address ID that you want to assign to this checkbook.  If you do not have one setup yet, you will click on the blue underlined company address ID label, which will take you to the address window:


Fill in the new address information, note that this information will be how it prints on the check so make sure you name the company name and address exactly how you want it to appear on your new checks and click Save, and close the window.  Then, select that new account in the Company Address ID field on the checkbook maintenance screen to assign it to your checkbook, and click Save on the Checkbook Maintenance screen to save your changes.

Phew!  Now we’re done what next?  It’s time to go to Report Writer and customize that report!

Click on the Microsoft Dynamics GP Logo and click on “Tools” then “Customize” then “Report Writer” (you can also press Alt-F9 to open Report Writer from GP).

Once Report Writer Launches you will see a product window pop up, it should default to Microsoft Dynamics GP, so click OK:
Report Writer Dynamics GP

Once report writer opens, it’s like a blank canvas, you have 4 buttons at the top.  To start customizing a report, click on the Reports Button:

Once you click on the reports button, you will see all your reports that you use in GP.   If you’ve never customized any reports, the modified reports side should be blank.  Scroll down through original reports, and locate the “Check With Stub on Top and Bottom – Graphical” and click on the “Insert” button to copy it over to Modified Reports:
Report Writer Dynamics GP

Now, you’ve copied the form over you can edit it by clicking on the form and clicking open.   When you open it, you still don’t see the report, you see this report definition box pop up:


Before we go look at the layout, we need to add the company address fields to the check report.  To do this, we need to link the table since the company address master table is not normally part of the check report, so click on “Tables” to open up the table window:
Report Writer Dynamics GP

This window shows you the tables that are included by default on the report.  We need to add a few more tables so we can get that address information we added to the checkbook earlier.  So, click on New, and you will add the CM Checkbook Master table by clicking highlighting the CM Checkbook Master and clicking OK:

Now you’ll see we have the checkbook on our report which has the “Address ID” field in it, but we will also need that Company Location Master to get the name and address to print on the check.  Click on CM Checkbook Master and click “New” which will open up the related tables again:

Click on Company Location Master, and click OK.  If you did everything correctly, your Report Table Relationships Window now looks like this:

You can go ahead and close this window by clicking close and we’ll move on to the next step of the process, the exciting part!  Adding these new fields to your report!  To do this you will click on the “Layout” button on Report Definition to pull up the layout of your check form:

Oh no!  It’s a big scary window with all this stuff, what do I do?  Don’t panic!  You’ve made it this far, we’re getting closer.  If you look at the report layout screen you will see this is the default layout for printing a check, with the various fields that go on the check stub as well as the check.  Off to the right of that, we have our “Toolbox” which is where we can add fields to the report, or add a text box to the report by clicking on the “A” font button.  Behind the toolbox there is a properties window.  When you click on a field on your report, the properties window pops up which allows you to edit your fonts various attributes of the field.

Before we go ahead and add that company name and address to the report, there is one more housekeeping item.  Because you’re going to add a customized address to the report, rather than the standard one, you will need to setup a City-State-Zip “Calculated field” so that the City, State and Zip can print perfectly on the form, otherwise you’ll have a bunch of blank spaces in between the 3 fields because the system will interpret whatever field size, so if City has a max size of 40, and your city is only 20 characters, you would have 20 blank characters.  To fix this we do as follows:

In the toolbox section, where it shows “PM Payment Work” you’re going to click on that, and change it to “Calculated Fields”:

When you click New, the following window pops up:

  1. Give it a name such as “Calc City-State-Zip”
  2. Change the Result type to “String”
  3. Expression type “Calculated”
  4. You need to trim the address, so first you will go to the functions tab, select system-defined, and locate the STRIP function, click add
  5. Now go back to the fields tab, select the Resource “Company Location Master”, and select the “City” field and click Add
  6. Then in the calculated box where it’s adding your fields, click the mouse pointer just off to the right of the ) so it looks like this:
  7. Then click on CAT to add the CAT symbol (#) which will tell it to concatenate the fields together into one
  8. Then click on the constants tab, change type to String, and in the constant box type , and a space and click Add
  9. Then click on the CAT to add another (#)
  10. Then add the strip function again under the functions tab,  and click on fields and select the State field and click Add
  11. Click your mouse pointer just to the right of the ) like we did in step 6
  12. Click on the CAT to add another #
  13. Click on Constants tab and select type of string and type a space in the constant box and click add
  14. Click on the CAT to add another #
  15. Click on the Functions tab, and add the strip function again, then go to the fields tab and add the field Zip Code from Company Location Master.  When you are done it should look like this:

In the Report Layout box, you’re going to look at section F2 – Check & Hdr – this is where you want your company name and address to print, probably in the top left corner of the check, much like a return address on an envelope.  Locate your toolbox with all your fields, and you’re going to click on the little down arrow next to the list of tables, and select “Company Location master”:
bp1-16Scroll down the list until you see “Location Name” , and drag it onto the report under check & hdr:


Go back to the toolbox, and select the field “Address 1″ and drag it under location name…

Lastly, go back to the toolbox, and click on the drop down where company location is and change it to “calculated fields”, and select the Calc Company City-State-Zip field that we created earlier and drag it onto the report.  When you are finished it should look like this:

Note:  You may need to click on the Calc Company City-State-Zip field on the right side and drag it out to make it wider like this if it’s cutting off on your report:

Now that you are done editing your report, you’re going to return back to GP by going to “File – Microsoft Dynamics GP”  … Important NOTE: Make sure when prompted to save the report layout that you click Save or you will lose all this work!  It may ask you twice, if so, click save both times…

Back in GP, you will now need to give your users access to the report.  You will need your system password for that.  Click on Microsoft Dynamics GP –> Tools –> Setup –> System –> User Security and enter your system password.

In the security setup window, select user sa, and select your company from the company drop down, and then at the bottom, click on the “Alternate/Modified Forms and Reports ID” blue underlined text to bring up the report screen.  You will need to enter your system password again.

In this window, you will select Reports Under type and it will bring up a list of reports.  Expand the Purchasing by clicking the Plus next to Purchasing.  Then go to the “Check With Stub on Top and Bottom – Graphical” and click on the plus next to it.  From there, you will see there is a modified report, so click the radio button next to the modified one so it’s selected, and click save at the top.


Once you click save, the window clears, go ahead and close the Alternate/Modified forms and reports window, and also close the security setup window.

You are officially done! Now you can go to print your checks.  NOTE:  Make sure when you print your checks that the stub you just customized is the one that is selected in the Check Format section, it should say Stub Top/Bottom – Graphical like this:


And here is my pretty check with the company location name and address printed on it! :)



I hope you have enjoyed this lesson in Report Writer and customizing the checks!


Dynamics GP Bank Reconcile to GL



Dynamics GP Bank Reconcile allows you to easily reconcile your Dynamics GP Checkbook to the General Ledger. This will really cut down on the amount of time it takes to validate your cash accounts and prepare financial statements.

Reconciling bank statements is a task that involves matching your bank’s account statement to your bank records in GP and to your general ledger. You are reconciling three sets of records. It can be a nuisance if you don’t keep on top of it and use the tools within GP to help you out. The bank reconciliation module has its own reconciliation process to match your bank’s account statement to the bank records created and maintained in GP. The Dynamics GP Bank Reconcile function allows you to then easily match the bank records in GP to the general ledger transactions in GP. Once you complete these two processes you can be assured that your financial statements related to cash are correct.

To run the Bank Reconcile to GL, go to the Finance Area Page >> Routines >> Reconcile to GL

Dynamics GP Bank Reconcile

Here you just select the Checkbook you want to reconcile, and the general ledger account that represents that checkbook. Specify the date range for which you want to select transactions for both bank transactions and general ledger transactions. Then click on Process. You will get results similar to this:

Excel Bank Reconcile to GL



The “Unmatched Transactions” are the ones that you’ll want to investigate. There are links to the underlying transactions in GP from the Excel results report that will help you identify and correct the problems.

The “Potentially Matched Transactions” are transactions that do not match on these four criteria:

  1. Transaction Source
  2. Document Number
  3. Transaction Date
  4. Payment/Deposit Amount

You will want to analyze these to see how your records can be adjusted to fix the problems.

The “Matched Transactions” are good and you don’t have to worry about them.

After you have made your changes you can run a new reconciliation to see the results.

Here is a quick video that shows this functionality:

Dynamics GP Inventory Reconcile to GL

Dynamics GP Inventory Reconcile to GL allows you to quickly reconcile your inventory records in GP to your general ledger.

The reconciliation of inventory to the general ledger can be complicated because you may have several general ledger accounts that represent the valuation of your inventory. And there are a lot of transaction types that effect inventory, e.g. purchase orders, cycle counts, sales invoices, quantity/value  adjustments. And if you then use the customer and inventory masterfile to select different GL accounts based on specific customers and items, you can have a real mess if everything isn’t set up correctly.

To run the Dynamics GP Inventory Reconcile to GL process, navigate to the Finance Area Page >> Routines >> Reconcile to GL:


The key with this window is to correctly and completely identify the general ledger accounts that represent your inventory value. Once you’ve selected the appropriate dates, click on “Process” to view the results in Excel.

This is an example of what you’ll see:

Dynamics GP Inventory Reconcile

The “Unmatched Transactions” are the ones that you’ll want to investigate. There are links to the underlying transactions in GP from the Excel results report that will help you identify and correct the problems.

The “Potentially Matched Transactions” are transactions that do not match on these criteria:

  • Transaction Source
  • Journal Entry Number
  • Document Number
  • Date
  • Amount

You will want to analyze these to see how your records can be adjusted to fix the problems.

The “Matched Transactions” are good and you don’t have to worry about them.

After you have made your changes you can run a new reconciliation to see the results.

Here is a quick video that shows this functionality:


Inventory Insights


Free Dynamics ERP Demos

Free Dynamics ERP Demos are available at If you just want to get your hands on Dynamics AX, GP, or NAV, this is a great place to do it.

If you want to try out the new web clients for GP and NAV, they’re at goerpcloud as well. Does it take a long time to set up a free demo? No; about 5 minutes.

You might also check out the Cloud Resource Library. There are a lot of white papers, brochures, blogs, etc. that you may find interesting. You can filter results to just get what you’re looking for. For example, if you’re just looking for something about manufacturing, filter by Industry Solutions:

Free Dynamics ERP Demos


To get started, you can start here to access AX, GP, or NAV, or complete the sign up form below for GP:

Free Dynamics ERP Demos

If you’re looking at new ERP solutions for your organization  wouldn’t you like to at least try it out first, before you commit to a new system. goERPcloud allows you to do that instantly, with no commitment. You’ll have access to a fully working Dynamics ERP system for 30 days with no credit card and no wait. Try it out now.

Dynamics GP Management Reporter Income Statement

A Dynamics GP Management Reporter Income Statement can easily be created from scratch.

You just need to create a Row Definition and a Column Definition, and then include them in a Report Definition, and print.


These are the three components:

Here is a Row Definition:

Management Reporter Simple Income Statement


Here is a Column Definition:

Management Reporter Simple Income Statement


Here is a Report Definition:

Management Reporter Simple Income Statement



If you’re used to FRx, Management Reporter will feel very comfortable to you.

Here is a quick video I put together that shows a glimpse of Management Reporter:



A good way to get started is to try the Report Wizard to create your first few reports. It walks you through the process of creating a report, and takes advantage of your chart of accounts and your account format.

This is a quick demo that shows this being used:



Sorting in Smartlist the Smart way

312339_117542088417759_1094173370_nLast year I worked closely with customers that performed data dumps into excel from Dynamics GP.

There are the easy exports where they just publish the report in text mode and then open the same in excel and I’ve seen customers that love formatting data in excel, spending countless hours formatting and at the end they have to do it again because data was refreshed.


There are others that are more complicated but yield better results, like Excel Reports, SSRS or plain T-SQL extracts and just very few who take advantage of the Export Solutions but that will be a subject of a future post so stay tuned.


Smartlist is a very useful tool within GP but some customers find it confusing because of the filters and how data is stored in GP.

One of the questions that made me write this post for you is, How can I sort data in Smartlist using more than one field, so I don’t have to do this in Excel?


The solution is simple but believe it or not, I learned that not many people know about this, I queried my own colleagues and I was shocked.

Here is my revelation for you, hope I can save you some time …

Use the Order By feature in Smartlist. Let’s go thru a sample:

Step 1.- Open Smartlist

Step 2.- Open Financial > Account Transactions

Step 3.- Double click on the * named smartlist


Step 4.- In the Search Account Transactions window click on the Order By button


Step 5.- Add the sort rules/levels from the list on the left, by using the Insert button

in my sample I am sorting by Account, then by the amounts, but I want all debits  from highest amount to the lowest, then I want all Credit postings same rule and lastly I want to sort by the Series.



And here is the result, so no more single column sorting!!!

Step 6.- Click OK twice and see your results



Here is another sample where I am sorting by Journal entry then Highest Debit Amount to lowest and lowest Credit Amount.




Until my next post!!!

Francisco G. Hillyer

Subscription Options:
Subscribe via RSS
Articles Categories