GP 2013: Why are my SmartLists not formatted when exported to Excel?

Just recently I have been seeing a number of questions around Microsoft Dynamics GP 2013 Smartlist exports to Microsoft Office Excel not being formatted correctly - let's rephrase, the correct word to use here is "nicely". The issues usually involve leading zeroes being dropped (in some cases) and currency amounts being exported with 5 decimals and no currency symbol.

Exported Payables Transactions smart list

To make a one swipe statement here, this behavior IS NOT a product bug - despite the inconvenience.

With the introduction of the Web Client in Microsoft Dynamics GP 2013, the development team needed the ability improve the performance of Smartlist exports over the browser. To gain this dramatic improvement in performance (which is also experienced when using the rich client), exported Excel smart lists were voided of formatting.

There's an undocumented Dex.ini switch that provides limited formatting to Smartlists, thus maintaining export performance:

SmartlistEnhancedExcelExport=TRUE

You can find more information on this Dex.ini switch here:

Undocumented DEX.INI switch cuts down SmartList export times to Microsoft Office Excel

If you are not going to deploy Web Client, then this should be fine as a workaround. However, this switch causes the Web Client to error out with the following message when attempting to export to Excel:

Web Client error exporting smart list to Excel with SmartlistEnhancedExcelExport=TRUE 

So there you have it!

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

SmartList Builder: Sales Summary By Quarter

Just recently I was approached by a partner wanting to leverage the info in the Receivables Summary table (RM00104) to build a smart list showing sales summary by quarter. The smart list needed to display the following information:

Customer Number	Year	Q1	Q2	Q3	Q4
AARONFIT0001	2013	0.00	4224.67	0.00	10277.37
AARONFIT0001	2014	21468.68	0.00	0.00	0.00
AARONFIT0001	2016	12164.15	0.00	0.00	0.00
AARONFIT0001	2017	4945.70	5809.40	0.00	0.00
AARONFIT0001	2018	0.00	0.00	877.50	0.00

As is customary with these types of request, the best bet is to create a SQL Server view that can then be leveraged from Smartlist Builder. By using a SQL Server view, we can leverage some cool T-SQL set-based data manipulation capabilities.

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
SELECT custnmbr as [Customer Number], year1 as [Year], isnull([1], 0.00) as Q1, isnull([2], 0.00) as Q2, isnull([3], 0.00) as Q3, isnull([4], 0.00) as Q4
FROM (
  SELECT custnmbr, year1, datepart(qq, datefromparts(year1, periodid, 1)) as qtr, smrysals 
  FROM RM00104
  WHERE histtype = 1
) p
PIVOT 
( SUM (smrysals) FOR qtr IN ([1], [2], [3], [4])
) AS pvt
GO

The above query, produces the results required, but what makes it happen is the beauty of the PIVOT operator. In addition, if you are using SQL Server 2012, you can take advantage of the DATEFROMPARTS function to simplify the conversion of the date parts (year1 and periodid) to a full date to then calculate the quarter with the DATEPART function.

If you are using SQL Server 2008 or earlier, the following query should do:

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
SELECT custnmbr as [Customer Number], year1 as [Year], isnull([1], 0.00) as Q1, isnull([2], 0.00) as Q2, isnull([3], 0.00) as Q3, isnull([4], 0.00) as Q4
FROM  (
  SELECT custnmbr, year1, datepart(qq, CAST(CAST(year1 AS varchar) + '-' + CAST(periodid AS varchar) + '-' + CAST(1 AS varchar) AS DATETIME)) as qtr, smrysals 
  FROM RM00104
  WHERE histtype = 1
) p
PIVOT 
( SUM (smrysals) 
  FOR qtr IN ([1], [2], [3], [4])
) as pvt
GO

Note that the above version of the query uses the CAST function to determine the date.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

DynamicsWorld UK Top 100 List

DynamicsWorld has released its 2013 Microsoft Dynamics Top 100 Most Influential People list and I am really excited to see my name in position 42. I have to say, it's an honor to share the podium with some fellow MVPs, as follow:

20. Mark Polino
46. Frank Hamelly
74. Victoria Yudin
90. Leslie Vail

But it's also good to see that the list features tons of Dynamics GP professionals:

12. Andy Vabulas
14. Andy Snook
30. John Rivers
36. Clinton Weldon
94. Richard Whaley

Special mention to all the folks over at DynamicCommunities who have made the list as well:

15. Andy Hafer
72. Bob McAdam
92. Kim Peterson

Finally, I have to congratulate my good friend David Musgrave (89) who has made the list for the first time. This was a long time coming entry and very well deserved. David's persona is a top of the Microsoft Dynamics GP community's collective mind and I am certainly happy to see his name on the list.

To everyone, congratulations and keep up the good work.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Microsoft Dexterity Training Roadshow - Next Stop: Chicago

(C) Microsoft Corporation

The Microsoft Dexterity Training Roadshow rolls around the United States with its next stop in Chicago, Illinois from May 13 - May 17, 2013. Our training will be hosted at the beautiful John Hancock Center building, from 9:00 AM to 5:00 PM.

John Hancock Center in Chi-town

John Hancock Center
875 N. Michigan Avenue
31st Floor
Chicago, IL 60611

We still have some seats left, so if you want to learn some of the development techniques used by Microsoft Dynamics GP developers this is your chance. For registration information, click here.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Where in the World is Mariano?

Hi all! I'm back!

Well, it's been a grueling 2 months for sure (since Convergence New Orleans) that have taken me to 2 countries (Chile and Venezuela), and 3 states (Louisiana, Nevada, Washington) and have allowed me to rack up over 23,000 new miles on my Delta SkyMiles account.

The beauty of this all is that I have gotten to share my knowledge with more than 5,000 individuals in that stretch - customers and partners - with topics ranging from Web Client, to Dexterity, to the Support Debugging Tool. As such, you have seen my blogging activity take a tumble.

However, I expect to have some time to get back to a number of interesting issues I have come across so topics will also be of a broad range -- just exactly what you are used to from this your humble servant. In the meantime, here are some snapshots from some of my voyages (in no particular order):

Santiago, Chile's skyline from my room at the Marriott Hotel in the Metro Area

Aerial view of a beachside community in Maiquetia, Venezuela at lift off from Simon Bolivar International Airport 

View of Mount Rainier from Delta Sky Club at SEA-TAC - a bit grainy due to camera settings :-(

Ragged Island in the Bahamas

Have a great day everyone and continue to stay tune.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More