Microsoft Dynamics GP 2013 What's New

You can download the complete document on Microsoft GP 2013 What's new here.

A few of my favorite new features in Dynamics GP 2013 are as below.

• Web Access—Provide employees easy access to Microsoft Dynamics GP 2013 with the new web client.  They can now connect and contribute no matter where they are, via PC or mobile device.
  
• Flexible deployment options—Choose an on premise, cloud or hybrid deployment model to give your business the tools it needs to adapt quickly, while keeping IT costs under control.
  
• Application Service Console—Achieve flexibility without sacrificing security, the Application Service Console allows for the management of active Dynamics GP 2013 web sessions, the termination of inactive ones and the ability to control product configuration by user.
  
• 64-bit Compatibility.

Management Reporter 2012 - How to Clear Report Queue?

It is often noted that after generating report Management Reporter 2012 keeps the logs of the same and during the course these are piled up and at time you need to clear these report queues, below steps will help you to clear the queues.

1. Log in to SQL Management Studio with admin privilege.
   
   
2. Run the below script against Management Reporter Database
   USE ManagementReporter
   DELETE RepositoryMessage
   


3. Open Management Reporter and Check if this has cleared all the Report Queue.
   Tools >> Report Queue Status.
   

Management Reporter 2012 – Fixing Sample Reports to Work with Dynamics GP 2013

Issue : It has been noticed that when you install sample reports in MR 2012 and when you generate report you notice that no report comes up and you get error message page stating to correct account dimension or report date or reporting tree.

Explanation : This is because there is a mismatch between the default settings of the sample reports and Microsoft GP whereby then Link to Financial Dimensions is broken if you observer closely when you open Row Definition. The cause of the problem is that the segments in the Reports are defined as Segment1, Segment2 and Segment3 but in Microsoft Dynamics GP they are called Division, Account and Department.

Solution :  Below is the steps to install and correct the above noted issue.

• Install Management Reporter Demo Reports for GP by downloading from the link belowMicrosoft GP Demo Reports for Management Reporter 2012
  Note: The above link also explains you to install demo reports into MR

• Run the below scripts on the Management Reporter Database in the SQL Management Studio

             UPDATE ControlTreeCriteria SET DimensionCode = 'Division' 
         WHERE DimensionCode = 'Segment1'

       UPDATE ControlRowCriteria SET DimensionCode = 'Account' 
         WHERE DimensionCode = 'Segment2'

       UPDATE ControlTreeCriteria SET DimensionCode = 'Department' 
         WHERE DimensionCode = 'Segment3'

             Note: Take a backup of the Management Reporter database before you execute the above script.

• Navigate to Tools >> Refresh Cache Financial Data under Management Reporter Designer and complete the process.

• Verify any Row Definition and you should be able to see the accounts are fixed under the column “Link to Financial Dimensions”

Now run the report, you should be able to successfully generate the reports.

Why doesn't my checkbook balance in GP match my GL balance?

A common challenge with Dynamics GP (Great Plains) is keeping the checkbook balance in sync with the general ledger (G/L) balance of the matching g/l bank account.   This article will help you keep the checkbook balance equal to the general ledger balance. Overview    This issue happens a lot partly because users do NOT understand that the checkbook is a subledger of the general ledger in Dynamics GP.   So if you do any journal entry in the general ledger that affects a bank balance (i.e. the checkbook), you will cause the checkbook to be different than the general ledger balance.  That is the most common mistake that happens. But this issue also happens because of a poor software design by Microsoft.    There exists a system control that is supposed to help you keep the checkbook balance equal to the general ledger balance, but it is an optional system control that people don't know exists.   In the setup of your general ledger bank accounts (go to Financial > Cards > How To Fix It If you notice that your checkbook balance in Dynamics GP does NOT match your general ledger balance, here's what to look for. Ways to resolve it: 1)   Did you do any regular journal entries that affected the cash account?        Use smartlist to look for this. 2)  Do you have any undeposited receipts?       When a receipt is entered, it increases your general ledger bank account balance but the checkbook does NOT get increased until you enter the deposit. 3)  Are there transactions coming from other modules that are bypassing the checkbook?       There can be a variety of things that are examples of this: a)   We had a client that was creating a debit memo in the receivables module for customer payments that bounced (NSF checks).    So in the debit memo they were crediting the bank account g/l account.    However, the checkbook would not know about this kind of transaction. b)   Did you book a transfer between 2 bank accounts but you didn't use the Bank Transfer window?   If you do any kind of bank transaction (such as an increase or decrease adjustment), and you credit or debit another bank account in the g/l debits and credits, this will mess up your checkbook balance.    Because the checkbook for the other bank account involved will not know about the adjustment you did.   So all transfers between 2 bank accounts have to be done using the Bank Transfer window in Dynamics GP. 4)   Did someone enter a bank transaction and accidently change the g/l account? When you start a bank transaction in Dynamics GP, and you pick a checkbook, the system then defaults the general ledger cash account to the general ledger cash account associated with that checkbook.    However, you can accidently override this general ledger account.   So, if you do a bank transaction, and then you change the account to the wrong account, we have noticed that the checkbook balance doesn't get updated.   Of course, the general ledger is also wrong.   But so too will the checkbook balance.

