Maps Reporting using SSRS

One of the challenges that I been trying to achieve is having my reports graphically designed to show data on “Maps” and that always been the toughest part customers might request.

Few days back I been researching with my colleagues on how the “Sales By States” KPI works in Dynamics GP that dynamically pulls the sales of each state on the US map and discovered the “Map” feature in SSRS!

We have been able to download the “.shp” file for Jordan map from one of the online free GIS providers and integrated this will SSRS, and then created the following report in SSRS in few minutes, saved this report in SSRS and been able to print this report from GP business analyzer:

This article is just to show that this is doable for users to give it a try.

Happy reporting!

---

Regards,

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

Cards Auto Numbering for Vendors, Customers and Items – Free Addon

We at Dynamics Innovations has developed a product for Dynamics GP allows end users to control the generation of the ID for customers, vendors and items, the tool is free and  available for use immediately, just drop me a mail and I will be sending the tool for you, once you get the DLL, please follow steps below to activate the product:

1. Copy sent DLL to GP\AddIns Folder.

2. Login first time with “sa” user to generate the needed database objects.

3. Open SQL and adjust the parameters to fit your needs:

a. You need to find a table called “DI_NextNumbers”, open the table.

b. Fill the prefix you need to use, length that includes the prefix, next number you wish to use.

Go to vendor card for testing and find the additional menu, same package works for customers and items if you wish to activate:

---

Regards,

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

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:

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

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

New Blog in town!

I would like to welcome a new blog in town for a colleague, his name is Mahmoud M. Al Saadi and has created a blog under the name of “Microsoft Dynamics GP Insight”, please join me welcoming Mahmoud to the community, being close to Mahmoud I would expect valuable articles specially when it comes to Supply Chain business.

Below is “Microsoft Dynamics GP Insight” blog:

http://mahmoudsaadi.blogspot.com/2013/09/dynamics-gp-2013-sp2-smartlist-designer_2.html

---

Regards,

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

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

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:

---

Regards,

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

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 Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\FEATURES\BPGPReportsCenter

        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

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:\SQL\setup.exe" /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SAPWD=P@ssW0rd /SQLCOLLATION=Arabic_CI_AI /SQLSYSADMINACCOUNTS="DAOUD-PC\DAOUD"

E:\SQL\setup.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-PC\DAOUD 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

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

Duplicated GL Transactions between Work and Open tables

One of my customers got a case that Journals were not deleting from GL10000 upon posting, this caused the transactions to duplicate between open and work tables and been annoying end users.

To fix this miss, I wrote a script that looks into GL10000 and GL10001 and compares the totals between both tables for each journal and generate the result, you can use this script to company these and make sure that all journals were successfully posted and none posted partially.

Below the script code:

Historical Stock Sttus Summary

1. SELECT UNPOSTED.JRNENTRY, UNPOSTED.AMOUNT, POSTED.JRNENTRY, POSTED.AMOUNT FROM
2.     (SELECT JRNENTRY, SUM(CRDTAMNT) AS AMOUNT
3.     FROM GL20000 GROUP BY JRNENTRY) AS POSTED
4. INNER JOIN
5.     (SELECT GL10000.JRNENTRY, SUM(CRDTAMNT) AS AMOUNT
6.     FROM GL10000 LEFT OUTER JOIN GL10001 ON GL10000.JRNENTRY = GL10001.JRNENTRY
7.     GROUP BY GL10000.JRNENTRY) AS UNPOSTED
8. ON POSTED.JRNENTRY = UNPOSTED.JRNENTRY
9. WHERE UNPOSTED.AMOUNT IS NULL

Hope that this helps.

---

Regards,

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

Dynamics GP 2013 Upgrade–Customized GL Inquiry Report

Guys,

If you are planning to upgrade to GP 2013, please consider to analyze your customizations carefully as there might be some changes between the application versions, I been in a situation were we upgraded from GP 2010 R2 to GP 2013 and the customer has a customized report for GL Transaction Entry Inquiry Report, the report didn’t work properly as the core table of the Report has been changed.

This is the only thing I found so far, but I wanted to let you know that you might need to plan redoing the customizations of this report upon upgrading to GP 2013.

---

Regards,

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

Mask a Field in Dynamics GP

