How to Display Parent Company Information on SOP documents

A few days aback I encountered a really interesting discussion on the Microsoft Dynamics GP Community forum where the user needed to get the parent customer information on the SOP Blank Invoice Form. I asked myself, "Well, how difficult can that be?"

The first thought around these requests is, you create a table relationship between the Sales Transaction Work (SOP_HDR_WORK) table and the RM Customer Master (RM_Customer_MSTR) table by Customer Number and you get the parent customer ID. In that train of thought you immediately realize that there's no way to alias the RM Customer Master table back into the table relationship to retrieve the parent customer ID information. [NOTE TO SELF: this would be a cool Report Writer feature, but I digress.]

Of course, a second alternative is to use VBA, but many customers and partners are wary of implementing a VBA solution, especially when they are considering upgrading to Microsoft Dynamics GP 2013 and taking advantage of the web client. VBA customizations simply do not work with the web client.

As I thought through an alternative, I quickly realized this would be a good fit for the Support Debugging Tool and it's awesome Runtime Execute feature. As it turns out, the Support Debugging Tool is capable of executing Dexterity sanScript code within the context of any dictionary, but not only that; it provides some really cool templates that save time and minimizes the amount of code an entry level developer needs to create to accomplish a solution. In turn, there are a number of Report Writer API scripts built into the Dynamics application dictionary (DYNAMICS.DIC) to allow third party developers to expose data from their tables onto an existing Microsoft Dynamics GP report without having to write a complex interface to do so.

The Solution

1. The first task at hand is to open the Runtime Execute window in the Support Debugging Tool. Once the window is open, click on the Helper button to open the Insert Helper Function window. From the drop-down list, select template for rw_TableHeaderString.

Insert Helper Function window

Below is the signature for the function as we will use it.

The rw_TableHeaderString function

function returns string sData;

in integer dict_id;  {Support Debugging Tool Dictionary ID}
in string report_name;  {Support Debugging Tool Script ID to call}
in string sNumber;  {Parent Customer ID field we will retrieve}
in integer sType;  {Not used, we will default to 0}
in integer iControl;  {which piece of data to return} 

We will use this function to return a string value for the selected Parent Customer ID information field to a Report Writer calculated field.

By clicking OK, the Support Debugging Tool inserts the code to get us started in the right direction.

Helper Code

Very cool! All that's left now is to add the extra bits of code to retrieve the parent customer information from the RM Customer Master. In addition, we need to make sure that this code executes in the context of the Microsoft Dynamics GP product.

Additional Code

The template function even indicates where our code should be inserted and as you can see this is easy stuff for any entry level Dex developer. All we did here was use the MBS_Number local field as the parameter value that will contain our parent customer ID (to be passed from Report Writer) to get the piece of data we are interested in based on the value that we will supply to the MBS_Control local field (again, from Report Writer). The returned table value will then be stored in the MBS_TableHeaderString local field.

The Support Debugging Tool then goes to action and call its MBS_Param_Set API function to talk back to the Report Writer API script and deliver the values retrieved to it. Isn't this awesome!

2. The second part of our solution consists of 3 parts really: a) we need to create a table relationship between the Sales Transaction Work table and the RM Customer Master table as this relationship does not exist out of the box, b) We then need to create calculated fields for the pieces of Parent Customer data we would like to retrieve, and c) We need to complete the layout and grant security to the modified report.

I will assume for this article that you are familiar with the processes of creating new table relationships in Report Writer and laying out the new calculated fields and granting security to the modified report. You can always browse the Report Writer manual accompanying your Microsoft Dynamics GP application for information of these processes, so my attention will focus solely on the calculated field.

In this example, I want to retrieve the parent customer name. For this we will create a new calculated field as follows:

a) Create a new calculated field, in this case I will call it (A) Parent Name. The result type of the calculation will be a String and the expression type is Calculated.

b) Since we are using the rw_TableHeaderString user-defined function, we will choose this accordingly:

rw_TableHeaderString

c) Now we have to setup the parameters as indicated by the function signature above. The first parameter we will add is the Support Debugging Tool's dictionary ID, 5261. We will add this as an integer constant.

Dictionary constant 5261 to call the script we wrote

d) Now we have to call the Runtime Execute script we created, GETPARENTINFO. We will add this as a string constant.

GETPARENTINFO

e) We now need to pass the parent customer number parameter, our default (not used) parameter, and the value number for the field that will be retrieved for the parent customer data, according to how we coded our script above. This is shown below.

Last 3 parameters.

The RM_Customer_MSTR.Corporate Customer Number field is retrieved from the RM Customer Master table which we created via a new table relationship and subsequently adding that table relationship to our report.

The following shows a muck-up of the report layout with the parent customer name calculated field created:

Report Layout

The report should display something like this when run in Microsoft Dynamics GP:

Invoice Layout

Final Notes

The above script could be extended to retrieve other pieces of data for our parent customer, like contact, address, city, state, zip, phone numbers, etc. In this case you will need to add more entries to the case...end case statement above and retrieve the correct table column. You can find more information on the table columns for the RM Customer Master in the Microsoft Dynamics GP software development kit (SDK).

This solution is absolutely compatible with Word Templates and the Web Client. In the case of Word Templates, you will need to export a new report definition file (XML) and import into your template fields. In the case of the Web Client, all the components above are within the confines of a Dexterity application and the Support Debugging Tool happens to be compatible with the Web Client, just as much as Report Writer is. Your modified report should render absolutely fine on the Silverlight canvas control, if printing the standard report.

For all workstations to take advantage of the Runtime Execute script, the Support Debugging Tool must be deployed with the recommended configuration - all workstations pointing to a centralized configuration file, debugger.xml.

---

The Support Debugging Tool never ceases to amaze me. You can find similar article to this one and more information here:

The Dynamics GP Blogster - Adding Customer Item User Defined Field to SOP Invoice

Developing for Dynamics GP - The Support Debugging Tool Portal

Until next post!

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

Read More

Listing all eConnect Modified PRE and POST stored procedures

As of recent, I was ask of a simple method to determine which eConnect PRE and POST stored procedures had been modified. After all, with a concrete list, you are able to script just the affected ones prior to a major version upgrade or a feature pack installation.

A modified stored procedure could be simply qualified as one on which you have ran an ALTER PROCEDURE statement or have edited it using SQL Server Management Studio. With that said, the following query may not be failsafe, but provides a very accurate way of getting a list.