SQL : Useful DateTime Queries

----Today

SELECT GETDATE() 'Today'
----Yesterday
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
----First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
----Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
----First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
----Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'
----First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month'
----Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month'
----First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'
----Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'
----First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year'
----Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year'
----First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'
----Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year'
----First Day Of Current Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate()), 0)'First Day Of Current Quarter'
----Last Day Of Current Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate())+1, 0)) 'Last Day Of Current Quarter'
----First Day Of Prior Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate())-1, 0) 'First Day Of Prior Quarter'
----Last Day Of Prior Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate()), 0)) 'Last Day Of Prior Quarter'

What are SSRS, SSIS and SSAS

Let me give a brief introduction of SSRS, SSIS and SSAS as you can get more details on these topic if you google it.

SSRS – SQL Server Reporting Services
                Microsoft SQL Server Reporting Services (SSRS) delivers enterprise, Web-enabled reporting functionality so you can create reports that draw content from a variety of data sources, publish reports in various formats, and centrally manage security and subscriptions.
                Microsoft SQL Server Reporting Services enables organizations to transform valuable enterprise data into shared information for insightful, timely decisions at a lower total cost of ownership.
SQL Server Reporting Services is a comprehensive, server-based solution that enables the creation, management, and delivery of both traditional, paper-oriented reports and interactive, Web-based reports. An integrated part of the Microsoft Business Intelligence framework, Reporting Services combines the data management capabilities of SQL Server and Microsoft Windows Server with familiar and powerful Microsoft Office System applications to deliver real-time information to support daily operations and drive decisions.

SSIS – SQL Server Integration Services

            It is a Data Warehousing Tool; SSIS is one of the key features introduced in SQL Server and is the new DTS (Data Transaction Services) platform. Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

Integration Services includes a rich set of built-in tasks and transformations; tools for constructing packages; and the Integration Services service for running and managing packages. You can use the graphical Integration Services tools to create solutions without writing a single line of code; or you can program the extensive Integration Services object model to create packages programmatically and code custom tasks and other package objects.

SSAS – SQL Server Analysis Services

               Microsoft SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting you design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services lets you design, create, and visualize data mining models that are constructed from other data sources by using a wide variety of industry-standard data mining algorithms.

Assign All Items to a given Vendor
 ---------------------------------------------------------------- -- Assign All Items to a all Vendors -- Code by Venugopal G A -- mail me for any queries venuasg@gmail.com / venuasg@yahoo.com ---------------------------------------------------------------- DECLARE @VENDORID VARCHAR(30) DECLARE @ITEM VARCHAR(100) DECLARE @ITEMDESC VARCHAR(100) DECLARE VENDOR CURSOR FOR SELECT VENDORID FROM PM00200 OPEN VENDOR FETCH NEXT FROM VENDOR INTO @VENDORID WHILE (@@fetch_status <> -1) BEGIN DECLARE ITEMMASTER CURSOR FOR SELECT ITEMNMBR, ITEMDESC FROM IV00101 OPEN ITEMMASTER FETCH NEXT FROM ITEMMASTER INTO @ITEM, @ITEMDESC WHILE (@@fetch_status <> -1) BEGIN INSERT INTO [IV00103] ([ITEMNMBR],[VENDORID],[ITMVNDTY],[VNDITNUM],[VNDITDSC]) VALUES(@ITEM,@VENDORID,1,@ITEM,@ITEMDESC) FETCH NEXT FROM ITEMMASTER INTO @ITEM, @ITEMDESC END DEALLOCATE ITEMMASTER FETCH NEXT FROM VENDOR INTO @VENDORID END DEALLOCATE VENDOR