I got an interesting question from one of the community followers and I saw that it should be published for community, the question was a request to have a special mask for the item number field, the user wanted to have the item number automatically has Dashes “-“ for each 3 letters to look like this >> “AAA-AAA-AAA” so the user will fill only 9 “A”s and the system to automatically add the “-“ in between and below how I covered this:

1. Go to Cards>> Inventory>> Item

2. Click on Tools>> Customize>> Modify Current Window which will open the modifier and allow you to modify the screen, click on the Item Number field and go to the “Properties” panel on the right:

3. Double click on “Field” option to get the following form:

4. Click on Open to go deeper with the Item Number format Options in Data Type Definition and then click on the button next to Format:

5. The format is currently set to be one of the out of the box formats that limits the length of the string to 30 characters, we will need to create a new format:

6. Customize the format as needed, for our example we will formatting the field as below:

7. Start clicking OKs to start all dialog windows, and save then get back to Dynamics GP and go to Cards>> Inventory>> Item:

8. Notice the change over all forms that has the item number field there!

Hope that this helps.

---

Regards,

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

The Top 100 Most Influential People in Microsoft Dynamics List for 2013

Well it is funny! First of all leave whatever you are doing and go to the following link then vote for me, we are running late and I didn’t publish about this before!

http://www.dynamicsworld.co.uk/the-top-100-most-influential-people-in-microsoft-dynamics-list-for-2013-page-10/

Talking about votes, I can see many names this year specially David Musgrave who is doing great for the time being, he owns 50% of the votes till now, he is on page 4:

http://www.dynamicsworld.co.uk/the-top-100-most-influential-people-in-microsoft-dynamics-list-for-2013-page-4/

For me, I did vote for the following, these are the people I know and sorry if I missed anyone:

http://www.dynamicsworld.co.uk/the-top-100-most-influential-people-in-microsoft-dynamics-list-for-2013-page-3/

Charles Allen, Christina Philips

http://www.dynamicsworld.co.uk/the-top-100-most-influential-people-in-microsoft-dynamics-list-for-2013-page-4/

Erik P. Ernst, David Musgrave

http://www.dynamicsworld.co.uk/the-top-100-most-influential-people-in-microsoft-dynamics-list-for-2013-page-5/

Gianmarco Salzano, Frank Hamelly

http://www.dynamicsworld.co.uk/the-top-100-most-influential-people-in-microsoft-dynamics-list-for-2013-page-6/

Ian Stewart

http://www.dynamicsworld.co.uk/the-top-100-most-influential-people-in-microsoft-dynamics-list-for-2013-page-7/

John Lowther, Jivtesh Singh, Jon Rivers

http://www.dynamicsworld.co.uk/the-top-100-most-influential-people-in-microsoft-dynamics-list-for-2013-page-9/

My favorite list! Mark Polino, Leslie Vail and the GP King Mariano Gomez! Wish you luck guys in the same list.

http://www.dynamicsworld.co.uk/the-top-100-most-influential-people-in-microsoft-dynamics-list-for-2013-page-11/

Nick Hoban, Pam Misialek

http://www.dynamicsworld.co.uk/the-top-100-most-influential-people-in-microsoft-dynamics-list-for-2013-page-12/

Richard Whaley

http://www.dynamicsworld.co.uk/the-top-100-most-influential-people-in-microsoft-dynamics-list-for-2013-page-13/

Steve Endow

http://www.dynamicsworld.co.uk/the-top-100-most-influential-people-in-microsoft-dynamics-list-for-2013-page-14/

Tom Taylor, Victoria Yudin

---

Regards,

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

Excel Paste Tips

Awesome idea created by Mark Polino, I am getting many mails daily concerning same questions and the answer every day is the same, Mark Polino used to get questions as well and he posted the following articles about some tips over the Excel Paste:

http://www.msdyngp.com/GP/weekly-dynamic-excel-paste-tips/

Well 50% of the product downloads is from Mark Polino sessions! It was his idea initially and it is his product now!

Worth to add that you need to make sure you have downloaded the correct version for your GP, this was added recently as we got many issues reported from users about incompatibility of frameworks and issues sometimes with missing DLLs:

---

Regards,

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

Purchase Order Commitment–Required Date from PO Required Date or Item Required Date?

I got an interesting question from of my customers who are willing to activate the purchase order commitments, the question was the documentation stated the following:

The Required Date must fall in a valid and open fiscal period.

The purchase order header has a required date field and the line items each has its own required date field, so which one will be used to commit the purchase order against the budget?

