Author Archive

Analytical Accounting Budgets not Pulled on Management Reporter while filters is enabled.

I got a very interesting case few months back, one of my customers implemented the Management Reporter and tried to implement their existing reports, it went well but for the reports that uses the Analytical Accounting Budgets that has filters over specific dimensions, budget amounts were not pulled at all.

At that time I tried very hard to resolve the issue with no luck, and therefore I had to install the FRx and work out the reports there till getting this resolved by Microsoft.

During the last days I have noticed many articles by Microsoft that they has identified the case and fixed this issue with the product updates, therefore this morning I decided to install Management Reporter 2012 with the final update to see if the issue was resolved, I created a dummy report and tested the case with no luck! Same issue is still there while I couldn’t replicate the same issue on a clean database.

After many researches I have seen a question answered in the community and located here, it is the same issue I am currently investigating, and the provided cause of issue is the gap in serials for “Analytical Accounting Transaction Dimension IDs”, simply the data inside AAG00400 table should look like the below:

image

What if I been having one of the dimensions coded as “15” instead of “7” as the case I had? The proposed solution was to create dummy dimensions to cover the gap between 9 and 15 which is doable and safe solution.

The problem is what if I had one of the dimensions coded as “700”! I am sure that there is no was to create 684 dummy dimensions to cover the gap where I had to reindex the whole database.

The operation I followed is very risky and requires a real expert in the SQL, below steps I used to fix my issue:

1. I searched for the tables that I need to affect, simply these are all the tables that has “aaTrxDimID” column, went to the SQL and wrote the following query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Column_Name = ‘aaTrxDimID’ AND COLUMN_DEFAULT IS NOT NULL

2. I didn’t take a backup of the database but you must take a full backup!

3. I took a backup for the tables that I need to update by running the following query:

SELECT * INTO BAK_AAG00401 FROM AAG00401
SELECT * INTO BAK_AAG00901 FROM AAG00901
SELECT * INTO BAK_AAG01001 FROM AAG01001
SELECT * INTO BAK_AAG01002 FROM AAG01002
SELECT * INTO BAK_AAG02000 FROM AAG02000
SELECT * INTO BAK_AAG02001 FROM AAG02001
SELECT * INTO BAK_AAG40003BAK FROM AAG40003BAK
SELECT * INTO BAK_AAG30003BAK FROM AAG30003BAK
SELECT * INTO BAK_AAG04001 FROM AAG04001
SELECT * INTO BAK_AAG20003 FROM AAG20003
SELECT * INTO BAK_AAG10003 FROM AAG10003
SELECT * INTO BAK_AAG00202 FROM AAG00202
SELECT * INTO BAK_AAG30003 FROM AAG30003
SELECT * INTO BAK_AAG00312 FROM AAG00312
SELECT * INTO BAK_AAG00400 FROM AAG00400
SELECT * INTO BAK_AAG00316 FROM AAG00316
SELECT * INTO BAK_AAG00801 FROM AAG00801
SELECT * INTO BAK_AAG40003 FROM AAG40003
SELECT * INTO BAK_AAG00402 FROM AAG00402
SELECT * INTO BAK_AAG00403 FROM AAG00403
SELECT * INTO BAK_AAG00404 FROM AAG00404
SELECT * INTO BAK_AAG00405 FROM AAG00405
SELECT * INTO BAK_AAG00406 FROM AAG00406
SELECT * INTO BAK_AAG00407 FROM AAG00407

4. I have identified what are the existing IDs and what it should be and ran the following set of commands for each record:

UPDATE AAG00401 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG00901 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG01001 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG01002 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG02000 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG02001 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG40003BAK SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG30003BAK SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG04001 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG20003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG10003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG00202 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG30003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG00312 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG00400 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG00316 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG00801 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG40003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG00402 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG00403 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG00404 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG00405 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG00406 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47
UPDATE AAG00407 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47

Viola! The budgets are now generating data and everything looks fine for the time being, I will keep you posted with any other issues that might occur due to this operation.


Regards,


Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
http://www.di.jo

View article:
Analytical Accounting Budgets not Pulled on Management Reporter while filters is enabled.

October 23, 2013 ·  · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 225

Microsoft Dynamics GP 2013 Financial Management

This morning I enjoyed reading a book by Ian Grieve discussing Dynamics GP Financials – Related modules, it contains all the information needed to to cover your financial needs using Microsoft Dynamics GP, it starts with Analytical Accounting module, moving to Cashflow Management, Budgeting and finally PO Commitment and Encumbrance Management.

For those who would like to learn more about the mentioned modules, I would recommend purchasing and reading this book from the below link, it worth every buck!

http://bit.ly/16n4oO2


Regards,


Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
http://www.di.jo

Visit site:
Microsoft Dynamics GP 2013 Financial Management

October 18, 2013 ·  · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 145

Moving from Dynamics GP to another ERP?

Are you looking to move from GP to another ERP like AX for example? I would extremely encourage you to read the following article by Martin in the eOne blog, the guy is collecting too many useful reasons to think twice before making this change, take a look into his article below:

http://eonesolutions.blogspot.com/2013/06/we-switched-from-gp-to.html


Regards,


Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
http://www.di.jo

View post:
Moving from Dynamics GP to another ERP?

September 1, 2013 ·  · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 163

GP 2013 – Smart List Crash when it reach 32,767 Records for Export

As many of you might notice, upon exporting huge number of records from Smart List (specifically more than 32,767 records) Smart List generates an error, this is actually a bug in GP 2013 which reflect the maximum number the integer can handle.

The good thing is that this was resolved in the newly released SP 2:

image


Regards,


Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
http://www.di.jo

