Page 3 of 1712345678910...Last »

Important SmartList Builder announcement

On the heels of the Extender and Collections Management announcements over the last year, it has just been announced that SmartList Builder is joining the Microsoft Dynamics GP OEM modules “coming home”. You can read about all the reasons and details for this in Martin Olsen’s comprehensive announcement on the eOne blog.

This is certainly an interesting direction of things and I wonder what this means for the future of selling and supporting Microsoft Dynamics GP. On one hand, as a Dynamics GP partner/VAR, I know that having more ISV products in the mix is sometimes a challenge because there are more moving parts to keep track of – both for sales and ongoing maintenance/support. Also, often selling a GP module to a customer is a much easier endeavor than selling them a third-party add-on product. On the other hand, as the owner of an ISV company myself (my company, Flexible Solutions, makes the GP Reports Viewer add-on), I think it’s great to see these widely used and popular add-ons become ISV products. I feel that paves the road for ISV products to be more accepted and become a standard part of purchasing and using Dynamics GP. In fact, after this change, I don’t think that my company will have more than 15% of our customers left without any ISV products.

I guess the big question will be for customers – are they willing to purchase an ERP system that needs so many additional products from ISVs? Will they start questioning why Microsoft does not make a product robust enough to stand on its own? The devious part of my brain is also wondering if Microsoft is paving the way to create their own products/modules to replace these OEM modules in the future. (Please don’t read more into this, I have absolutely no knowledge of any plans for this, and it’s entirely possible this is just lack of coffee talking.)

After writing the above paragraph, I actually found some support for this happening already with SmartList Designer introduced in GP 2013 SP 2. Take a look at this blog post by eOne describing the differences and another blog post by Mariano Gomez about SmartList Designer. Hmmm…

All my speculation aside, eOne has been fabulous to work with over the last year with the transition of the Extender module (which I love and use daily, by the way). We have quite a number of customers using Extender and we’ve not had a single issue with getting in touch with eOne, transitioning customers, getting support or anything else. I expect the same to hold true for SmartList Builder. And Martin certainly makes a very compelling case as to why this change for SmartList Builder is a good one for all of us.

In the meantime, my understanding is that support for existing SmartList Builder versions will continue to be offered by Microsoft and only if/when you upgrade to the eOne version starting January 2014 will you need to go to eOne for support. eOne also welcomes feature requests for all their products and they just created this forum to gather feature requests for SmartList Builder. I work with SmartList Builder almost daily and will be very interested to see what suggestions people have.

Filed under: Dynamics GP, Microsoft, SmartList Builder Tagged: Dynamics GP, featured, Microsoft, SmartList Builder

Read More:
Important SmartList Builder announcement

September 10, 2013 · Victoria Yudin · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 220

syExcelReports Table & SQL Server Native Client 10.0 – Thanks Aaron Berquist

While upgrading our GP from GP2010 SP3 to GP2013 SP1, DYNAMICS database upgrade stopped unexpectedly with GP crashing without any information for me to debug.

But it stopped exactly at table syExcelReports and crashed. Without spending anymore time, I just went and checked my ODBC DSN setup, as explained by Aaron Berquist on his blog High Dynamics Range. Expectedly, the ODBC DSN driver was not pointing to SQL Server Native Client 10.0.

For some reason, GP fails to process upgrade further if your DSN driver is NOT native client 10.0 or above.

Those who upgrade your GP to latest version, please check DSN before you start database upgrade using GP Utilities.

VAIDY

Filed under: Debugging, DSN, GP, GP 2013, GP Administration, GP Utilities, GP2013, Implementation, Microsoft Dynamics GP, Microsoft Dynamics GP 2013, ODBC, SQL, SQL Native Client, SQL Server, syExcelReports, Troubleshooting, Upgrade

More:
syExcelReports Table & SQL Server Native Client 10.0 – Thanks Aaron Berquist

August 1, 2013 · Vaidyanathan Mohan · No Comments
Tags: , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 174

Quick Tip: Debugging Visual Studio Tools – Developing For Dynamics GP

Awesome tip up there on Developing For Dynamics GP blog on how to debug VS Tools For Dynamics GP.

I am late in blogging about this post. But NOW is the time, as this post just saved my 1/2 day. Seriously. Not kidding. I was stuck with one SQL query execution. There were records to be retrieved, but my code didn’t. After 5-10mins of frustrated troubleshooting, I remembered this post. I should have done that immediately then, but I didn’t.

