Microsoft Dynamics Corner

GP2010 R2 Additional SQL Reporting Services Metrics Feature

This feature will add additional out of the box dashboard pages in Business Portal and additional SQL Reporting Services metrics to display on these pages. The new metrics are also reusable in the Business Analyzer and Dynamics GP home page as well.  For more information about this feature visit this link click here.

Microsoft Dynamics-GP Smart List Performance

Do you have smart list that take for ever to export to Excel?  Sometimes even a smart list with just a few thousand rows can take a few minutes to export.  Do that a number of times a day and you can be really wasting some time.  Well, there is a undocumented DEX.INI setting that will dramatically improve the performance of exporting to Excel.

If you enter the following statement in your DEX.INI, SmartlistEnhancedExcelExport=TRUEyou will see some great improvements.  Open the DEX.INI file with Notepad.  The DEX.INI file is located in the Data folder in your Dynamics GP directory.  Add this line SmartlistEnhancedExcelExport=TRUE at the end of the file and save the file.  If you are already logged into Dynamics-GP exit and launch Dynamics-GP again.  You will now be enjoying a faster export to Excel from Smart List.

KTL Solutions, is a leading provider consulting and development services for Microsoft Dynamics-GP.  With over 11 years of experience providing solutions for organizations of all sizes.

Index Fragmentation can cause performance issues

Keeping your SQL Server turned requires that you perform routine maintenance on a regular basis.  Some of those routines can be automated through a maintenance plan most importantly the shrinking and reorganizing of the database files.  What sometimes is missed is the index fragmentation.  If your indexes are fragmented it can cause performance issues on how the data searches through the leafs to get to the data it needs.  I have included an excellent script you can run that will de-frag your indexes that have a 30% or more fragmentation.  You can adjust the variables in the script to de-frag indexes with less than 30% fragmentation or you can just view the results.  Remember if an index is not heap the fragmentation doesn't matter, if the table has a low amount of page counts.

-- Specify your Database Name
USE DatabaseName -- Change to the database you want to defrag
GO

-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR(128)
DECLARE @execstr VARCHAR(255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag decimal
DECLARE @maxfrag decimal
DECLARE @IdxName varchar(128)
DECLARE @ViewOnly bit

-- Set to 1 to view proposed actions, set to 0 to Execute proposed actions:
SET @ViewOnly=1

-- Decide on the maximum fragmentation to allow for.
SET @maxfrag = 30.0

-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))
+'.'+CAST(TABLE_NAME AS VARCHAR(100))
AS Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the temporary table.
if exists (select name from tempdb.dbo.sysobjects where name like '#fraglist%')
drop table #fraglist

CREATE TABLE #fraglist (
ObjectName CHAR(255),
ObjectId INT,
IndexName CHAR(255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity decimal,
BestCount INT,
ActualCount INT,
LogicalFrag decimal,
ExtentFrag decimal)

-- Open the cursor.
OPEN tables

-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor.
CLOSE tables
DEALLOCATE tables

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor.
OPEN indexes

-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @IdxName

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ViewOnly=1)
BEGIN
PRINT 'WOULD be executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( LOB_COMPACTION = ON ) - Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
END
ELSE
BEGIN
PRINT 'Now executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( LOB_COMPACTION = ON ) - Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
SELECT @execstr = 'ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( LOB_COMPACTION = ON )'
EXEC (@execstr)
END

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @IdxName
END

-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table.
DROP TABLE #fraglist
GO

Tim Lally has been working with SQL Server and ERP systems since 1996.  His experience spans over 25 years and includes developing systems using a variety of development languages and systems, for businesses around the world.

SQL Server Versions

Anyone needing a reference to the SQL Server versions inlcuding hotfixes and the related tech document from Microsoft this is a great web site to keep up to date.  They are pretty good at keeping current.

http://sqlserverbuilds.blogspot.com/

KTL Solutions is a provider of .Net development services, Microsoft Dynamics implementation and training, business process review.  Dynamics-GP, Dynamics-CRM, and Dynamics-SL sales, service and support.

GP2010 R2 has been released

The Microsoft Dynamics GP 2010 R2 update expands key areas for Microsoft Dynamics GP 2010, including the following:

•	Enhancing insight into role-specific information through an improved user interface and personalized dashboards, quickly delivering information that’s relevant to the actions people are taking or the information they are viewing
•	Making it easier to complete tasks with improved approval workflow and electronic signature capabilities, helping people save time while managing compliance and helping to reduce fraud
•	Extending connections through Fact Boxes and Action Shortcuts, which are now connected with Microsoft Lync 2010 instant messaging windows, bringing information about the people who are chatting and their companies to the user’s fingertips Attached is the whats new for GP2010.  New items for GP2010 R2 are marked.Download WhatsNew GP2010 R2

