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

September 23, 2011

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/

Advertisements

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

September 22, 2011

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/


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

August 30, 2011

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/


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

August 11, 2011

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/


SQL: Assigning Microsoft Dynamics GP Users to SSRS Database Roles

August 8, 2011

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/


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

August 3, 2011

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/


SmartList Builder and creating Calculated Fields with Extender data

July 14, 2011

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/