-- 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 name, create_date, modify_date 
FROM sys.objects 
WHERE (type = 'P') AND (name LIKE 'ta%Pre' or name LIKE 'ta%Post') AND (create_date <> modify_date)

Enjoy!

Until next post!

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

Read More

Rejecting duplicate checks during Bank Transactions import with Integration Manager

One of the interesting things about checkbooks setup in Microsoft Dynamics GP is that you have the ability to prevent duplicate checks from being issued in the Payables Management module, yet those same controls do not apply if you are entering checks in the Bank Transaction Entry window.

Checkbook Maintenance window - Payables Options

This can certainly be an issue if those check numbers happen to be integrated from a line of business application as a bank transaction in the Financial module.

Bank Transaction Entry window

This was certainly the case for a partner who was integrating a number of transactions from a line of business application into Microsoft Dynamics GP and required to implement a control to prevent duplicate check numbers from being integrated.

Integration Manager scripting capabilities proved to be very helpful here. By adding some VBScript to the Before Document event script, we can check to see if the check number being integrated exists in the CM Transactions table (dbo.CM20100) prior to allow the integration to commit the record in Microsoft Dynamics GP.

Before Document script

'
' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons 
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.

Const adUseClient     = 3
Const adCmdStoredProc = 4
Const adCmdText       = 1

Dim oCn, oCmd, oRs

Set oCn = CreateObject("ADODB.Connection")
With oCn
  .ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
  .CursorLocation = adUseClient
End With

GPConnection.Open(oCn)

' Prepare the SQL statement and retrieve the next voucher number

Set oCmd = CreateObject("ADODB.Command")
With oCmd 
 .ActiveConnection = oCn
 .CommandType = adCmdText
 .CommandText = "SELECT * FROM CM20100 WHERE CMTRXNUM = '" & _ 
    CStr(SourceFields("sourceQry.CheckNumber")) & _ 
    "' AND CHEKBKID = 'FIRST BANK';"
 Set oRs = .Execute
End With

If Not oRs.EOF Then
' This is a duplicate check
   CancelDocument "Duplicate Check Number for checkbook FIRST BANK: " & _ 
     CStr(SourceFields("sourceQry.CheckNumber"))
End If
oRs.Close
oCn.Close

Set oCmd = Nothing
Set oCn = Nothing

A few things to note:

The CancelDocument function is used to reject the record if it's found in the database. We can also add a simple message to be written to the integration log file describing the check number found to be a duplicate.

You can optimize this code by opening a connection to the database and storing the connection in a global variable in the Before Integration event script, rather than having to open and close the connection several times as I do here. Likewise, you can close the connection in the After Integration script.

The bottom line, nonetheless, is to show a simple technique for record control and rules implementation that help the partner and the customer overcome the issue they were having.

Until next post!

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

Read More

Adding Customer Item User Defined fields to SOP Invoice

Just recently I ran across a request to add the Customer Item user defined fields to the Sales Blank Invoice Form report in Report Writer. As usual with these type of requests, chances are it's been done before and/or it's probably documented in some KB article.

As it turned out, KB article 918943 outlines the steps required to add the Customer Item Number to the Sales Blank Invoice Form report:

How to add the "Customer Item Number" field to a quote, order, or invoice form in Sales Order Processing in Microsoft Dynamics GP

The KB article however, makes use of the Report Writer function rw_SOP_GetCustomerItemNumber to get around the issue. There's also an additional Report Writer function, rw_SOP_GetCustomerItemDescription which allows you to retrieve the description for the customer item. Now, that's all cool, but what about the user-defined fields?

Customer Item Maintenance

The screenshot above highlights the fields in question. As it turned out, there are no Report Writer functions to retrieve those.

In looking at possible ways to add these fields you could try 3 different things:

1. Use Modifier with VBA to retrieve the user-defined fields. While this is a viable solution, the partner was adamant about not using VBA because of lack of supportability on the Microsoft Dynamics GP 2013 Web Client.

2. Table relationships in Report Writer. After looking at the table keys on SOP_LINE_WORK (SOP10200) and sopCustomerItemXref (SOP60300), I concluded that table relationships would not work either. The problem here is that the SOP_LINE_WORK table does not contain the Customer Number field, required to be able to establish a relationship to the sopCustomerItemXref. After all, if the field existed, there would be no need for the Report Writer functions mentioned above.

3. Support  Debugging Tool. The SDT has the ability to encapsulate Dexterity code with it's Runtime Execute feature. That code can be saved to a Script ID. Furthermore, the Support Debugging Tool leverages the 6 generic Report Writer functions available in the Microsoft Dynamics GP dictionary. These functions were created to allow third party developers a way around an Alternate Report, with the ability to retrieve data from their tables.

For more information on the generic Report Writer functions, take a look at KB article 888884:

Useful functions for developers to use instead of creating alternate reports in Microsoft Dynamics GP

Given all the options and drawbacks, it was decided that Support Debugging Tool would be able to accomplish the job. Since it's a Dex application, it certainly would move forward to Microsoft Dynamics GP 2013 and Web Client, and in addition, would be able to work around the table relationships issue.

---

One thing I like about the Support Debugging Tool's Runtime Execute is the ability to use the Helper functions. In particular, the helper function needed is one that allows us to leverage a generic Report Writer function, the rw_TableLineString, while line items are being printed on the report - when a line item is displayed, we must display the corresponding Customer Item user defined field.

Insert Helper Function window

Since there's a template for the above function, all we need to do is click on the Ok button. As if the Support Debugging Tool wasn't already helpful, it now autogenerates some of the code needed to work with the rw_TableLineString:

Runtime Execute window with auto-generated Dexterity code

All that's left now is to add a script ID and enter the bit of code needed to retrieve the user-defined fields:

Runtime Execute script

The full version of the script can be found below:
Script ID: CUSTITMUDF

local string MBS_TableLineString;
local string MBS_Number;
local integer MBS_Type;
local currency MBS_SequenceOne;
local currency MBS_SequenceTwo;
local integer MBS_Control;
local string MBS_String;

{Declarations}
local string ItemNumber;
local string CustomerNumber;

call with name "MBS_Param_Get" in dictionary 5261, "Number", MBS_Number;
call with name "MBS_Param_Get" in dictionary 5261, "Type", MBS_String;
MBS_Type = integer(value(MBS_String));
call with name "MBS_Param_Get" in dictionary 5261, "SequenceOne", MBS_String;
MBS_SequenceOne = currency(value(MBS_String));
call with name "MBS_Param_Get" in dictionary 5261, "SequenceTwo", MBS_String;
MBS_SequenceTwo = currency(value(MBS_String));
call with name "MBS_Param_Get" in dictionary 5261, "Control", MBS_String;
MBS_Control = integer(value(MBS_String));
MBS_TableLineString = "";