I have also attached the install guide for GP2010 R2 for your reference.  This is a fairly significant update and you should consider updating to this version if you are currently on GP2010.

Download 11.0SP2_install_guide_ENUS

Dynamics-GP Long Integer Out of Range

A client recently called because they could not post a check batch because of the following error message "Long Integer Out of Range".  The user could print the checks but could not post the checks and the batch was stuck in batch recovery.  The client is currently using Dynamics-GP v10.

After doing a little research I found that the document date they entered for a credit and a voucher that was being paid by a check had a date in the future that was an invalid date.  The year entered was actually 3028.  If you are wondering how it got posted, they post using the GL date from the batch, so the document date is more or less irrelevant until the check posting.

What I did to solve it was update transactions in the PM10300, PM20100, PM20000, PM10200 tables where the DOCDATE was incorrect.

Microsoft Dynamics GP12

Looks like Dynamics GP will finally go browser base with the release of GP12.  I recently viewed a demo mate preview of GP12 and it looked pretty neat.  It obviously was very early in the development but the interface was very close to what you now find in GP2010.  Here is a blog entry of some more detailed information.  I am working on uploading the preview for others to see.  Click here for the block link on more information.

Microsoft Dynamics Enhancement Plans

For those of you that rely on your Dynamics Var to make sure you keep up with your enhancement renewal dates I thought I would pass along this update regarding expired enhancement fees.

In the past there use to be a grace peiod after the expiration of your enhancement plan before you were penalized. Microsoft does not allow that anymore and the first day after your expiration you will start incurring penalties.

If your enhancement plan is expired the math is a little different. It use to be based off of the list price but now with Business Ready Licensing they have changed how they calculate the enhancement. Say you are on Business Ready Licensing BE. You own a 10 user system with a List price of 22,500.00. Common sense would say the catch up would be based on the 22,500.00 price.

Under the new rules Microsoft will now add in a Light user charge and a Managerment Reporter charge for each user essentially adding 10,000 for the Management reporter licensing and 1950.00 for the Light user licensing. The thought behind this is when you purchased the software you received a deal because they bundled it together as a freebe. So when your enhancement expires it isn't a freebe and your list price changes.

So pay attention and if you are someone that generally doesn't let the enhancement expire you will want to make sure you pay before the due date.

Tim Lally has over 25 years experience working with Dynamics-GP and before that Great Plains. Tim Lally has been involved in over 500 implementations and has developed over 1000 customizations for Dynamics-GP.

Configuring SQL Server 2008 on Hyper V

I found a good white paper regarding setting up SQL Server 2008 for Hyper V.  It provides some detail information that I think anyone that is considering virtualizing their SQL Server to take a look at this.  I am sure there is a ton of information out there but I found this information very good.

Download SQL2008inHyperV2008

2011 W-2 and Benefit Reporting for Dynamics-GP Payroll

WASHINGTON — The Internal Revenue Service today issued interim guidance to employers on informational reporting on each employee's annual Form W-2 of the cost of the health insurance coverage they sponsor for employees. The IRS is also requesting comments on this interim guidance. The IRS emphasized that this new reporting to employees is for their information only, to inform them of the cost of their health coverage, and does not cause excludable employer-provided health coverage to become taxable; employer-provided health coverage continues to be excludable from an employee's income, and is not taxable.

The Affordable Care Act provides that employers are required to report the cost of employer-provided health care coverage on the Form W-2. Notice 2010-69, issued last fall, made this requirement optional for all employers for the 2011 Forms W-2 (generally furnished to employees in January 2012). In today's guidance, the IRS provided further relief for smaller employers (those filing fewer than 250 W-2 forms) by making this requirement optional for them at least for 2012 (i.e., for 2012 Forms W-2 that generally would be furnished to employees in January 2013) and continuing this optional treatment for smaller employers until further guidance is issued.

Using a question-and-answer format, Notice 2011-28 also provides guidance for employers that are subject to this requirement for the 2012 Forms W-2 and those that choose to voluntarily comply with it for either 2011 or 2012. The notice includes information on how to report, what coverage to include and how to determine the cost of the coverage.

The 2011 Form W-2, prior IRS Notice 2010-69deferring the reporting requirement for 2011, and Notice 2011-28containing the new guidance are available on IRS.gov.