Issue while deploying SSRS Reports or Business Intelligence reports for GP 2010 R2

As the Process of Deploying SSRS reports or Business Intelligence Reports in GP 2010 R2 has been enhanced and it has been noticed that the system generates an error which is as below, however the error itself has a solution for it  

Solution

1)      Go the Microsoft SQL Reporting Server installation folder

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer

2)       Look for web.config file and open it in text editor.

3)      Find the text executionTimeout="9000"  and replace with
        executionTimeout="9000" maxRequestLength="20690"
4)      Save the file

5)      Run the Business intelligence reports deployment, it should go smoothly now      

Enjoy, Get back to find more tips

Microsoft Dynamics GP 2010 R2 is Now Available

Good News, Latest Release of GP 2010 R2 Over 50 additional and interesting stuffs enhanced, my personal intrest was in Project Accounting where i can find the reports i needed.

Below is the link for all the features Microsoft Dynamics GP 2010 What's New

https://mbs.microsoft.com/partnersource/newsevents/news/MSDYGP2010R2_R2nowavailable.htm

Year End Process in Microsoft GP

This article explains the year end process in Microsoft GP, The posting procedure and the closing procedure for the following modules must be performed in the following order.

1. Inventory
2. Receivables
3. Payables
4. Fixed Assets
5. Analytical Accounting
6. General Ledger

It is highly recommended before proceedings with year-end process in GP take a backup of the company and the Dynamics database, detailed steps for each module are explained in the Microsoft KB articles which can be accessed through Partner/Customer source, but the simple steps would be as below (all the steps needs to be repeated for all the above said modules)
Steps to do the year-end

Year End Procedures – Inventory
KB Article – Inventory Year end Process

1. Take a Company Database backup.
2. Post all the transaction for the module for the year.
3. Reconcile Inventory Quantities.
4. Complete a Physical Inventory count and post any adjustments.
5. Print any additional reports you'll need for planning or for your permanent records.
6. Make a backup of all Company data. (Go to Microsoft Dynamics GP >> Maintenance >> Backup).
7. Close the year.
8. Close the fiscal periods for the Inventory Series (optional).
9. Make a final backup.
   
   

Year End Procedures – Receivables
KB Article – Receivables Year end Process

1. Take a Company Database backup.
2. Post all the transaction for the module for the year
3. Make a backup of all Company data. (Go to Microsoft Dynamics GP >> Maintenance >> Backup).
4. Close the fiscal year in the Receivables Year-End Closing window
5. Close all fiscal periods for the Sales series (optional)
6. Make a final backup.

Year End Procedures – Payables
KB Article – Payables Year end Process

1. Take a Company Database backup.
2. Post all the transaction for the module for the year.
3. Print the Aged Trial Balance with Options Report to show the status of each Vendor Account.
4. Print the Vendor Period Analysis report.
5. Close All in the Payables Year-End Closing window
6. Close all fiscal periods for the Purchasing series (optional).
7. After you have completed the year-end closing procedures for all Sales and Purchasing modules, close the sales tax periods for the year.
8. Make a final backup.

Year End Procedures – Fixed Assets
KB Article – Fixed Assets Year end Process

1. Take a Company Database backup.
2. Post all the transaction for the module for the year.
3. Enter in all activity for the current fiscal year. This includes additions, changes, transfers and retirements.
4. Run depreciation on all assets through the last day of the current fiscal year.
5. Process the GL Interface (GL Posting) routine by going to Tools >> Routines >> Fixed Assets >> GL Posting.
6. If required print FA reports.
7. Verify that the Quarters are set up correctly for all fiscal years. Go to Tools >> Setup >> Fixed Assets >> Quarters.
8. Perform the Fixed Asset Year-End Close routine by going to Tools >> Routines >> Fixed Assets >> Year End.
9. Make a final backup.

Year End Procedures – General Ledger
KB Article – General Ledger Year end Process

1. Compete the posting procedure and the closing procedure (in the following order) for Inventory, Receivables, Payables, and Fixed Assets, if applicable.
   NOTE: Year-end procedures for Payroll are only performed at the end of the calendar year.