{ Add your code below here }

{ Retrieve the item number in question }
clear table SOP_LINE_WORK;
set 'SOP Number' of table SOP_LINE_WORK to MBS_Number;
set 'SOP Type' of table SOP_LINE_WORK to MBS_Type;
set 'Line Item Sequence' of table SOP_LINE_WORK to MBS_SequenceOne;
set 'Component Sequence' of table SOP_LINE_WORK to MBS_SequenceTwo;

get table SOP_LINE_WORK by number 1;
if err() = OKAY then
 set ItemNumber to 'Item Number' of table SOP_LINE_WORK;
end if;

{ Now, get the customer number }
clear table SOP_HDR_WORK;
set 'SOP Number' of table SOP_HDR_WORK to MBS_Number;
set 'SOP Type' of table SOP_HDR_WORK to MBS_Type;

get table SOP_HDR_WORK by number 1;
if err() = OKAY then
 set CustomerNumber to 'Customer Number' of table SOP_HDR_WORK;
end if;

{Since we have the customer number and item number, we should now be able to
 retrieve the customer item number
}

if not empty(ItemNumber) and not empty(CustomerNumber) then
 clear table sopCustomerItemXref;
 set 'Item Number' of table sopCustomerItemXref to ItemNumber;
 set 'Customer Number' of table sopCustomerItemXref to CustomerNumber;
 
 get table sopCustomerItemXref by number 1;
 if err() = OKAY then
  case MBS_Control 
   in [1] {UDF 1}
    MBS_TableLineString = 'User Defined 1' of table sopCustomerItemXref;
   in [2] {UDF 2}
    MBS_TableLineString = 'User Defined 2' of table sopCustomerItemXref;
   in [3] {UDF 3}
    MBS_TableLineString = 'User Defined 3' of table sopCustomerItemXref;
   in [4] {UDF 4}
    MBS_TableLineString = 'User Defined 4' of table sopCustomerItemXref;
   in [5] {UDF 5}
    MBS_TableLineString = 'User Defined 5' of table sopCustomerItemXref;
   in [6] {UDF Key 1}
    MBS_TableLineString = 'User Defined Key1' of table sopCustomerItemXref;
   in [7] {UDF Key 2}
    MBS_TableLineString = 'User Defined Key2' of table sopCustomerItemXref;
   in [8] {UDF Key 3}
    MBS_TableLineString = 'User Defined Key3' of table sopCustomerItemXref;
   in [9] {UDF Key 4}
    MBS_TableLineString = 'User Defined Key4' of table sopCustomerItemXref;
  end case;
 else
  MBS_TableLineString = "";
 end if;
end if;

{ Add your code above here }

call with name "MBS_Param_Set" in dictionary 5261, "TableLineString", MBS_TableLineString;

Cool balloons! One aspect of the programming was, how to leverage the same function (as opposed to creating multiple scripts that would perform the same operations) to pull in different UDFs? As it turns out, there's a parameter in the rw_TableLineString function, which allows you to specify a control value which determines the position of the piece of data to be returned. This control value is an integer type. Knowing this allowed me to send in a numeric value for the UDF I wanted to retrieve and define a case...end case statement evaluating the control parameter to return the proper UDF field.

---

Finally, the rest is just house keeping at the Report Writer level... First, we must create two currency calculated fields: one for the Line Item Sequence and another for the Component Sequence. As it turns out, our Report Writer custom function requires two currency parameters. Below is the rw_TableLineString function signature

rw_TableLineString()

in integer dict_id; {Dictionary ID}
in string script_id; {Script ID} in string sNumber; {control field 1}
in integer sType; {control field 2}
in currency cSequenceOne; {control field 3}
in currency cSequenceTwo; {control field 4} 
in integer iControl; {which piece of data to return}

Our calculated fields are shown below:

(C) Component Sequence calculated field

(C) Line Item Sequence calculated field

Finally, we need a calculated field to invoke our Report Writer function, which in turn will call our script ID created in Support Debugging Tool. In this specific case, we are going to retrieve the Customer Item user-defined field 1.

CustomerItemUDF1 Calculated Field

FUNCTION_SCRIPT( rw_TableLineString 5261 "CUSTITMUDF" SOP_LINE_WORK.SOP Number SOP_LINE_WORK.SOP Type (C) Line Sequence Number (C) Component Sequence 1)

We can now move our CustomerItemUDF1 calculated field onto the report layout:

After granting security to the modified report, we can print an invoice to verify the report works.

As you can see, once more we have leveraged the power of the Support Debugging Tool to deliver what would have otherwise seemed like a difficult or unattainable customization to a report. I hope this is yet another incentive to install the tools and begin taking advantage of its features.

Until next post!

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

Read More

SSRS: GL Trial Balance Summary report returns no data - Follow Up

Back in July of last year, I wrote an article on a reported issue with the SSRS GL Trial Balance reports were not returning any data with Microsoft Dynamics GP 2010 and 2010 R2 (SP2) - see SSRS: GL Trial Balance Summary report returns no data.

Since then, this problem has been documented in KB article 2588519 - GL Trial Balance SRS Reports return no data using Microsoft Dynamics GP and a workaround provided in the same article to fix the ailing stored procedure, dbo.seeglPrintSRSTrialBalance, causing data not to be returned.

The good news is, this problem is scheduled to be fixed in Service Pack 3.

Until next post!

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

Read More

Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL - Follow up!

Ok, of course my dear friend Corey in Louisville, Ohio had to challenge my post from yesterday and ask a question that is, frankly, a logical and natural progression of things - see Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL.

In yesterday's post I showed a query that could create a year-to-year Inventory Margin report using T-SQL's PIVOT operator. The query, while very useful, hardcodes the years you want to display in the pivoted columns list. An excerpt of the PIVOT operator and the pivoted columns list as follows:

.
.
.
PIVOT (
 SUM(MARGIN)
 FOR TRXYEAR IN ([2014], [2015], [2016], [2017])
 ) AS pvt

So naturally, Corey's question was, "What if I want to add the pivoted columns list dynamically, instead of hardcoding them?". Since all transactions have a date, it would make sense to add the list of years based on the document dates. The good news is this is possible. For this we would need to create a dynamic pivoted list using, well, dynamic SQL, as follows:

DECLARE @listCol NVARCHAR(MAX), @sqlstmt NVARCHAR(MAX);

SELECT  @listCol = STUFF(
 ( SELECT DISTINCT '],[' + CONVERT(NVARCHAR(5), YEAR(DOCDATE))
   FROM SOP30200
   ORDER BY '],[' + CONVERT(NVARCHAR(5), YEAR(DOCDATE))
   FOR XML PATH('')
 ), 
 1, 
 2, 
 '') + ']'

SET @sqlstmt = 
N';WITH CTE AS (
 SELECT YEAR(b.DOCDATE) AS TRXYEAR, a.ITEMNMBR, a.ITEMDESC, SUM(a.XTNDPRCE) AS XTNDPRCE, SUM(a.EXTDCOST) AS EXTDCOST, SUM(a.XTNDPRCE - a.EXTDCOST) AS MARGIN
 FROM SOP30300 a LEFT OUTER JOIN SOP30200 b ON (a.SOPTYPE = b.SOPTYPE) AND (a.SOPNUMBE = b.SOPNUMBE) 
 WHERE (b.SOPTYPE = 3) AND (b.VOIDSTTS = 0) 
 GROUP BY YEAR(b.DOCDATE), a.ITEMNMBR, a.ITEMDESC
)
SELECT ITEMNMBR AS [Item Number], ITEMDESC AS [Item Description],' + @listCol + N'
FROM (
 SELECT TRXYEAR, ITEMNMBR, ITEMDESC, MARGIN 
 FROM CTE
) p 
PIVOT (
 SUM(MARGIN)
 FOR TRXYEAR IN (' + @listCol + N')
 ) AS pvt
ORDER BY ITEMNMBR;';

EXEC sp_executesql @sqlstmt;

Couple observations...

We use the FOR XML clause with the PATH mode to build a list of elements and attributes based on the distinct year values stored in the document date (DOCDATE) column in our SOP30200 (SOP History) table, which we store in the @listcol variable length Unicode character data type (NVARCHAR).

Our CTE is now embedded in an NVARCHAR variable, which we call @sqlstmt, with just the right breaks to concatenate our pivoted column list variable, @listcol as part of the overall SQL statement character string that will be executed.

We finally use the sp_executesql system stored procedure to run our dynamic SQL query and produce the results below:

Item Number Item Description 2014 2015 2016 2017 2018
100XLG                          Green Phone                                                                                           NULL NULL 307.05000 40.05000 NULL
3-B3813A                        Keyboard                                                                                              NULL NULL NULL 40.00000 NULL
3-C2924A                        SCSI Cable, 2.5m. 68-pin HI-Density                                                                   NULL NULL NULL 148.50000 NULL
3-C2924A                        T0101 - SCSI Cable, 2.5m. 68-pin HI-Density                                                           NULL NULL NULL NULL 84.38000
3-C2924A                        T0102 - SCSI Cable, 2.5m. 68-pin HI-Density                                                           NULL NULL NULL NULL 67.50000

Finally, you will notice NULL values for some of the years where there was no sales activity for an item. You can take care of these and how they display, directly on your report or Excel spreadsheet.

Not bad at all!

For a primer on the Do's and Don'ts of dynamic SQL, I invite you to read SQL Server MVP, Earland Sommarskog article, The Curse and Blessings of Dynamic SQL.

Until next post!

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

Read More

Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL

As of late I have been camping out at the SQL Developer Center's  Transact-SQL Forum and I have to say, I have learned a great deal from my fellow SQL Server MVPs. Very alike the Microsoft Dynamics GP MVPs, these folks are willing to resolve pretty much any T-SQL request that comes across the forum.

So long you follow some simple posting guides, like including your table definitions, providing some sample data and detailing your expected results, there's nothing these folks won't do for you.

I decided to put some of what I have learned to the test by creating a T-SQL query that would produce a Year-to-year Inventory Margin Report (in currency) by using the T-SQL PIVOT operator. My fellow MVP Mark Polino is very well versed with Microsoft Excel and PowerPivot and I thought this would be a way to demonstrate that you can still use T-SQL to resolve some very complex issues which otherwise would require the use of front-end tool or some back-end cubes.

The following query can be run against the Fabrikam test company and adjusted to meet your specific needs:

;WITH CTE AS (
 SELECT YEAR(b.DOCDATE) AS TRXYEAR, a.ITEMNMBR, a.ITEMDESC, SUM(a.XTNDPRCE) AS XTNDPRCE, SUM(a.EXTDCOST) AS EXTDCOST, SUM(a.XTNDPRCE - a.EXTDCOST) AS MARGIN
 FROM SOP30300 a LEFT OUTER JOIN SOP30200 b ON (a.SOPTYPE = b.SOPTYPE) AND (a.SOPNUMBE = b.SOPNUMBE) 
 WHERE (b.SOPTYPE = 3) AND (b.VOIDSTTS = 0) 
 GROUP BY YEAR(b.DOCDATE), a.ITEMNMBR, a.ITEMDESC
)
SELECT ITEMNMBR AS [Item Number], ITEMDESC AS [Item Description], 
 COALESCE([2014], 0) AS Y2014, 
 COALESCE([2015], 0) AS Y2015,
 COALESCE([2016], 0) AS Y2016, 
 COALESCE([2017], 0) AS Y2017 
FROM (
 SELECT TRXYEAR, ITEMNMBR, ITEMDESC, MARGIN 
 FROM CTE
) p 
PIVOT (
 SUM(MARGIN)
 FOR TRXYEAR IN ([2014], [2015], [2016], [2017])
 ) AS pvt
ORDER BY ITEMNMBR;

The script uses a query encapsulated in a Common Table Expression (CTE) to produce a temporary result for the sales data by item, grouped over each year. However, this result would produce rows of records for each year, as shown below:

SELECT YEAR(b.DOCDATE) AS TRXYEAR, a.ITEMNMBR, a.ITEMDESC, SUM(a.XTNDPRCE) AS XTNDPRCE, SUM(a.EXTDCOST) AS EXTDCOST, SUM(a.XTNDPRCE - a.EXTDCOST) AS MARGIN
 FROM SOP30300 a LEFT OUTER JOIN SOP30200 b ON (a.SOPTYPE = b.SOPTYPE) AND (a.SOPNUMBE = b.SOPNUMBE) 
 WHERE (b.SOPTYPE = 3) AND (b.VOIDSTTS = 0) 
 GROUP BY YEAR(b.DOCDATE), a.ITEMNMBR, a.ITEMDESC

