Dynamics Blogger – Blog Analytics for the year 2010

Personally, I could not contribute much to my blog for this year 2010 due to various reasons, one of which is primarily due to my job transition. However, readers continue to show their interest towards the blog which keeps me encouraging. I have been contributing to the community after being influenced by the fellow Dynamics Community legends like David Musgrave, Mariano Gomez, Mark Polino, Frank Hammelly, Victoria Yudin and several others. I learnt one thing that what we take from the community in the form of knowledge, we are also obliged to give the community the same knowledge what we acquire over the course of time.

Coming back to the Blog Analytics for my blog, My intention to publish the numbers here is to keep myself encouraging and help let others take notes on what topics users are interested to read and searching for so that the blog writing can be more focused on those topics.

One major observation that I made from my analysis that GP blog readers are specifically interested in trouble shooting articles. It is quite common. Whenever the user get struck on specific issue, we are used to bing or google it for probable answers which ultimately lead to the blog of the interest.

Here is the presentation on Dynamics Blogger Analytics.

  1. Country Wise Readership Facts: United States brought the most visitors to the site as always, for most sites. US is followed by Canada, UK and India respectively.


2. Search Engine Wise Traffic Facts: Google is the clear winner which brought maximum visitors for the site.


3. Page wise Statistics: While page visits direct to site root are higher in number, the second highest numbers are brought to the site by the article “Send To: Mail Recipient (PDF) grayed out” followed by other articles.


4. Keyword Searches Facts: “kb971808” and “All call stacks are in use” are the Top two searches for this year.


Happy New Year 2011 to everyone!

Name one thing in GP that really sucks

business man biting pencil

An interesting question. Isn’t it? I borrowed this Q&A from LinkedIn Group Microsoft Dynamics GPS User Group. The dialogue is initiated by Ian Stewart, Dynamics GP expert and there are pretty interesting submissions by users. This is some kind of raw data that is vital and critical to Microsoft in fine tuning its product upgrades. Because, User feedback is the top most source for the success of any product. I’d say the end users are the actual testers of any application.

Key submissions by the users for the question above are summarized below

  1. Payroll is not fun for one user though she doesn’t go into much detail as to why it is the case
  2. Contract Management as they relate to RMAs’ is not fun  to another user.
  3. One user doesn’t like the ‘save changes’ dialog boxes function through Dynamics GP.
  4. Another user feels it frustrating since V2 that he cannot pick the printer on the fly. Though Named printers can be an alternative, yet.
  5. Another user says Inventory Turns report doesn’t calculate correctly.

In my experience, I have seen most clients ask me two common features

  1. Change the Font of the entire GP bigger including the forms and reports
  2. Text reports replaced by Graphical reports.

Visit the original source here. Credits to Ian Stewart from Genesys Financial System Ltd. for the post.

Year-End Closing: Backup And Backup Data

“Prevention is Better than Cure”

We came to the Year-End of 2010 and we would see the Year-end closing Routine in Dynamics GP in most clients in the months to come based on their Year-end Audit schedules. Year-End Closing in Dynamics GP is all that simple it looks. It can also be a night mare if not handled properly. In general, Clients  depend on their Microsoft partners with the year-end process. This is always a safe bet relying on the subject-matter experts on clients side. However some clients do not want to go for Partners support and instead perform the Year-End process. If they have an in house GP consultant, yes, it helps. If not, Be wary of it.

MAKE SURE YOU TAKE A VALID BACKUP OF ALL COMPANIES AND DYNAMICS DATABASES BEFORE RUNNING THE YEAR-END ROUTINE. There is no exception to this task. You MUST take the backup. I have seen several clients experience problems who failed to take sufficient data backups. A classical example of what I have seen is the client thought the Year-end process went well, didn’t have any backup pre and post year-end routine. Continued posting transactions for the current year and after a month or so, realized the balances still popping up in Summary Inquiry and Detail Inquiry instead of them sitting in History Inquiry and History Detail. Cause was Year-end wasn’t complete and the balances were still in Open (GL20000) tables instead of History (GL30000) tables. This is one such example and there can be several other examples and experiences encountered by fellow consultants. Most clients would want to ring their Microsoft Partner buddies only when they experience issue and do not want to ring them before that.