I setup my VS Tools project to launch GP as instructed on this post and within 2 minutes I could find the issue.

This one deserves to be there on Microsoft Dynamics GP Cookbook (technical).

VAIDY

Filed under: Customizations, Debugging, GP Administration, SQL, SQL Profiler, Support, Tools, Troubleshooting, Visual Studio Tools, VS Tools

More here:
Quick Tip: Debugging Visual Studio Tools – Developing For Dynamics GP

July 30, 2013 · Vaidyanathan Mohan · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 190

Developing with the Microsoft Dynamics GP 2013 Named System Database

David Meego - Click for blog homepageMicrosoft Dynamics GP 2013 introduced another long sought after feature….
The Named System Database.

Instead of the System database being hardcoded to DYNAMICS, a different name can be selected when installing GP.

So what does that mean? Well, it means that more than one Dynamics GP system can reside on a single instance of SQL Server. Before when a second Dynamics GP system was needed, you would have to install a new instance of SQL Server. Now you can have multiple System Databases on a single server without needing additional instances of SQL Server.

For hosting partners, it means that they can host multiple clients off a single SQL Server instance. As a side benefit for consultants supporting multiple versions and multiple customers, it means no longer having to have lots of SQL Server instances on their machines

As a developer, how can I make sure my code uses the correct System Database for my instance?

From a Dexterity perspective it means updating your code to use a call to the function GetSystemDatabaseName() rather than using the constant SQL_SYSTEM_DBNAME to obtain the name of the System Database.

If you have code shared between previous versions of Dynamics, you can create your own function which has conditionally compiled code based on the value of a “version” constant. See the code below for an example:

Code MBS_GetSystemDatabaseName()

function returns string sSystemDBName;
optional in boolean fConnected = true;

#if MBS_PROD_MAJ >= 12 then
    sSystemDBName = GetSystemDatabaseName(fConnected);
#else
    sSystemDBName = SQL_SYSTEM_DBNAME;
#end if

 

But how can you write SQL code which can run against a company database, but reference data in the System Database?

You can’t just prefix the table names with “DYNAMICS..” anymore as that might not be the correct name for the System Database.

I discussed this issue with Patrick Roth who pointed out that the GP 2013 company databases now have a new sySystemDB (SY00100) table with a single record in it that provides the Company ID and the name of the System Database. So if you need to know the Company ID of the current company database or the name of the System Database, just get the first record in the table (ie. from Dexterity: get first table sySystemDB;). 

From SQL, try the query below to see the table.

select top 1 * from SY00100

So armed with this knowledge I created some code to build a query as a string to return some data from a table in the System Database. Then I used the exec() SQL function to call it. This works, but how can I return the value from the pass through SQL code to my script? The exec() function cannot do this, it has no mechanism to pass data back to the calling script.

I then asked my friend and SQL guru, Robert Cavill, “How you can execute pass through SQL code AND return data from the code?”. He told me about the sp_executesql stored procedure which allows for parameters to be defined and passed to and from the pass through SQL code.

The final piece to the puzzle was identifying whether to use the hardcoded “DYNAMICS” (for pre GP 2013) or to use the data in the SY00100 table for the System Database name. This was fairly easy to solve as the SY00100 table will only exist in a company database from GP 2013 or later.

The code below should be executed in the context of a company database. It will identify the associated System Database and then look at the Company Master table and return whether the Use Security Checkbox in the Company Setup window is selected:

SQL Code Example

— Define Variables
declare @UseSecurity INT
declare @SQLCode NVARCHAR(4000)
declare @SYSDBNAME CHAR(80)