2. Post the final adjusting entries in the General Ledger module. If you must make any adjusting entries to allocate revenue, expenses, or depreciation to the year that you are closing, use the Transaction Entry window or the Quick Journal Entry window. If you wish to track your adjustments separately, use the Second Closing Period feature.
3. Use SmartList to verify the posting type of each account. Go to Microsoft Dynamics GP >> SmartList >> Accounts. By default, only the first 1,000 accounts will be displayed. If you have more than 1,000 accounts, double click on the box at the bottom where it says "First 1,000 accounts with no search criteria" and increase the maximum records (in red at bottom). Click the header of the Posting Type column to sort by posting type. Scroll down to the change from Balance Sheet to Profit and Loss. Verify that the last Balance Sheet and the first Profit and Loss accounts are correct. Note that Great Plains treats all unit and allocation accounts as Balance Sheet, but the category will be zero.
4. Go to Reports >> Financial >> Accounts. Select All Accounts, then New. Type "all accounts" in the option field. If you are using 8.0 or later, put a check in the Inactive Accounts box. Select Destination >> OK >> Print. If you want to bring the balance forward, the account should be designated as Balance Sheet; if you prefer to close to a retained earnings account, the posting type must be set to Profit and Loss. You can make changes in the Account Maintenance window.
5. Close the last period of the fiscal year (optional). Use the Fiscal Periods Setup window. If you use FRx, keep one period in the most recent historical year open.
6. Perform file maintenance on the Financial Series group of modules. Go to Microsoft Dynamics GP >> Maintenance >> Checklinks. Insert all of the financial series. Print the report to the screen.
   Go to Tools >> Utilities >> Financial >> Reconcile. Reconcile the year you are closing.