/* Results */

TRXYEAR ITEMNMBR ITEMDESC XTNDPRCE EXTDCOST MARGIN
2014 ACCS-CRD-12WH                   Phone Cord - 12' White                                                                                39.80000 13.16000 26.64000
2014 ACCS-RST-DXBK                   Shoulder Rest-Deluxe Black                                                                            29.85000 13.65000 16.20000
2014 ACCS-RST-DXWH                   Shoulder Rest - Deluxe White                                                                          39.80000 18.20000 21.60000
2014 ANSW-ATT-1000                   Attractive Answering System 1000                                                                      119.95000 59.29000 60.66000
2014 ANSW-PAN-1450                   Panache KX-T1450 answer                                                                               219.90000 100.50000 119.40000
2014 FAXX-CAN-9800                   Cantata FaxPhone 9800                                                                                 23999.50000 11970.00000 12029.50000
2014 FAXX-HLP-5433                   Hewlett Packard FAX-310                                                                               854.50000 395.10000 459.40000
2014 FAXX-SLK-0172                   Sleek UX-172 fax                                                                                      2699.90000 1349.00000 1350.90000
2014 HDWR-PNL-0001                   Control Panel                                                                                         609.95000 303.85000 306.10000
2014 HDWR-PRO-4862                   Processor 486/25MHz                                                                                   5999.95000 2998.15000 3001.80000
2014 PHON-ATT-53BL                   Cordless-Attractive 5352-Blue                                                                         1139.70000 561.30000 578.40000
2014 PHON-ATT-53WH                   Cordless-Attractive 5352-White                                                                        189.95000 92.59000 97.36000
2014 PHON-BUS-1250                   Handset,multi-line                                                                                    359.95000 165.85000 194.10000
2014 PHON-PAN-2315                   Panache KX-T231 wall                                                                                  119.90000 59.50000 60.40000
2014 WIRE-SCD-0001                   Single conductor wire                                                                                 8.75000 4.00000 4.75000
2016 100XLG                          Green Phone                                                                                           4136.55000 3829.50000 307.05000

But in order to produce the results we want, having a true year by year comparison, it is necessary to pivot the results of the above query. Here's where the PIVOT operator comes into play, as shown in the initial query. This is the output produced:

Item Number       Item Description      Y2014  Y2015  Y2016  Y2017
100XLG            Green Phone                                            0.00000 0.00000  307.05000 40.05000
3-B3813A          Keyboard                                               0.00000 0.00000  0.00000  40.00000
3-C2924A          SCSI Cable, 2.5m. 68-pin HI-Density                    0.00000 0.00000  0.00000  148.50000
3-C2924A          T0101 - SCSI Cable, 2.5m. 68-pin HI-Density            0.00000 0.00000  0.00000  0.00000
3-C2924A          T0102 - SCSI Cable, 2.5m. 68-pin HI-Density            0.00000 0.00000  0.00000  0.00000
3-D2657A          T0101 - DB 15 Male Adapter                             0.00000 0.00000  0.00000  0.00000
3-D2657A          T0102 - DB 15 Male Adapter                             0.00000 0.00000  0.00000  0.00000
3-D2659A          T0101 - DB 25 Female Adapter                           0.00000 0.00000  0.00000  0.00000
3-D2659A          T0102 - DB 25 Female Adapter                           0.00000 0.00000  0.00000  0.00000
3-E4471A          HP Extractor Fan, 200-240V                             0.00000 0.00000  0.00000  134.00000
3-E4592A          SurgeArrest Plus                                       0.00000 0.00000  0.00000  180.00000
3-E4592A          T0101 - SurgeArrest Plus                               0.00000 0.00000  0.00000  0.00000
3-E4592A          T0106 - SurgeArrest Plus                               0.00000 0.00000  0.00000  0.00000
3-J2094A          HP-PB 16 Channel RS-232C Modem Conn MUX                0.00000 0.00000  0.00000  473.00000
4-A3666A          4.2GB LP Disk Drive                                    0.00000 0.00000  0.00000  350.00000
5-FEE             Per Call Fee                                           0.00000 0.00000  0.00000  0.00000
5-FEE             T0101 - Per Call Fee                                   0.00000 0.00000  0.00000  0.00000
5-FEE             T0102 - Per Call Fee                                   0.00000 0.00000  0.00000  0.00000
5-OVTLABOR        T0101 - Overtime service labor                         0.00000 0.00000  0.00000  0.00000
5-OVTLABOR        T0102 - Overtime service labor                         0.00000 0.00000  0.00000  0.00000
5-STDLABOR        T0101 - Standard service labor                         0.00000 0.00000  0.00000  0.00000
5-STDLABOR        T0102 - Standard service labor                         0.00000 0.00000  0.00000  0.00000
5-STDLABOR        T0106 - Standard service labor                         0.00000 0.00000  0.00000  0.00000
5-TVLLABOR        T0101 - Travel Labor                                   0.00000 0.00000  0.00000  0.00000
5-TVLLABOR        T0102 - Travel Labor                                   0.00000 0.00000  0.00000  0.00000
5-TVLLABOR        T0106 - Travel Labor                                   0.00000 0.00000  0.00000  0.00000
ACCS-CRD-12WH     Phone Cord - 12' White                                 26.64000 0.00000  199.80000 226.44000
ACCS-CRD-25BK     Phone Cord - 25' Black                                 0.00000 0.00000  69.85000 83.82000
ACCS-HDS-1EAR     Headset-Single Ear                                     0.00000 0.00000  1034.00000 813.35000
ACCS-HDS-2EAR     Headset - Dual Ear                                     0.00000 0.00000  0.00000  527.67000
ACCS-RST-DXBK     Shoulder Rest-Deluxe Black                             16.20000 0.00000  226.80000 226.80000
ACCS-RST-DXWH     Shoulder Rest - Deluxe White                           21.60000 0.00000  163.20000 205.20000
ANSW-ATT-1000     Attractive Answering System 1000                       60.66000 0.00000  242.64000 303.30000
ANSW-PAN-1450     Panache KX-T1450 answer                                119.40000 0.00000  1194.00000 1134.30000
ANSW-PAN-2460     Panache KX-T2460 answer                                0.00000 0.00000  149.60000 169.60000
FAXX-CAN-9800     Cantata FaxPhone 9800                                  12029.50000 0.00000  66987.41000 51129.85000
FAXX-HLP-5433     Hewlett Packard FAX-310                                459.40000 0.00000  0.00000  0.00000
FAXX-RIC-060E     Richelieu Fax 60E                                      0.00000 0.00000  2404.50000 2404.50000
FAXX-SLK-0172     Sleek UX-172 fax                                       1350.90000 0.00000  1350.90000 675.45000