My suggestion would be to have your Microsoft Dynamics GP Partner assist you in the Year-End process, be it an extra charge for you. Their expertise can always help you close the year smoothly than to run into issues later on.


DynamicsBlogger Daily

It’s always fun on Internet with the kind of new tools available for us to explore. One such tool that I recently came across is paper.li, An application that consolidates the data from various twitter posts and presents the readers with a well organized e-magazine format. The source of information can be either the hashtag of a twitter search or specific search terms from which it generates all the tweets into a formatted magazine. Over and above, it also extracts the hidden links behind those short URLs’ and in turn presents us a complete digest. It also update by itself every day!

I thought I should give this a try to see how Dynamics GP tweets are turning up on this cute twitter application. I realized it’s worth than I thought it to be!

Here is a snapshot of the Dynamicsblogger Daily.


let me know your comments.


Microsoft Dynamics Community Website got a new look

Microsoft Dynamics community website has got a version upgrade today with a complete new look and feel. The original community website was launched in 2007 and since then the website has become a one stop resource for Microsoft Dynamics users and professionals. Currently, the site is serving about 40,000 + members.

For a detailed information of the new features introduced with the community 2.0 release, visit the link below

Community 2.0 Features


How to reverse Revenue Recognition transactions in Project Accounting

When the revenue is recognized for a project using Revenue recognition entry in Microsoft Dynamics GP Project Accounting module and if there is a situation where we would like to reverse the posted RR transaction. Here in this article, I put up the steps to do the same.

1. Make sure override RR amount is marked in PA | Billing | RR Options setup. Otherwise, the system wouldn’t allow us to change the amounts in RR entry window.

2. Open RR Entry window, Enter the customer ID and the Project ID for which revenue transactions that were posted earlier need to be reversed now.

3. Once the Project ID is selected, click the arrow next to Budget


4. Enter the negative amount in each cost category in To recognize field. If the amount is higher than the recognized amount, then the system wouldn’t allow doing that.


5.  Now Post the RR transaction so as to reverse the posted RR transaction.


How to copy Smart list to Excel Report Builder

If you look to copy a report from Smart list builder (SLB) to  Excel Report Builder (ERB) or vice versa, there is an option in both SLB and ERB called “duplicate” in options menu to do this task for us.

To copy a report from SLB to ERB, Go through the following steps

1. open the report to be copied in SLB, Go to options | Duplicate


2. Choose Excel Report in New List type and assign New list ID and New list name or accept defaults.


3. Click Duplicate.

Now, Go back to ERB. The same report appears in ERB as in SLB. Repeat the same steps for copying report from ERB to SLB.

Display Totals in Smart lists

While we all know that Smart list doesn’t support displaying totals, We now have a workaround to beat this limitation in Dynamics GP 2010. Though I’m yet to test this functionality, I thought to quickly share this cool feature.

In Excel Report builder, Select the report and go to options and mark “Display totals at the end of each list”. This feature would then ensure all the numeric fields in the reports sum up when the report is published.


We can duplicate all our existing smart list builder reports to Excel report builder using Duplicate option in Smart list Builder to copy them to Excel Report Builder.

EFT Module: Bugs or by Design?

Quite recently, I had a chance to work on EFT for Payables Management module first time as one of my clients wanted this feature implemented in their environment for Dynamics GP 10 for one of the global bank format. During the feasibility test, I found the following issues which I termed them as “bugs” while Microsoft Technical support doesn’t want them to term them as bugs and instead call them “by design”. Well, while I understand it is always difficult to accommodate specific requests into a product that has a global reach and instead guide those requests to be routed to a product suggestion however I felt the below issues are far more critical to be fixed as they question the very functionality of EFT module in specific.