Read More:
GP 2013 – Smart List Crash when it reach 32,767 Records for Export

September 1, 2013 ·  · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 208

Dynamics GP Business Portal: Folder Executive– CompanyName does not exist.

I ran into the below error this morning while installing business portal for Dynamics GP 2013:

———————————————————————–

Feature Id:  538b415a-89eb-4de6-a93c-9fea1d565363

      Location:  C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14TEMPLATEFEATURESBPGPReportsCenter

        Action:  Activate

     Exception:  Folder Executive – Company Name does not exist.

   Stack Trace:     at Microsoft.Dynamics.SharePoint.Helper.ValidateFolder(SPList pageLibraryList, String folder)

   at Microsoft.Dynamics.SharePoint.Helper.AddFolderPermission(String siteRelativePath, String pageLibrary, String folder, String group, String permissionLevel)

   at Microsoft.Dynamics.GP.BusinessPortal.GPReportsCenter.CenterActivateProcess(String urlCenterLibrary, String quickLunchTitle, String groupName, String groupDesc, String folderName, String pageLibraryName)

   at Microsoft.Dynamics.GP.BusinessPortal.GPReportsCenter.FeatureActivated(SPFeatureReceiverProperties properties)

   at Microsoft.SharePoint.SPFeature.DoActivationCallout(Boolean fActivate, Boolean fForce)

   at Microsoft.SharePoint.SPFeature.Activate(SPSite siteParent, SPWeb webParent, SPFeaturePropertyCollection props, Boolean fForce)

   at Microsoft.SharePoint.SPFeatureCollection.AddInternal(SPFeatureDefinition featdef, Version version, SPFeaturePropertyCollection properties, Boolean force, Boolean fMarkOnly)

   at Microsoft.SharePoint.SPFeatureCollection.AddInternalWithName(Guid featureId, String featureName, Version version, SPFeaturePropertyCollection properties, Boolean force, Boolean fMarkOnly, SPFeatureDefinitionScope featdefScope)

   at Microsoft.SharePoint.SPFeatureCollection.Add(Guid featureId)

   at Microsoft.Dynamics.GP.Deployment.SharePoint.Office14.FeatureAdapter.Activate(Guid id, FeatureScope scope, String siteCollection, String relativeUrl)

   at Microsoft.Dynamics.GP.Deployment.WindowsInstaller.SharePoint.SharePointActivateFeature.OnPerform(IFeature spFeature, SharePointFeatureInformation spFeatureInfo)

   at Microsoft.Dynamics.GP.Deployment.WindowsInstaller.SharePoint.SharePointFeatureDeploymentProcessor.Process(IFeature featureInterface, SharePointFeatureCustomActionData data)

———————————————————————–

After searching everything on the internet and investigated all possibilities I found finally that one of my companies contains “-“ and “*”, removing these from this company resolved my issue!


Regards,


Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
http://www.di.jo

Read this article:
Dynamics GP Business Portal: Folder Executive– CompanyName does not exist.

July 3, 2013 ·  · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 223

Update SQL Server Collation for a Clustered Installation

I been in a situation with one of my clients where I been implementing and Active/Active clustering setup over SQL Server 2008 R2 as my customer is still using GP 10.0, after finalizing and testing everything I noticed that the code page I must use is 1256 while I have used 1250!!!

Thanks god for having a utilities to update the collation without having to destroy the cluster! I have followed the steps below and it worked like a charm:

1. Backup your objects! As this process will drop logins, linked servers, jobs, and anything related to the instant itself plus will de-attach your database.

2. Make sure that your current server is online and carrying your SQL Instant.

3. Make sure that SQL Services is offline.

4. Run the following command:

E:SQLsetup.exe” /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SAPWD=P@ssW0rd /SQLCOLLATION=Arabic_CI_AI /SQLSYSADMINACCOUNTS=”DAOUD-PCDAOUD

E:SQLsetup.exe will need to be replaced with the locations of the SQL Server 2008 R2 setup.exe path.

MSSQLSERVER represents the default instance, if you have a named instance this must be changed to hold the instance name.

P@ssW0rd: is the new SA user password, it must be strong password or otherwise the repair will fail.

Arabic_CI_AI: is the new collation name.

DAOUD-PCDAOUD need to be replaced with the local administrator of the server or PC you are installing the SQL on.

Paste above command into CMD and wait for 3-4 minutes, you will have your SQL Collation changed.

Warning: this operation will recreate your master database, were all existing setting will be reset, if you had databases attached it will be de-attached and will be found in the database folder.


Regards,


Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
http://www.di.jo

Read More:
Update SQL Server Collation for a Clustered Installation

July 1, 2013 ·  · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 178

Business Portal 6.0 for Dynamics GP 2013

It has been finally released, Business Portal 6.0 for Dynamics GP 2013, it still does not support SharePoint 2013 but it has many enhancements could be found HERE.

Business Portal 6.0 is supported with the following: Click HERE to go to the System Requirements Page.

  • SharePoint Server 2010 or SharePoint Foundation 2010 only. 
  • Windows Server 2008 x64 SP2 or later or Windows Server 2008 R2 SP1 or later. 
  • Internet Explorer 8.0, Internet Explorer 9.0, and Internet Explorer 10.0.

*NOTE:* SharePoint 2010 is currently not supported on Windows Server 2012 until the release of SharePoint 2010 Service Pack 2 due out this summer.

Below is the direct download link:

BusinessPortal60_RTM_x64.zip


Regards,


Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
http://www.di.jo

See original article:
Business Portal 6.0 for Dynamics GP 2013

May 10, 2013 ·  · No Comments
Tags: , , , , , ,  · Posted in: Blogs I Follow Total Views: 204