Happy pivoting.

Until next post!

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

Read More

"Object Reference Not Set" error when running Integration Manager with eConnect Adapter

I have seen a number of forum posts around this subject and have even received a few calls for help in troubleshooting the issue. In the occassions I have assisted someone, I have noticed that most of the time the developer or consultant was using an event or field script of some kind, which almost always attempts to get some information from Microsoft Dynamics GP.

So, in an attempt to reproduce the problem, I have recreated the following VBScript based on a recent case:

' Created by Mariano Gomez, MVP

'  This code is licensed under the Creative Commons 

'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.



Dim objConn, objRec, cmd, sJE

 



set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "database=" & GPConnection.GPConnInterCompanyID

GPConnection.Open(objConn) 

 



Set cmd = CreateObject("ADODB.Command")

cmd.ActiveConnection = objConn

 

cmdString = "SELECT NJRNLENT FROM GL40000;" 

Set objRec = objConn.Execute(cmdString)



if Not objRec.Bof and Not objRec.Eof then

  objRec.MoveFirst

  CurrentField = objrec.fields(0).value

end if

  

'Close recordset when finished

Call objRec.Close

  

'Close connection when finished

Call objConn.Close

 

Set cmd = Nothing

Set objConn = Nothing

NOTE: This script purposefully contains errors and does not follow best practices. It was recreated to illustrate the issue on the subject.

In summary, the above script was added by the consultant to retrieve the next journal number for a GL Transaction integration with the eConnect Adapter. The consultant reported the script working on and off on the server and not working on the workstations. However, in each case the error reported by Integration Manager is as follows:

Opening source query...

Establishing source record count...

Beginning integration...



DOC 1 ERROR: Error Executing Script 'GLTransaction.Journal Entry#' Line 9: - 

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Integration Failed

Integration Results

    1 documents were read from the source query.

    1 documents were attempted:

        0 integrated without warnings.

        0 integrated with warnings.

        1 failed to integrate.

The error indicates the is a problem with the data source name not being found, which leads to an object reference problem when the connection is attempted. But why would this code work on the server at times and not work on the client? Then it hit me!

The GPConnection object retrieves the connection and login information for the user currently signed on to Microsoft Dynamics GP... and therein lies the issue! The GPConnection object actually requires the Microsoft Dynamics GP user interface to be active for the object to retrieve the connection information, which is typically not the case for eConnect Adapter-based integrations.

As a side note, the times the integration did work, the user interface HAD to be active, but this was not apparent to the consultant.

So, how can we adjust this integration to follow best practices and work without the Microsoft Dynamics GP user interface having to be active?

The answer is relatively simple. The above code will need to switch out the way it obtains the connection string for an actual (as in hardcoded) connection string.

'

objConn.ConnectionString = "Provider=SQLNCLI10;Server=yourSQLServerName;_

Database=YourCompanyDB; Trusted_Connection=yes;" 

Because the script uses a trusted connection to the database (a best practice), it is advisable that proper permissions be granted to the user's domain account on SQL Server in order for the integration to be successful. The domain account will also need to be added to the DYNGRP role. What many customers have done is created specific domain accounts to execute eConnect integrations under a trusted connection. This further limits the exposure to security breaches.

For a final look at a technique to implement the above script, see the following article on this site:

Integration Manager: Integrating journal entries with Analytical Accounting Information

Hope you found this post useful.

Until next post!

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

Read More

Cannot insert the value NULL into column 'CONTACT' error when clicking on Items List in Navigation Pane

Moving on from my previous article on a similar subject - see Cannot insert the value NULL into column 'BASEUOFM' error when clicking on Items List in Navigation Pane, I recently came across this error, Cannot insert the value NULL into column 'CONTACT' when clicking on the All Purchasing Transactions list under the Purchasing Navigation Pane option, after performing an upgrade from Microsoft Dynamics GP 9.0 to Microsoft Dynamics GP 2010 R2.

All Purchasing Transactions list error - Purchasing Navigation List

The name of the global temp table - in this case, tempdb.dbo.##2093338- varies in almost all cases, but the end result of the error is the same. The issue has been identified running Microsoft Dynamics GP 2010 RTM, SP1 or SP2.

Upon further review, the issue is due to bad data in the Vendor ID (VENDORID) column in the Purchasing Receipt History table (POP30300). In summary, if you have a purchasing receipt with a blank vendor ID or a vendor ID that does not exist in the Vendor Master table (PM00200), it will cause the Items list to fail with the error above.

The following query should help in identifying the offending record(s):

' Created by Mariano Gomez, MVP

'  This code is licensed under the Creative Commons 

'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.

SELECT * FROM POP30300 WHERE VENDORID NOT IN (SELECT VENDORID FROM PM00200);

Once you have identified the record(s) causing the failure, you can use the Vendor Maintenance window to add the missing vendor or further study the issue to remove the offending receipts if necessary:

Vendor Maintenance window

Patrick Roth, Escalation Engineer with Microsoft and blogger at Developing for Dynamics GP, provides a full explanation of his troubleshooting method for this error at the Partner Online Technical Community forum:



GP2010 Purchasing List Error - Partner Online Technical Community forum


Until next post!

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

Read More

SQL: Assigning Microsoft Dynamics GP Users to SSRS Database Roles

As I begin to wrap up a Microsoft Dynamics GP 2010 R2 production upgrade from Microsoft Dynamics GP 9.0, I ran into a small issue at my client. After deploying the new SSRS reports, and as users were getting ready to try them out, we realized that some 15 logins needed to be assigned to a number of the 24 default database security roles created for the SSRS reports.