Logically each line item will be validated separately against the budget and therefore the date should be the line item date, after checking this it turned to be right and the system is looking into the line item “Required Date”.

---

Regards,

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

Historical Stock Status Script

We been suffering from Historical Stock Status report issues when it comes to customers with big inventory transactions, I am providing a script that recalculate the historical stock status for your items based on the IV10200 and IV10201 tables, it will allow you to estimate your inventories using a historical date:

Historical Stock Sttus Summary

1. DECLARE @ASOFDATE DATETIME
2. SET @ASOFDATE = '2016-12-31'
3.  
4. SELECT
5. ITEMNMBR AS [Item Number],
6. TRXLOCTN AS [Location],
7. SUM(Quantity) AS Quantity,
8. SUM([Extended Cost]) AS [Total Cost]
9.  
10. FROM
11.  
12. (SELECT ITEMNMBR,
13.  
14. (dbo.IV10200.QTYRECVD -
15. ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
16.         WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
17.         AND ITEMNMBR = IV10200.ITEMNMBR
18.         AND DOCDATE <= @ASOFDATE), 0))
19. AS [Quantity],
20.         UNITCOST *
21.             (dbo.IV10200.QTYRECVD -
22.             ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
23.             WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
24.             AND ITEMNMBR = IV10200.ITEMNMBR
25.             AND DOCDATE <= @ASOFDATE), 0))
26. AS [Extended Cost],             
27. TRXLOCTN
28. FROM dbo.IV10200 WHERE DATERECD <@ASOFDATE) AS SOURCE
29. GROUP BY ITEMNMBR, TRXLOCTN

Hope that his helps!

---

Regards,

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

Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013

Today I will be charging my timesheet on Mark Polino account for publishing his book! He has released an awesome book that details how to make your management graceful in reading their figures using Dashboards, he has utilized the excel features to generate a report that looks like the below:

Well done Mark! Go guys and purchase your copy of the book by clicking here, your ROI will be an increase in salary after providing the management with these dashboards!

---

Regards,

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

Vacancies

Dynamics Innovations is currently looking to hire talented resources with the following qualifications for its core business and for its customers and partners in Jordan and Saudi Arabia.

If you feel that you are qualified for one of the below positions and has enough knowledge in the fields requested below, you are kindly asked to send your CV to Careers@di.jo with the required expertise in the email subject, not filling the position required in the subject will lead to ignore your resume as it will not reach the concerned department.

1. Senior .NET Developer

Minimum 4 years of professional experience covering:

• VS 2008/2010, SQL Server 2008/2012, Windows Server 2008/2012
• ASP.NET & C# Web Applications
• Multi-tier Architecture, OOP and UML
• SQL Stored Procedures, ADO and ADO.net
• Experience in reporting service 2005/2008
• XML Web Services
• Experience in Microsoft SharePoint and BizTalk Server is a plus.
• HTML, JavaScript and DHTML Programming

2. Microsoft Dynamics GP Support

Between 1 and 4 Years of professional experience covering:

• Dynamics GP Technical Knowledge (Installation, Configuration, Troubleshooting)
• SQL Server is a must.
• Crystal Reports knowledge is recommended.
• .Net Development experience is a plus.

3. Senior SharePoint 2010 Developer

Minimum 4 years of professional experience covering:

• Experience with SharePoint Portal Server: 2007, specifically 2010
• 4+ years of developing SharePoint web parts and/or components
• 3+ experience developing workflow (SharePoint workflow foundation) .
• 3+ years’ experience in Performance Point dashboard designer
• 3+ years of experience in developing Interactive dashboards, answers reports and analytical
• Experience developing scorecards
• Experience with data warehousing concepts
• Experience with OLAP
• Expert level of knowledge (4+ years’ experience) with various languages/tools such as C#, ASP.NET, HTML, CSS, XML, VBScript, Classic ASP, and JavaScript
• 3+ years of application design and implementation
• 4+ years’ experience with web application / data base integration
• 3+ years of application design and implementation
• 3+ years’ experience with SQL Server database, stored procedures, triggers, DTS, schema design and query optimization is a plus
• Strong object-oriented programming and design skills (Java, C++, Objective-C) are desired
• Experience using and building web UI frameworks.
• Experience in applying design using SharePoint designer 3+ years administrating and configuration of SharePoint

---

Regards,

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

Dynamics GP 2013 MVP