In EFT File Format maintenance, there is calculation field mapping that is available to map certain fields. Whenever calculation type is selected in maps to field, then various options appear as shown in the image below.


The purpose of these calculation types are as follows that is provided in GP documentation.


The documentation states these types are often used in batch footers or file footers of flat files, however, it doesn’t explicitly deny the fact that they are supposed to work in other line types. Below are the issues with these calculated fields. (Not all calculated fields are tested)

1. Line count when used in File header generates 1 constantly when it is supposed to place the value of total number of lines in the file. Say a file has 10 lines including file header, it has to place a value of 10 but it generates value 1 constantly.

2. Total Credit Amount when used in batch header generates 0 value

3. Detail count when used in batch header generates 0 value

I didn’t test the other calculated fields though. I went to Microsoft Technical support route to help fix these issues and here is the final reply that I received from them.

The Line Count, Number of Credits, and Total Credit Amount calculations are only designed to work in the footer lines (Batch Control, File Control, etc).  They do not work in the header files at this point, by design.  The calculations create the field in the footer lines and then update them as the EFT file is written.  Adding the functionality to the header rows at this point is not a possibility and may slow down the file processing procedure as well.

I’m in total disagreement to the above statement personally for the following reasons.

1. EFT in its first place is a module that has to be configured according to the formats provided by different banks. In other words, the module needs to be flexible enough to set any type of format.

2. It is not logical to expect the banks to follow the GP format and it should be the other way round. Expecting all banks to have line count, Total credit and number of credits like fields only in footer lines is not a valid assumption in my personal opinion.

3. In EFT, there are some readily defined formats. Among those formats, CA-National Bank of Canada (refer to image below) and CA-RBC Royal Bank have line count field defined in the file header. So How can we say that this is by design when there are several banks requiring the line count and other fields in File header or Batch header or Detail lines?




4. If this is the case, what’s the point providing a user-defined format in the format type if the system isn’t flexible enough to accommodate the bank specific formats?

Having said the above, I must say the module works absolutely perfect in terms of functionality barring few other format issues. Fortunately, In my case I’m blessed with a highly qualified in house dexterity development team who has been able to take care of the above issues very quickly through dexterity. I just thought to highlight the above issues to get to know anyone else with better thoughts or similar experience on this.


FRx to Management Reporter Migration

If you are looking to migrate from FRx to Management Reporter, There is an inbuilt migration wizard in Management Reporter to do the same. I don’t need to specify the steps involved in the migration process as it has been covered in much detail elsewhere on the blogosphere. Here is a perfect link to accomplish the same.

FRx 6.7 to Management Reporter Migration