— Identify System Database Name
if exists (select * from dbo.sysobjects where id = object_id(N’dbo.SY00100′) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
 select top 1 @SYSDBNAME = DBNAME from SY00100
else
 set @SYSDBNAME = ‘DYNAMICS’

— Define and Execute pass through SQL Code
set @SQLCode = N’select @pUseSecurity = USESCRTY from ‘ + rtrim(@SYSDBNAME) + ‘..SY01500 (nolock)
     where INTERID = DB_NAME()’
exec sp_executesql @SQLCode, N’@pUseSecurity int output’, @UseSecurity output

— Display result from Variable
select @UseSecurity AS UseSecurity

/* Copyright © Microsoft Corporation.  All Rights Reserved.                     */
/* This code released under the terms of the                                    */
/* Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.) */

 

 

I hope you find these techniques useful. I know I did. In my next post you will see why….

David

More:
Developing with the Microsoft Dynamics GP 2013 Named System Database

July 26, 2013 · David Musgrave · No Comments
Tags: , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 277

SQL view for sales quantities by item by site by month in Dynamics GP

As many variations for item quantity summaries as I think I have posted on this blog, there is always another one to be had. A request from a reader brings us a monthly version of my Sales Quantities by Item by Year. The view below shows the total item quantity sold by site by month for a hard-coded year. I am making a number of assumptions (listed in the view comments in green), and there is also an overall yearly total column at the end. You can easily change the year as needed on line 64.

Some additional resources:

create view view_Sales_Qty_by_Item_Site_Month
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
--view_Sales_Qty_by_Item_Site_Month
--Created Jun 14, 2013 by Victoria Yudin - Flexible Solutions Inc
--For updates see http://victoriayudin.com/gp-reports/
--Returns total sales quantities fulfilled (SOP invoices less
--  returns) for each item by month for hardcoded year
--Calendar months and Document Dates are used
--Only posted invoices and returns are included
--Quantity is calculated by multiplying by QTYBSUOM column in
--  case other UofM's are used on transations
--Voided transations are excluded
--Item Description is taken from Inventory Item Maintenance
--  for all inventory items
--  and from SOP line items for non-inventory items
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
d.ITEMNMBR Item_Number, d.Item_Description,
d.LOCNCODE Site_ID,
sum(case when month(d.DOCDATE) = 1
    then d.Qty else 0 end) as Jan_Qty,
sum(case when month(d.DOCDATE) = 2
    then d.Qty else 0 end) as Feb_Qty,
sum(case when month(d.DOCDATE) = 3
    then d.Qty else 0 end) as Mar_Qty,
sum(case when month(d.DOCDATE) = 4
    then d.Qty else 0 end) as Apr_Qty,
sum(case when month(d.DOCDATE) = 5
    then d.Qty else 0 end) as May_Qty,
sum(case when month(d.DOCDATE) = 6
    then d.Qty else 0 end) as Jun_Qty,
sum(case when month(d.DOCDATE) = 7
    then d.Qty else 0 end) as Jul_Qty,
sum(case when month(d.DOCDATE) = 8
    then d.Qty else 0 end) as Aug_Qty,
sum(case when month(d.DOCDATE) = 9
    then d.Qty else 0 end) as Sep_Qty,
sum(case when month(d.DOCDATE) = 10
    then d.Qty else 0 end) as Oct_Qty,
sum(case when month(d.DOCDATE) = 11
    then d.Qty else 0 end) as Nov_Qty,
sum(case when month(d.DOCDATE) = 12
    then d.Qty else 0 end) as Dec_Qty,
sum(d.Qty) Total_Qty

from
(select sh.DOCDATE, sd.ITEMNMBR, sd.LOCNCODE,
 coalesce(I.ITEMDESC, sd.ITEMDESC) Item_Description,
 case sd.SOPTYPE
     when 3 then sd.QTYFULFI*QTYBSUOM
     when 4 then sd.QUANTITY*QTYBSUOM*-1
     end Qty
 from SOP30200 sh
 inner join SOP30300 sd
     on sd.SOPNUMBE = sh.SOPNUMBE
     and sd.SOPTYPE = sh.SOPTYPE
 left outer join IV00101 I
     on I.ITEMNMBR = sd.ITEMNMBR
 where sh.VOIDSTTS = 0
     and sh.SOPTYPE in (3,4)
	 and year(sh.DOCDATE) = 2013 --change year as needed
     and sd.ITEMNMBR not like 'XXXXXXXXXXXXXXX%') d

group by d.ITEMNMBR, d.Item_Description, d.LOCNCODE

go
grant select on view_Sales_Qty_by_Item_Site_Month to DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Filed under: Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code Tagged: featured, GP Reports code, Sales Order Processing, SQL code

See more here:
SQL view for sales quantities by item by site by month in Dynamics GP

June 14, 2013 · Victoria Yudin · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 339

SQL view for SOP sales by customer by month

Someone asked me for code to get monthly SOP sales and I could have sworn I already had that. But I searched and didn’t come up with anything, so here is a new view to show SOP sales (the total of SOP invoices less returns) by month. As usual, I am making a few assumptions (listed in the view comments), and am hard coding one year at a time (2013 in this example). You can easily change the year as needed on line 59.

create view view_Sales_by_Customer_by_Month
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Sales_by_Customer_by_Month
-- Created June 7, 2013
--    by Victoria Yudin - Flexible Solutions, Inc.
-- For updates see http://victoriayudin.com/gp-reports/
-- Returns total sales (invoices - returns) for each customer
--    by month (for the specified year)
-- Amount used is the invoice total (including freight,
--    taxes, discounts, etc.)
-- Only posted invoices and returns are included
-- Shows functional currency
-- Voided transactions are excluded
-- Document Date is used (not GL Posting Date)
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
d.CUSTNMBR Customer_ID,
d.CUSTNAME Customer_Name,
sum(case when month(d.DOCDATE) = 1
  then d.SALES else 0 end) Jan_Sales,
sum(case when month(d.DOCDATE) = 2
  then d.SALES else 0 end) Feb_Sales,
sum(case when month(d.DOCDATE) = 3
  then d.SALES else 0 end) Mar_Sales,
sum(case when month(d.DOCDATE) = 4
  then d.SALES else 0 end) Apr_Sales,
sum(case when month(d.DOCDATE) = 5
  then d.SALES else 0 end) May_Sales,
sum(case when month(d.DOCDATE) = 6
  then d.SALES else 0 end) Jun_Sales,
sum(case when month(d.DOCDATE) = 7
  then d.SALES else 0 end) Jul_Sales,
sum(case when month(d.DOCDATE) = 8
  then d.SALES else 0 end) Aug_Sales,
sum(case when month(d.DOCDATE) = 9
  then d.SALES else 0 end) Sep_Sales,
sum(case when month(d.DOCDATE) = 10
  then d.SALES else 0 end) Oct_Sales,
sum(case when month(d.DOCDATE) = 11
  then d.SALES else 0 end) Nov_Sales,
sum(case when month(d.DOCDATE) = 12
  then d.SALES else 0 end) Dec_Sales,
sum(d.SALES) Yearly_Total

from
(select s.DOCDATE, s.CUSTNMBR, c.CUSTNAME,
 case s.SOPTYPE
   when 3 then s.DOCAMNT
   when 4 then s.DOCAMNT*-1
   end SALES
 from SOP30200 s
 left outer join RM00101 c
   on s.CUSTNMBR = c.CUSTNMBR
 where s.VOIDSTTS = 0
   and s.SOPTYPE in (3,4)
   and year(s.DOCDATE) = 2013 --change year as needed
 ) d

group by d.CUSTNMBR, d.CUSTNAME

-- add permissions for DYNGRP
go
grant select on view_Sales_by_Customer_by_Month to DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Filed under: Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code Tagged: featured, GP Reports code, GP SQL view, Sales Order Processing

Read More:
SQL view for SOP sales by customer by month

June 7, 2013 · Victoria Yudin · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 209

Assign sequential numbers in SQL Server

Here is a cool little SQL Server tip from the April GP Reports Viewer newsletter. Say you have a list of rows and you want to assign sequential numbers to them, so that you always know which one is first, second, etc. Or so that you can display line numbers. This is often a need for Dynamics GP Sales Order Processing (SOP) transactions because they use line item sequence numbers that are very spread out to allow for inserting rows in-between them. (More detail on this from Dynamics GP MVP Mariano Gomez.)

Here is an example of an SOP transaction in GP:SOP Transaction example

Using the following SQL code you can see this data in SQL:

select SOPTYPE, SOPNUMBE, LNITMSEQ, ITEMNMBR
from SOP10200 where SOPNUMBE = 'ORDST2227'

The results look like this:

SQL Results - original

The LNITMSEQ column is what determines the order of the lines. If you wanted to know which line is 1st, 2nd, 3rd, that would be a little difficult. The code below will add a sequential line number to this data using the line item sequence to determine the order of the lines:

select
row_number() over (partition by SOPNUMBE, SOPTYPE
 order by LNITMSEQ) LineNumber,
SOPTYPE, SOPNUMBE, LNITMSEQ, ITEMNMBR
from SOP10200
where SOPNUMBE = 'ORDST2227'

Now your results will include a line number column:
SQL Final Results

For more tips like this sign up for the GP Reports Viewer newsletters. You can see past newsletters on the GP Reports Viewer website.

Filed under: Dynamics GP, GP Reports code, GP Reports Viewer, SOP SQL code, SQL Server Tagged: Dynamics GP, featured, GP Reports code, GP Reports Viewer, Sales Order Processing, SQL code

See the original article here:
Assign sequential numbers in SQL Server

May 7, 2013 · Victoria Yudin · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 292