In April 1st I got a mail from Microsoft telling me that I got a new MVP for 2013! That was a great news to hear but wanted to postpone publishing the announcement before getting the below!

Installing SQL Failover Clustering Without SAN Storage – Windows 2008 R2

It always been hard to test out the failover clustering as it requires SAN Storage device to be connected and requires many servers for testing.

This morning I have seen an interesting article in the community the Microsoft has released “Microsoft iScsi Software Target” free of charge, by using the utility you can create a storage and share it across multiple servers, “fooling” the windows to think that a SAN Storage is available.

Following the above, I been able to install failover clustering for testing over one single machine without any other equipments! I used Hyper-V to create 3 Windows 2008 R2 Servers and 1 Virtual Hard Disk Drive and worked out a fully functioning test environment, below what you need if you like to test this one your machine:

1. Over a test server, install Windows 2008 R2 and activate the Hyper-V.

2.  Create 3 virtual machines, one for SQL Server Node1, and the second for the second failover node, and the third one to act as storage server.

3. Create 1 virtual hard disk to represent your SAN Storage.

When you gather these, click on the following tech-net article that details all what you need:

http://blogs.technet.com/b/mghazai/archive/2009/12/12/hyper-v-guest-clustering-step-by-step-guide.aspx

Hope that this helps.

Regards,

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

LinkedIn

As many of you might notice that Linked In is celebrating their 200,000,000 users and started sending thanks for people who help spreading their name! They have ranked their users per the the viewing of their profile, so you either be one of the top 1% most viewer profiles, 5% or 10%.

I made the 5% List!

Regards,

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

How to Track unused Journal Numbers

I got an interesting question from one of the GP users requesting a list of unused Journal Number, therefore I decided to scratch my head and write this query, below will help:

SELECT DISTINCT NUMBER FROM MASTER..spt_values WHERE NUMBER BETWEEN
(SELECT MIN(JRNENTRY) AS FROMNUMBER FROM (SELECT DISTINCT JRNENTRY FROM GL20000) AS JOURNALS)
AND
(SELECT MAX(JRNENTRY) AS TONUMBER FROM (SELECT DISTINCT JRNENTRY FROM GL20000) AS JOURNALS)
AND
number NOT IN (SELECT DISTINCT JRNENTRY FROM GL20000)

Happy tracking…!

Regards,

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

Dynamics GP Excel Paste V 4.0

We have finally released and uploaded a new version for the Excel Paste, this one has the following coverage:

1. The major move was the ability to integrate into multiple products, like combining the GL Transaction Entry with the Analytical Accounting.

2. Ability to Paste into modified forms and additional fields.

3. We have added a link between the defined Products and the actually installed products to avoid annoying errors if you have missed one product in your workstation.

4. Added an option to handle prompts or disable the handling, to avoid any annoying messages during the paste operation.

5. For users who are running behind firewalls, they used to have Slowness during the login to GP, we have fixed this by moving the online validation to be through the webservices first and then direct database connection.

6. We had many “Minor Bugs” that were reported, all been resolved.

Regards,

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

Dynamics GP 2013 Demo Image

Yes it has been released, Microsoft team is doing everything fast in relation to GP 2013! take a look into the following article:

http://blogs.msdn.com/b/gp/archive/2013/01/15/where-s-the-demo-image-here-today.aspx

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

Developing Microsoft Dynamics GP Business Applications

Do you want to learn everything related to Dynamics GP development? Dexterity, VBA, .NET, Extender and everything for just a 30$?!? I found these all in one book I reviewed written by Leslie Vail, it is Developing Microsoft Dynamics GP Business Applications

Worth to take a look!

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

Dynamics GP - Change a form field to “Required” field

I got many requests from my customers on how to make this happens and I used to create a package and send it to the users for import.

However with the new Licensing structure, everybody owns the modifier as a part of the starter pack! Therefore anyone can do the following steps to activate this:

1. Open the Form.
2. Go to Tools>> Customize >> Modify Current Window
3. Select the field and switch “Required” to true as shown below:

Hope that this helps!

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

Dynamics GP 2013 Business Portal

Customers who are running Business Portal cannot upgrade to GP 2013 till march as the Business Portal has not yet released, take a look into the announcement from Inside Microsoft Dynamics GP Blog:

http://blogs.msdn.com/b/gp/archive/2013/01/08/microsoft-dynamics-gp-2013-business-portal-update.aspx

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