However, Hold on before running the wizard if you are on Management Reporter RTM edition. If you are running Management Reporter RTM edition for Dynamics GP 2010, you’d run into an error that says 'Windows Authentication is not supported for Microsoft Dynamics GP’. I heard this occurs even if you choose SQL Authentication. If you observe the event logs, it might be like this

  Message: Microsoft.Dynamics.Performance.Reporting.DataProvider.Service.DirectLinkException: Windows Authentication is not supported for Microsoft Dynamics GP ---> Microsoft.Dynamics.Performance.DataProvider.GeneralLedger.GLConnectionException: Windows Authentication is not supported for Microsoft Dynamics GP
   at Microsoft.Dynamics.Performance.DataProvider.Core.TraceManager.Publish(Exception sourceException, Boolean throwException)
   at Microsoft.Dynamics.Performance.DataProvider.GeneralLedger.GP.GLProvider.HandleConnect(EntitySettingCollection settings, CredentialCollection credentials)
   at Microsoft.Dynamics.Performance.DataProvider.Core.SourceSystemProvider.Connect(EntitySettingCollection settings, CredentialCollection credentials)
   at Microsoft.Dynamics.Performance.DataProvider.Core.SourceSystemConnection.Open()
   at Microsoft.Dynamics.Performance.DataProvider.Core.SourceSystem.CreateConnection(SourceSystem system, EntitySettingCollection settings, CredentialCollection credentials)
   at Microsoft.Dynamics.Performance.DataProvider.Core.SourceSystem.Connect(EntitySettingCollection settings, CredentialCollection credentials)
   at Microsoft.Dynamics.Performance.DataProvider.GeneralLedger.GLSystem.Connect(Guid providerTypeId, EntitySettingCollection settings, CredentialCollection credentials)
   at Microsoft.Dynamics.Performance.Reporting.DataProvider.Server.DirectLinkService.GlConnect(Guid providerTypeId, EntitySettingCollection settings, StoredCredential credential)
   --- End of inner exception stack trace ---
   at Microsoft.Dynamics.Performance.Reporting.DataProvider.Server.DirectLinkService.GlConnect(Guid providerTypeId, EntitySettingCollection settings, StoredCredential credential)
   at Microsoft.Dynamics.Performance.Reporting.DataProvider.Server.DirectLinkService.ConnectInternal(Guid entityId, StoredCredential credential)
   at Microsoft.Dynamics.Performance.Reporting.DataProvider.Server.DirectLinkService.<>c__DisplayClassa.<Connect>b__5()
   at Microsoft.Dynamics.Performance.Reporting.DataAccess.Server.SecureService.<>c__DisplayClassb`1.<SecureExecute>b__a()
   at Microsoft.Dynamics.Performance.Common.Service.ServiceBase.BaseExecute[T](Func`1 executor)
Component: Microsoft.Dynamics.Performance.Reporting.DataProvider.Server.DirectLinkService
User: DEMOGP\managementreporter

Currently, This is identified as a bug in MR RTM and there is a workaround suggested to get away with this issue

1.      Uninstall MR RTM