7. Verify the setup in the General ledger setup window. If you want to keep historical records, you must click to select the Accounts check box and the Transactions check box in the Maintain History area of the General Ledger Setup window.
8. Make a backup. (Go to Microsoft Dynamics GP >> Backup).
9. Print a final detailed trial balance.
10. Print any required year-end financial statements, e.g. balance sheet, profit and loss statement, statement of cash flows, and statement of retained earnings.
11. Set up a new fiscal year using the Fiscal Periods Setup window. Go to Tools >> Setup >> Company >> Fiscal Periods.
12. Close the fiscal year. Click Tools >> Routines >> Financial >> Year-End Closing. Specify an account in the Retained Earnings Account field (this account is where the year's profit or loss is closed to; if you want to distribute the retained earnings for the year to more than one account, you can specify an allocation account or a specific account segment). Specify the number that you want to use as the first journal entry number for the next fiscal year in the Starting Journal Entry field. Click Close Year to start the routine. If the progress window hangs at 50%, don't reboot. It may take some time for it to close each account to retained earnings. As long as your hard drive is processing, let it continue. When the year-end closing routine is complete, the Year-End Closing Report is printed. This report lists the accounts that were closed and the transactions that were created to close those accounts. The Year-End Closing Report is part of the audit trail; save it for your company's permanent records. The Year-End Closing Report cannot be reprinted.
13. Close all fiscal periods for all series using the Fiscal Periods Setup window (optional).
14. Adjust budget figures for the new year and print financial statements. You can adjust your budget using Excel-Base Budgeting, Budget Maintenance, or Single-Account Budget Maintenance. Print the Profit and Loss Statement to verify that profit and loss accounts were closed to the retained earnings account. Print the Balance Sheet to verify that balance sheet accounts indicate that the balances were brought forward. If you are using Advanced Financial Analysis to print your financial statements, you must update the report layout to reflect the current fiscal year. Click Reports >> Financial >> Advanced Financial. Select the financial statement from the Reports list | Open >>Layout. Double-click the first column heading. If the column type is set to Period Range, Year-to-Date, or Variable Year-to-Date, select the current fiscal year in the Year list | OK. Repeat for each column of each report.
15. Make a final backup.   

Microsoft GP .ini Settings

Some useful .ini settings in Microsoft GP, you can find the full list here. Some settings are not not recommended however you can you these in test envoirnment. 

SQL Script to find missing or deleted Journal No in Microsoft GP

Script
 ------------------------------------------------------------------------- -- Find Missing or deleted Journal Entry No in Microsoft GP -- Code by Venugopal G A -- mail me if you need query for other type of transactions or for any -- queries venuasg@gmail.com ------------------------------------------------------------------------- If Object_Id('tempdb..#tempJV') is Not Null Drop table #tempJV create table #tempJV ( JVno int, MJV int identity(1,1) ) insert into #tempJV (JVno) (select distinct JRNENTRY from GL20000 ) select wg.MJV as id_is_missing from #tempJV wg left join #tempJV ti on wg.MJV = ti.JVno where ti.JVno is null order by wg.MJV

SQL Script to Compare Bank Subledger balance to corresponding GL account balance

Script
 ---------------------------------------------------------------------------- -- Compare Bank Subledger balance to corresponding GL account balance in GP. -- Code by Venugopal G A -- mail me for any queries venuasg@gmail.com ---------------------------------------------------------------------------- DECLARE @CHECKBOOKID VARCHAR(30) SET @CHECKBOOKID = 'FIRST BANK' -- CHANGE THIS TO YOUR CHECKBOOK ID --Set Bank Type and get Bank Balance Select 'Bank' as SubType, CURRBLNC as SubBal, 0 as GLBal Into #SubtoGLCompare From CM00100 Where Chekbkid=@CHECKBOOKID /*Get the GL total for this Account. Change the account below or optionally add additional actnumbr parameters to tighten or broaden the account*/ INSERT #SubtoGLCompare (SubType,SubBal,GLBAL) SELECT 'Bank' as SubType, 0 as SubBal, SUM(Perdblnc) AS GLBal FROM GL11110 WHERE actnumbr_2 = '1100' -- Change this Select SubType, sum(Subbal) as SubBal, Sum(GLBal) as GLBAL, sum(SubBal)-Sum(GLBAL) as Difference, 'Difference is Subledger minus GL.' as Instructions from #SubtoGLCompare Group by SubType Drop Table #SubtoGLCompare

SQL Script to assign all items to all sites in Microsoft GP

Assign All Items to ALL Sites
 -------------------------------------------------------- -- Assign All Items to ALL Sites -- Code by Venugopal G A -- Below script will assign all the available inventory item -- to all available sites if they are not assigned already. -- mail me for any queries venuasg@gmail.com -------------------------------------------------------- DECLARE @ITEM varchar(100) DECLARE @LOCATION varchar(100) DECLARE @ITEMAlreadyExist int DECLARE ITEMMASTER CURSOR FOR SELECT ITEMNMBR FROM IV00101 OPEN ITEMMASTER FETCH NEXT FROM ITEMMASTER INTO @ITEM WHILE (@@fetch_status <> -1) BEGIN DECLARE SITE CURSOR FOR SELECT LOCNCODE FROM IV40700 OPEN SITE FETCH NEXT FROM SITE INTO @LOCATION WHILE (@@fetch_status <> -1) BEGIN SELECT @ITEMAlreadyExist = isnull(count(*), 0) FROM IV00102 where ITEMNMBR = @ITEM and LOCNCODE = @LOCATION if @ITEMAlreadyExist = 0 BEGIN INSERT INTO IV00102 VALUES(@ITEM,@LOCATION,'',2,'',0,0,0,0 ,'01/01/1900','','01/01/1900',0,0,0,0,0,0,0,0,0,0,0,0 ,'01/01/1900','01/01/1900','01/01/1900','01/01/1900',0,'' ,'','',1,0,0,1,0,0,1,2,0,0,0,0,0,0,0,1,0,0,0,3,0,0,0,'' ,'','','','','','','',1,1,'',1,1,0,1,1,1,0,0,0,0,0) END FETCH NEXT FROM SITE INTO @LOCATION END DEALLOCATE SITE FETCH NEXT FROM ITEMMASTER INTO @ITEM END DEALLOCATE ITEMMASTER

SQL Script to assign all Items to a given Vendors

Assign All Items to a given Vendor
 -------------------------------------------------------- -- Assign All Items to a given Vendor -- Code by Venugopal G A -- mail me for any queries venuasg@gmail.com -- Change the vendor variable to your vendorid -------------------------------------------------------- DECLARE @VENDORID VARCHAR(15) DECLARE @ITEM VARCHAR(100) DECLARE @ITEMDESC VARCHAR(100) SET @VENDORID = 'ACETRAVE0001' DECLARE ITEMMASTER CURSOR FOR SELECT ITEMNMBR, ITEMDESC FROM IV00101 OPEN ITEMMASTER FETCH NEXT FROM ITEMMASTER INTO @ITEM, @ITEMDESC WHILE (@@fetch_status <> -1) BEGIN INSERT INTO [IV00103] ([ITEMNMBR],[VENDORID],[ITMVNDTY],[VNDITNUM],[VNDITDSC]) VALUES(@ITEM,@VENDORID,1,@ITEM,@ITEMDESC) FETCH NEXT FROM ITEMMASTER INTO @ITEM, @ITEMDESC END DEALLOCATE ITEMMASTER