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:
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
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.
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.
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.
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.
If you are Latin American partner looking to gain some insight into the deployment considerations of Microsoft Dynamics GP 2013 Web Client, along with the skillset you will need on your team to carryout a successful implementation, then this 2-day course is for you. Course will be conducted at the following locations and dates:
Santiago, Chile - April 17 - 18, 2013
Santiago de Chile
Caracas, Venezuela - April 29 - 30, 2013
Caracas, Venezuela
These courses are organized by Microsoft and all content will be delivered in Spanish. As an added bonus, you get to be trained by me!!
We still have plenty of 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.
For quite some time I had been running into this error when attempting to access the Microsoft Dynamics GP 2013 Web Management Console application from a Windows 8 machine.
SnapIn config data store error
I had ran a Fiddler trace on the issue and determined that the issue simultaneously reported and HTTP 405 error, which suggested a problem with Windows Authentication, required by the Web Management Console.
Fiddler Trace
Since I really couldn't determine what the problem was, the issue went dormant for more than 4 months. In addition, I never seemed to have the time to troubleshoot it with my friends over at Microsoft... until this past Microsoft Convergence New Orleans 2013, that is.
As it turned out, there's one "other pre-requisite" if installing the Web Management Console on a Windows 8 environment: Windows Communication Foundation (WCF) HTTP Activation. Simply put, HTTP activation allows a WCF service to relay its messaging asynchronously over HTTP - in this case HTTPS. In Windows 8, HTTP Activation is turned off by default, unlike Windows Server.
The SnapIn config data store is a table in the GPWEBMANAGEMENT database (dbo.SnapInStorage) and stores the URL to the Session Central Service WCF service. I ran a SQL Profile trace and noticed that the Web Management Console was not able to reach the GPWEBMANAGEMENT database on the SQL Server to read the Session Central Service URL. Therefore, the Console was not able to communicate to the Session Central Service to obtain user session or tenant information.
To turn on HTTP Activation on Windows 8:
1. Open Control Panel
2. Select Programs
3. Click on Turn Windows features on or off
4. Expand .NET Framework 4.5 Advanced Services
5. Expand WCF Services
6. Click on the HTTP Activation checkmark
WCF HTTP Activation
7. If you plan to support other protocols in your environment, you can turn on MSMQ Activation, Named Pipe Activation TCP Activation, or TCP Port Sharing as additional options for your WCF endpoints.
Once the services were up and running, the Web Management Console worked like a charm.
Many thanks to Daryl Anderson and Grant Swenson at Microsoft for assisting with this one.
Convergence is over and I am now back to the daily grind. However and as is customary, this wrap up post serves as a venue to provide some insight into this past event, links to all posts, and of course, suggestions for the next.
First and foremost, a big thank you goes out to my friends at Microsoft for inviting me to this year's Convergence event as a speaker and presenter at the General Session. This is probably one of the highlights of my career in the channel. In particular, I want to send out a big shout-out to the following folks (in no particular order):
Aaron Donat, for accepting the co-presenter role with me at GPPC's PreGAME and to help me in continuing building my understanding and knowledge of the Web Client.
Pam Misialek, for all her guidance throughout the process leading up to the General Session presentation.
Kevin Racer and Daryl Anderson, for inviting me as a co-presenter in our session CSGP32 - Microsoft Dynamics GP 2013 Web Client For Smaller Companies. Sorry I came down ill on our first go around, but hopefully the second time was great.
Errol Schoenfish, for his great introduction of Mark and I and the duel we were about to engage in.
Chad Sogge, for his participation at the MVP Breakfast
David Musgrave, for his patience in preparing me for the Support Debugging Tool session and for dedicating a great deal of time in making the pre-build 18 release available for the session.
I can't thank the rest of you at Microsoft enough for your continued camaraderie and support: Jay Manley, Jennifer Ranz, Theresa Nistler, Brian Meier, Sivakumar Venkataraman, Jared Hall, Brian Roney, Sarah Purdy, Kelly Youells, and the list goes on and on, so please forgive me if I omitted your name here. You all contributed to making this a great Convergence for me and my wife.
Special thanks to Brian Graftaas for conceding some time on his busy Convergence calendar to talk about the Certified for Microsoft Dynamics program.
Outside of Microsoft, I would like to extend a big thank you to Kim Peterson and Andy Hefer at DynamicCommunities for inviting me to GPPC PreGAME as a presenter, MVP Mark Polino for accepting the challenge of presenting with me at General Session, though we only had a few hours to rehearse in person, MVP Jivtesh Singh for his guest appearance during my session CSGP30 - The Microsoft Dynamics GP Support Debugging Tool,
Now, the links...
On this site, you can find coverage of Convergence using the following links:
The Learning Resources page of this blog has been updated with latest Convergence 2013 materials I presented for your consumption. Please download here:
Scroll to the end of the page and click on any of the links to download.
Finally, visit the Virtual Convergence site for links to my presentations. As of this article, they all seem to be up and available, including the General Session.
All good things must come to an end, but no one knows how to wrap up things better than the folks at Microsoft. The day had a few good things in store: the closing Keynote with guest speaker and former United Nations Secretary, Kofi Annan; sessions galore; and a mega concert with Matchbox 20.
As for me, I had to skip all the keynote, but very excited I will be able to catch up with it on Virtual Convergence, so I really don't feel I have missed anything.
Came the afternoon, I was up on stage to present CSGP30 - The Microsoft Dynamics GP Support Debugging Tool, my first ever session on the subject without my buddy, David Musgrave, author of the Support Debugging Tool. The session was attended by 166 strong who got a chance to look at 5 demos ranging from the Support Debugging Tool compatibility with the Web Client, to some of the most misunderstood and underutilized features within the tool, to a glimpse into the new Database Validation tool soon to be released with Build 18.
After my session, I went on to interview Brian Graftaas, Program Manager CfMD at Microsoft. I had vowed to interview Brian since Technical Airlift and am very glad I was able to. You can listen to the full interview here:
My final session of the day was CSGP32-R2 - Microsoft Dynamics GP 2013 Web Client For Smaller Companies. We had 62 attendees for this session and I have to say I enjoyed working with Kevin Racer and Daryl Anderson on this. Unfortunately, I could not carry out our R1 session due to illness, but a quick turnaround in my conditioned allowed me to get on the stage for this one.
Kevin Racer and Me presenting CSGP32-R2
After our session, Convergence 2013 sessions had come to an end. My wife and I met up for dinner at Mulate's, a Cajun institution in the heart of New Orleans, before we headed over to the Matchbox Twenty concert.
Now, I said no one knows closing better than Microsoft and effectively this Convergence won't be any different. For "dessert" we were treated to a full hour concert to the beat of Matchbox Twenty at grounds of the Woldenberg Riverfront Park. The setting was great! Microsoft also provided access to the Audubon Aquarium of the Americas were some found refuge from the cold winds.
Things won't be cold for too long as Matchbox Twenty took the stage by storm playing some of their coolest songs, among them "She's so Mean", by far my favorite - well, maybe one of my many favorites.
Well, concert ended just around midnight and it was time to get back to the hotel to get some rest since I had an early flight at 7:15 AM and needed to be up by 4:30 AM to beat the possible traffic and security lines due to all the people heading out of town.