2.      Install MR Beta (Server Components and Migration Wizard - https://mbs.microsoft.com/fileexchange/?fileID=0843903d-3544-49f5-af37-3e80ccd0384b

3.      Perform the migration using the beta

4.      Export reports into .tdbx.

5.      Uninstall MR Beta

6.      Reinstall MR RTM

7.      Create companies in MR and then import in reports from the .tdbx file.

(Source: Dynamics GP Forum)


Restrict User Access to Dynamics GP during Maintenance

In SQL server, We have an option to restrict databases to single, restricted user or multi users. When we want all users to connect to database, the status would be Multi_User. In case we have a maintenance plan and expect the other users not log into the database under maintenance, We can set the database to Restricted user or Single user mode.


Restricted user mode allows connections to SQL server through any user ID which is a member of dbcreator, db_owner or sysadmin roles. In other words, Users like “sa” can log into the database and continue with maintenance activities.

I tried to test this option on Dynamics GP Application databases and It seems to work perfect in my tests. I find this useful when I have to do some maintenance routines and do not expect the users to be logged into the application while the maintenance is still on. Once my maintenance routine is complete, I can reset the status to Multi_user mode which then enables connections to Dynamics GP databases.

If we need to restrict users access to Dynamics GP completely including all companies, We can set the restricted user mode on all databases and if we want to restrict access to any particular company, We can always set the database in question to Restricted user mode.

To set the status of databases, We have two options.

1. Through SSMS or

2. Through a Query in SSMS

Through SSMS to set the status,  Right click the database, click properties and click options.


A message pops up to disconnect any open connections apart from SSMS and click yes to it


Prior to this exercise, Make sure your users are all logged out. Once the maintenance is done, come back to the same screen and set the status back to Multi User.

Would expect some feedback from our community just to make sure there is no added risk doing this as i haven’t yet applied this anywhere before.

Note: This is for informative purposes only and do not apply it without proper backups in a production environment. Also Please do not change the status while other users are logged into Dynamics GP and this would adversely affect their active GP session immediately.


Requisition Management Enhancements – When?

I was thinking of much apt title for this post, I couldn’t get one. This post is about some of the pain areas in Requisition Management module for Dynamics GP (BP 3.0 or 4.0 or 5.0 whatever version we are). Because I feel we continue to see the same functionality since the old Business Portal 3.0 for Dynamics GP 9.0, Business Portal 4.0 for Dynamics GP 10.0 or Business Portal 5.0 for Dynamics GP 2010 with few exceptions. Having said that, We also understand there’s a lot of feature enhancements for BP as a whole, Workflows, Web services and Dynamics GP itself that is helping Dynamics GP to withstand as a favorite product to many in terms of choice.

I have implemented Requisition Management module in several places and I must say the product is flexible to implement and a robust functionality than what we could expect from. Yet, I opine that It has certain limitations which has to be addressed at least in the upcoming versions (Though not certain about when it would actually happen).

1. Ability to attach documents with each Purchase Requests- Idea here is to attach any supporting documents related to the request that can establish the valid need of the user for the same which in turn helps the purchasing or Finance departments to make right purchasing decisions. Requisition Management doesn’t support this functionality currently. workaround is to make use of Document library functionality in SharePoint.

2. Requisition Management integration with RFQ- Once any request reaches purchasing department, the purchasing department initiates purchasing process, part of which is RFQ process. A process that Requisition Management doesn’t support currently.

3. Multicurrency Support-  Currently Requisition Management allows users to enter requisitions in functional currency only and no multi currency is entertained.

4. Quantity Field- Quantity field in each Item is limited to 7 characters currently meaning that we cannot enter more than 1000000. Though it is very rare to see requests with more than 1 million in quantity field, yet it is a limitation to be looked into. However workaround is to split the quantities into two line items in this case.

Barring the above exceptions, the module works so intelligently and the biggest advantage I could see is its user friendly interface for entering and approving requests. So is the case with Approval Hierarchy which works for any kind of approval mechanism that is required for and easy to set it for.

Fortunately, Most of the above limitations have already been entered as product suggestions in Microsoft connect. Though we have other third party products integrating with Dynamics GP for Requisition Management, yet, Requisition Management for Dynamics GP module remains the majority choice in terms of cost and value addition it brings to the purchasing process as a whole.

Here is a link pointing to Product suggestions page in Microsoft connect and all I could say is to help strengthen the need to fasten the enhancement timeline for Requisition Management in specific.

Dynamics GP Requisition Management Product Suggestion Link


GPWindow.com: Wonderful Resource

I got to look at this wonderful site this morning www.gpwindow.com while trying to catch up myself to the latest developments in our GP blogging community of late. I must admit I’m very proud of my good friend Jivtesh work on the basic idea of this site and how it is structured to be a one-stop K-base for Dynamics GP resource shoppers. I must say it’s a GP shop with an exhaustive list of items that are available free of cost:). I always like the idea of “aggregation” of data and I personally know how tough it is to aggregate and present the consolidated data in a user friendly formats and structures.

It’s not an easy task to build a database like this specially segment like Dynamics GP which requires subject specific expertise in organizing data.

Jivtesh! Great Effort and Congrats on your great work. Keep going! I’ve already bookmarked the site under my favorites!

Jivtesh has given us a user’s guide for us on how to follow this site. Read here.


Dynamics GP 10 Service pack Upgrade Failure

While upgrading from Dynamics GP 10 SP1 to SP4, one of my clients have encountered the error below.

“The stored procedure Synchronize TableData() of form duSQLAccountsynch : 27Pass Through SQL returned the following results: DBMS: 102, Microsoft Dynamics GP: 0.”

This error was surfacing while running utilities and upgrading DYNAMICS database. I’ve not seen this error before. Taking cue from the error message, I thought initially it might be due to non-synchronization of account framework so tried resetting dex.ini file to Synchronize=TRUE, but it didn’t work at all.