User Mappings (some information blurred to protect the client's identity)

This would be a bit cumbersome giving the share number of clicks required to accomplish this feat. In addition, we had just setup Microsoft Dynamics GP security, and given that the SSRS database roles were similar to those in GP, something needed to be done to automate the assignment of these roles based on Microsoft Dynamics GP security roles.

As a result, I created the following script:

-- Created by Mariano Gomez, MVP

--  This code is licensed under the Creative Commons 

--  Attribution-NonCommercial-ShareAlike 2.5 Generic license.

use DYNAMICS;

go



DECLARE @userid varchar(50), @companyid varchar(5), @securityroleid varchar(200), @ssrsRole varchar(200);

DECLARE @sqlStmt varchar(255);



DECLARE c_reportsecurity CURSOR FOR 

	SELECT a.USERID, b.INTERID, a.SECURITYROLEID FROM SY10500 a

		LEFT OUTER JOIN SY01500 b ON (A.CMPANYID = b.CMPANYID)

	WHERE a.USERID not in ('sa', 'DYNSA', 'LESSONUSER1', 'LESSONUSER2') and a.SECURITYROLEID NOT LIKE ('MBS%')

	ORDER BY a.USERID;



OPEN c_reportsecurity;

FETCH NEXT FROM c_reportsecurity INTO @userid, @companyid, @securityroleid;



WHILE @@FETCH_STATUS = 0

BEGIN

	SELECT @ssrsrole = 

		CASE 

			WHEN @securityroleid = 'ACCOUNTING MANAGER*      ' THEN 'rpt_accounting manager'

			WHEN @securityroleid = 'AP CLERK*                ' THEN 'rpt_accounts payable coordinator'

			WHEN @securityroleid = 'AR CLERK*                ' THEN 'rpt_accounts receivable coordinator'

			WHEN @securityroleid = 'BOOKKEEPER*              ' THEN 'rpt_bookkeeper'

			WHEN @securityroleid = 'CA AGENT*                ' THEN ''

			WHEN @securityroleid = 'CA MANAGER*              ' THEN ''

			WHEN @securityroleid = 'CA STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'CERTIFIED ACCOUNTANT*    ' THEN 'rpt_certified accountant'

			WHEN @securityroleid = 'CL AGENT*                ' THEN ''

			WHEN @securityroleid = 'CL DISPATCHER*           ' THEN 'rpt_dispatcher'

			WHEN @securityroleid = 'CL MANAGER*              ' THEN ''

			WHEN @securityroleid = 'CL STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'CUSTOMER SERVICE REP*    ' THEN 'rpt_customer service rep'

			WHEN @securityroleid = 'DP MANAGER*              ' THEN ''

			WHEN @securityroleid = 'DP STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'DP TECHNICIAN*           ' THEN ''

			WHEN @securityroleid = 'FA MANAGER*              ' THEN 'rpt_accounting manager'

			WHEN @securityroleid = 'FA STAKEHOLDER*          ' THEN 'rpt_certified accountant'

			WHEN @securityroleid = 'IT OPERATIONS MANAGER*   ' THEN ''

			WHEN @securityroleid = 'MBS DEBUGGER ADMIN       ' THEN ''

			WHEN @securityroleid = 'MBS DEBUGGER USER        ' THEN ''

			WHEN @securityroleid = 'OPERATIONS MANAGER*      ' THEN 'rpt_operations manager'

			WHEN @securityroleid = 'ORDER PROCESSOR*         ' THEN 'rpt_order processor'

			WHEN @securityroleid = 'PAYROLL CLERK*           ' THEN 'rpt_payroll'

			WHEN @securityroleid = 'PM AGENT*                ' THEN ''

			WHEN @securityroleid = 'PM MANAGER*              ' THEN ''

			WHEN @securityroleid = 'PM STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'POWERUSER                ' THEN 'rpt_power user'

			WHEN @securityroleid = 'PURCHASING AGENT*        ' THEN 'rpt_purchasing agent'

			WHEN @securityroleid = 'PURCHASING MANAGER*      ' THEN 'rpt_purchasing manager'

			WHEN @securityroleid = 'RT AGENT*                ' THEN ''

			WHEN @securityroleid = 'RT MANAGER*              ' THEN ''

			WHEN @securityroleid = 'RT STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'SHIPPING AND RECEIVING*  ' THEN 'rpt_shipping and receiving'

			WHEN @securityroleid = 'WAREHOUSE MANAGER*       ' THEN 'rpt_warehouse manager'

			WHEN @securityroleid = 'WENNSOFT SMS CONTRACTS*  ' THEN ''

			WHEN @securityroleid = 'WENNSOFT SMS DISPATCHER* ' THEN ''

			WHEN @securityroleid = 'WENNSOFT SMS POWER USER* ' THEN ''

			WHEN @securityroleid = 'WENNSOFT SMS SETUP*      ' THEN ''

			WHEN @securityroleid = 'WSJC ACCOUNTANT*         ' THEN ''

			WHEN @securityroleid = 'WSJC ACCOUNTING MANAGER* ' THEN ''

			WHEN @securityroleid = 'WSJC ADMIN*              ' THEN ''

			WHEN @securityroleid = 'WSJC BILLING CLERK*      ' THEN ''

			WHEN @securityroleid = 'WSJC POWERUSER*          ' THEN ''

			WHEN @securityroleid = 'WSJC PROJECT MANAGER*    ' THEN ''

			WHEN @securityroleid = 'WSTT PAYROLL CLERK*      ' THEN ''

			WHEN @securityroleid = 'WSTT POWERUSER*          ' THEN ''

		END

	

	IF (@ssrsRole <> '') 

	BEGIN

		SET @sqlStmt = 'USE ' + rtrim(@companyid) + '; EXEC sp_addrolemember ' + QUOTENAME(@ssrsRole, '''') + ',' + QUOTENAME(rtrim(@userid), '''');

		EXEC(@sqlStmt);

	END

	FETCH NEXT FROM c_reportsecurity INTO @userid, @companyid, @securityroleid;

END



CLOSE c_reportsecurity;

DEALLOCATE c_reportsecurity;

The script looks at the Security Assignment User Role table (SY10500) and retrieves the physical company database from the Company Master table (SY01500), then assign an SSRS database security role to each of the Microsoft Dynamics GP default roles. If a role does not exist, you can choose to leave the assignment blank.

The script then proceeds to evaluate the database security role obtained, then creates a SQL string that can be executed. The SQL string uses the sp_addrolemember system stored procedure to add the corresponding SQL login to the role. A cursor is used to loop through each user, company, and security role combination to obtain and assign the proper SSRS database role.

You can choose to add custom security roles or roles for third party applications that deploy their own SSRS reports to the above script.

This definitely helped saving some time... phew!

Until next post!

MG.-

Mariano Gomez, MVP

IntellPartners, LLC

http://www.IntellPartners.com/

Read More

Cannot insert the value NULL into column 'BASEUOFM' error when clicking on Items List in Navigation Pane

Just recently a few partners began reporting getting the error Cannot insert the value NULL into column 'BASEUOFM' when clicking on the Items list under the Inventory Navigation Pane option.




Items list error

  The name of the global temp table - in this case, tempdb.dbo.##0251007 - varies in almost all cases, but the end result of the error is the same. The issue has been identified running Microsoft Dynamics GP 2010 RTM, SP1 or SP2.

Upon further review, the issue is due to bad data in the Unit of Measure Schedule (UOMSCHDL) column in the Item Master table (IV00101). In summary, if you have an item record with a blank Unit of Measure Schedule or a Unit of Measure Schedule that does not exist in the Unit of Schedule Master table (IV40201), it will cause the Items list to fail with the error above.

The following query should help in identifying the offending record(s):

' Created by Mariano Gomez, MVP

'  This code is licensed under the Creative Commons 

'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.

SELECT * FROM IV00101 WHERE UOMSCHDL NOT IN (SELECT UOMSCHDL FROM IV40201);

Once you have identified the record(s) causing the failure, you can use the Item Maintenance window to correct the problem:

Item Maintenance window

Until next post!

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

Read More

SmartList Builder and creating Calculated Fields with Extender data

Just recently, I ran into a case where the partner was creating a SmartList Builder calculated field using data from the RM Open table (RM20101) via an Left Join table operation with the Extender Window Field Numbers. In fact, this is a very typical scenario for a lot of deployments where Extender is used, especially when you cannot use the standard Extender functionality to integrate with out-of-the-box smartlists.

The original SLB calculated field look something like this:

Extender Calculated Field

When the SLB smartlist was deployed, a number of results came back as zero for the records where there was no entry in the Extender Window Field Numbers table, even when the Sales Amount and Current Trx Amount fields had a value in the RM Open File table.

Paying a bit more attention to the issue made me think of how LEFT OUTER JOINs are processed by the Microsoft SQL Server query engine. This is best illustrated with the following example:

RM Open				Extender
Customer Number	Document Number	Sales Amount	Current Transaction Amount	PT UD Key	PT UD Number	Total
AARONFIT001	INV3223	200.00	40.00	INV3223	5.00	5.00
ADAMPARK001	INV1020	100.00	20.00	NULL	NULL	NULL

Note that if Extender data was not entered for INV1020, a left outer join query would produce a NULL value as a result of the join operation. To overcome this situation, we applied the T-SQL ISNULL() function to the Extender field in SmartList Builder. Since SmartList Builder uses pass-through SQL to build each portion of the SELECT statement used to retrieve the records, then this should work just fine. The final calculated field is as follows:

Remember, Extender is a valuable tool to capture additional data and enhances the value of SmartList Builder when combined together to deliver reports. Pay special attention when creating calculated fields that rely on information from Extender tables in left join scenarios.

Until next post!

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

Read More

SSRS: GL Trial Balance Summary report returns no data

This one comes courtesy of my friend Steve Sieber at McGladrey.

After installing Microsoft Dynamics GP 2010 R2 and deploying the SQL Server Reporting Services reports, you will encounter an issue when printing the GL Trial Balance Summary SRS report located under Financial.

1. Launch Report Manager and click on the company for which you would like to run the report (the issue can also be reproduced in the Fabrikam (TWO) company database). Click on Financial | Trial Balance Summary, enter all the parameters and options for the report the click on View Report, the following is returned:

GL Trial Balance Summary - SSRS

As you can see, even though the correct parameters are selected, the report returns no records.

2. If the GL Trial Balance Summary report is executed from GP with the same parameters, the report correctly delivers the expected records and result:

GL Trial Balance Summary - GP

From a technical perspective, the GL Trial Balance Summary SSRS report executes the dbo.seeGLPrintSRSTrialBalance stored procedure. The issue appears to be that the #GLTBDTemp temp table does not get populated with the records needed to render the report. You can test the stored procedure by executing the following statement from SQL Server Management Studio against any company database.

exec seeglPrintSRSTrialBalance 0,0,0,'000-0000-00','999-9999-99','01/01/2017','12/31/2017',2017,0,1,1

This issue has been reproduced by Microsoft Support and they are currently researching the problem for a solution. 

Until next post!

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

Read More

Why shouldn't I shrink my Microsoft Dynamics GP databases?

A client recently approached me with the question of whether they should shrink their Microsoft Dynamics GP databases to reclaim hard disk space, but instead of telling you what I think, I will demonstrate some of the issues arising from shrinking your databases:

Now to what I think...I have never been a big fan of shrinking databases to reclaim hard disk space -- though, if you are running a dev environment where space is critical, then this may only be the one time. The problem arises from the way the shrink process occurs, and applies to DBCC SHRINKFILE, DBCC SHRINKDATABASE and the Auto Shrink setting in the database properties.

In summary, SQL Server goes to the end of a dabatabase file, picks up each individual page, then moves them to the first available empty space in the file. This process may reverse the order of your pages, turning perfectly defragmented indexes into perfectly fragmented ones.

So, let's take a look with a test database in one of my client's environments:

1. The first thing we will do is take a look at the stats on the GL00100 table by running the Microsoft SQL Server sys.dm_db_index_physical_stats function:

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'TWO'), OBJECT_ID(N'dbo.GL00100'), NULL, NULL , 'LIMITED');

The following are the results from those stats:

Original database stats

 Now, in particular, I have highlighted the Average Fragmentation in Percent and the Average Fragment Size in Pages columns. Also, note that I have executed the function in 'LIMITED' mode, which combines information from all the columns that form part of each index. So, while these fragmentation levels would indicate low defragmentation rates -- which is always desirable -- it means there is still room for improvement. So, let's see what happens after rebuilding the indexes on GL00100 for our test database, then rerunning the stats:

Stats after rebuilding indexes

As you can tell now, we have no fragmentation and our page size utilization went up - this is what we would expect after rebuilding indexes. So let's see what happens when we run the shrink process on the database:

Stats after DBCC SHRINKDATABASE

You can now tell that perfectly defragmented indexes now appear fragmented and even to a higher degree than what we started out with. These levels of fragmentation can cause serious performance issues in a production envrionment where database maintenance procedures are not properly planned and executed.

If you must reclaim hard disk space in your Microsoft Dynamics GP environment, please consult with your database administrator, but also keep in mind that storage is dirt cheap.

Until next post!

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

Read More