How to do a "Save As" in SmartList Builder

Ever wish you could do a "Save As" in SmartList Builder? Have you been working on a very complex SmartList and got to a point where things are working and now you need to make additional changes and you don't want to mess up the work you have done so far?

Just recently, I came across a request for being able to save a SmartList Builder smartlist under a different name - hence the "Save As" title. However, as you know SmartList Builder does not offer the ability to perform such operation out of the box. So here are some steps for you to do a "Save As" with Smartlist Builder:

1. Open SLB's Export Lists window and click on the list you would like to duplicate.
MSDGP | Tools | SmartList Builder | Export

Export Lists window

Enter or identify a path and file name for the list to be exported. Click the Export button when finished.

2. Edit the exported XML file with a text editor, for example Notepad. Search and replace all occurrences of the list ID in your file with the new name.

XML file edited in Notepad

Save the file when finished and exit the editor.

3. Open the SLB's Import Lists window and choose the file you previously exported, now containing the changes you made in step 2.

Import Lists window

Click the Import button to perform the actual import.

4. Return to SmartList Builder to verify the imported record.

Duplicated List

It would be nice sometimes if some of these features were provided straight out of the box, but with some understanding of the functionality and little creativity almost anything can be accomplished.

Until next post!

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

Microsoft SQL Server security roles and Microsoft Dynamics GP SSRS reports

It's not uncommon to find system administrators and consultants alike struggling to deploy Microsoft Dynamics GP SRS reports in an environment, while trying to keep these reports secured. I find that the main issue revolves around understanding the different moving parts: domain accounts, GP user logins, the SQL Server database roles created for reporting, the reports web site security, the reports folder security and the list goes on.

So here is a list of resources that I believe will help with this:

The best place to start is the SQL Server Reporting Services Guide, which has all of the information for installing, deploying and setting up security for SRS reports to use with Dynamics GP. This guide is available for download from the Microsoft Download Center.

Microsoft Dynamics GP 2013 Guide: SQL Server Reporting Services

In order for users to access SRS reports (default) for Dynamics GP, they need to have the following permissions:

1. Access to the Reporting Services web site (native mode) or report library (SharePoint Integrated). I have found the following resources to be extremely useful when configuring Reporting Services security in native mode:

Granting Permissions on a Native Mode Report Server

If you are dealing with Reporting Services in SharePoint Integrated mode, the following is a good resource:

Security Overview for Reporting Services in SharePoint Integrated Mode


2. Permissions to the SRS report files themselves. The following TechNet video shows just that:


3. Database security roles (RPT_ ) under the DYNAMICS and/or company databases in order to pull data into the SRS reports. You will want to take a look at the following KB article 954242 - Frequently asked questions about the integration of SQL Server Reporting Services (SSRS) with Microsoft Dynamics GP 10.0 and Microsoft Dynamics GP 2010, which can be found at:

http://support.microsoft.com/kb/954242

I hope you find these resources useful.

Until next post!

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

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/

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/

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/

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/

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/

Technical Readiness for Microsoft Dynamics GP Latin American Partners

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!!

For registration information, please contact:

North America: Pam Misialek
Latin America/Caribbean: Rodolfo Gonzalez-Llanos

Until next post!

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

Microsoft Dexterity Training Roadshow - Next Stop: Seattle

(C) Microsoft Corporation

The Microsoft Dexterity Training Roadshow rolls around the United States with its next stop in Seattle, Washington from April 22 - April 26, 2013.

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.

Until next post!

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

"Unable to access SnapIn config data Store" accessing Web Management Console

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.

Until next post!

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