To understand the probable cause of this issue, I had to rely on dexsql.log file.  The log showed that there is a possible permission issue, so we tried running the GRANT.SQL and DYNSA DB owner scripts on the databases as these were databases restored from Live instance. The same issue continued.

The dexsql.log showed that the database upgrade of DYNAMICS is getting interrupted with this error when it is reaching SY10800 table specifically.

After verifying SY10800 table, We realized that the index is missing for this table.

Now the upgrade went smoother. One thing which i didn’t understand during this process is that there is another table SY10800_t that got created possibly during the upgrade by the system itself. I assume the utilities upgrade is creating a temp table which should have been deleted otherwise after successful upgrade. However, it wasn’t deleted since the upgrade is failing at this point. I compared the SY10800 and SY10800_t tables to realize both tables are having the same data while index is missing in SY10800 alone. So, to try resolve the issue, I dropped the SY10800 table from the DYNAMICS DB and then renamed the SY10800_t table as well as index to SY10800 and PKSY10800 respectively. If someone knows the exact history behind this so called “_t” tables, it’d be great if they could add their comments here.

Now tried launching the Utilities and Voila ! It worked.

BTW, I found couple of good resources while binging for the solution related to this issue though they couldn’t help much in our case. Here you go




Remember Username and Password

We’re quite familiar with this phrase in almost all websites across offering us to store our usernames and passwords so that when we try logging in next time, we just don’t need to enter them again and again thus freeing our time little more. Surprisingly, Dynamics GP 2010 has got a feature similar to this option. In fact, It has not just one but two such options.

1. Remember user name and password – This option when marked, the GP client doesn’t ask you for your credentials and push you to the next screen, Company selection.


2. Remember this company – Now If You’re having 5 companies, and you would be using a particular company most often, You can mark this option so that it will hide this window of company selection as well when you log into GP next time. I find this option more useful when I have even one company.


Marking both the options above helps us to just get into Dynamics GP Home screen directly every time we click that tiny cute GP icon on our desktops. Something that is more similar to the way we log on to Dynamics AX client.

I remember several users asking for a windows authentication based mechanism for Dynamics GP for long. Though We don’t have it yet, at least we can say this requirement is offset to the most extent possible by this cool feature on Dynamics GP 2010.

When I first looked into this option, this option is disabled by default. To enable them, We can go to System preferences in System set up where we can mark Enable Remember user under Remember user option.

When I first enabled this option, I have to think a while and even rely on Dynamics GP help menu to help me find a way where I can get the Welcome screen back for me to either change the company or the user because when this option is enabled, It just don’t welcome me through its welcome to Dynamics GP screen and instead all I have is the Home screen before me. The tip to get back the Welcome screen is either disable the Remember Me option in system preferences or click on company or user to get back to the respective screens.

Until Next post…..


Unable to connect to the Home Page Metrics URL

In Microsoft Dynamics GP 2010, We can get this error.

“ Unable to connect to the Home Page Metrics URL. Please contact your system administrator.”

Causes of this error is that you’ve activated the option to use SQL server reporting services Home page metrics under Charts and KPIs’ in Reporting tools setup window (Dynamics GP | Administration | Setup | System | Reporting Tools setup)  and provided incorrect URLs’ in your SQL reporting services setup tab in Dynamics GP.

Resolution for this error is

1. Ensure your SSRS is running properly

2. In SQL Server Reporting Tools setup tab in Dynamics GP, Check the URLs’ provided for Report server and Report Manager. The standard path of these reports are provided in Help Manual of Dynamics GP 2010.

Until Next Post….


Management Reporter on Workgroup

For those who are looking to run Management Reporter on a workgroup machine, this is something to make note of. Management Reporter doesn't work on Workgroup or when we disconnect from the domain network. When we disconnect from a domain network, we get the error message saying "can't connect to MR Server". Once we connect back to the domain, it works normally.

Until Next Post....