Security Views For Use In SmartList Designer: Group Based Company Access In Management Reporter

Microsoft Dynamics GPA while ago, I did a series of views on the Microsoft Dynamics GP security model. Well, a little after that I wrote a couple of scripts to allow the security configuration of Management Reporter to easily be enquired upon.

This, the second Management Reporter security script, shows security for users as granted by their Group membership. the previous post, on Friday, showed the user based company access.

The view is configured to read the security from a database called ManagementReporter and assumes the user who runs the report has select permissions on this database and relevant tables.

IF OBJECT_ID (N'uv_AZRCRV_GetManagementReporterGroupBasedSecurity', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetManagementReporterGroupBasedSecurity
GO
CREATE VIEW uv_AZRCRV_GetManagementReporterGroupBasedSecurity AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['Security User'].UserName AS 'Username'
	,['Security User Principal'].Name AS 'Domain Name'
	,['Security User'].LastLoginAttempt AS 'Last Login Attempt'
	,CASE ['Security User'].RoleType
		 WHEN 2 THEN
			'Viewer'
		 WHEN 3 THEN
			'Generator'
		 WHEN 4 THEN
			'Designer'
		 WHEN 5 THEN
			'Administrator'
		ELSE
			'None'
		END AS 'Role'
		,['Security Group Principal'].Name AS 'Group Name'
		,['Security Group Principal'].Description AS 'Group Description'
		,['Control Company'].Code AS 'INTERID'
		,['Control Company'].Name AS 'Company Name'
 FROM 
	Reporting.SecurityUser AS ['Security User'] WITH (NOLOCK)
INNER JOIN
	Reporting.SecurityPrincipal AS ['Security User Principal'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security User Principal'].ID
LEFT JOIN
	Reporting.SecurityGroupUser AS ['Security Group User'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security Group User'].UserID
LEFT JOIN
	Reporting.SecurityPrincipal AS ['Security Group Principal']  WITH (NOLOCK)
		ON
			 ['Security Group User'].GroupID = ['Security Group Principal'].ID
LEFT JOIN
	Reporting.SecurityCompanyPermission AS ['Security Company Group Permission'] WITH (NOLOCK)
		ON
			['Security Group Principal'].ID = ['Security Company Group Permission'].PrincipalID
LEFT JOIN
	Reporting.ControlCompany AS ['Control Company'] WITH (NOLOCK)
		ON
			['Security Company Group Permission'].CompanyID = ['Control Company'].ID
GO
GRANT SELECT ON uv_AZRCRV_GetManagementReporterGroupBasedSecurity TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

Security Views For Use In SmartList Designer: User Based Company Access In Management Reporter

Microsoft Dynamics GPA while ago, I did a series of views on the Microsoft Dynamics GP security model. Well, a little after that I wrote a couple of scripts to allow the security configuration of Management Reporter to easily be enquired upon.

This first script returns the security based on how the user is configured; the view I will post on Monday shows Group based security.

The view is configured to read the security from a database called ManagementReporter and assumes the user who runs the report has select permissions on this database and relevant tables.

IF OBJECT_ID (N'uv_AZRCRV_GetManagementReporterUserBasedSecurity', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetManagementReporterUserBasedSecurity 
GO
CREATE VIEW uv_AZRCRV_GetManagementReporterUserBasedSecurity AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['Security User'].UserName AS 'Username'
	,['Security User Principal'].Name AS 'Domain Name'
	,['Security User'].LastLoginAttempt AS 'Last Login Attempt'
	,CASE ['Security User'].RoleType
		 WHEN 2 THEN
			'Viewer'
		 WHEN 3 THEN
			'Generator'
		 WHEN 4 THEN
			'Designer'
		 WHEN 5 THEN
			'Administrator'
		ELSE
			'None'
		END AS 'Role'
	,['Control Company'].Code AS 'INTERID'
	,['Control Company'].Name AS 'Company Name'
FROM 
	ManagementReporter.Reporting.SecurityUser AS ['Security User'] WITH (NOLOCK)
INNER JOIN
	ManagementReporter.Reporting.SecurityPrincipal AS ['Security User Principal'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security User Principal'].ID
LEFT JOIN
	ManagementReporter.Reporting.SecurityCompanyPermission AS ['Security Company Permission'] WITH (NOLOCK)
		ON
			['Security User Principal'].ID = ['Security Company Permission'].PrincipalID
LEFT JOIN
	ManagementReporter.Reporting.ControlCompany AS ['Control Company'] WITH (NOLOCK)
		ON
			['Security Company Permission'].CompanyID = ['Control Company'].ID
GO
GRANT SELECT ON uv_AZRCRV_GetManagementReporterUserBasedSecurity TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

Hands On With Microsoft Dynamics GP 2016 R2: SmartList Designer Favorites In Advanced Lookups

Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components and also look at the new functionality introduced; the index for this series can be found here.

This is the first of the posts where I will be covering the new functionality introduced to Microsoft Dynamics GP 2016 R2. In this post I am going to cover the addition of SmartList Designer favorites in Advanced Lookups new feature.

To use this feature, create a SmartList Designer object:

SmartList Designer

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: SmartList Designer Favorites In Advanced Lookups”

MDGP 2016 R2 Feature of the Day: SmartList Designer SmartLists in Advanced Lookups

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2016 R2; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily.

The series index for this series of posts is here

The first Feature of the Day is SmartList Designer SmartLists in Advanced Lookups:

SmartList Favorite

Continue reading “MDGP 2016 R2 Feature of the Day: SmartList Designer SmartLists in Advanced Lookups”

MDGP 2016 R1 Feature of the Day: SmartLists from Favorites

As mentioned in the previous post, Microsoft Dynamics GP 2013 SP2 saw the introduction of SmartList Designer and have been gradually improving it since. This new feature allows a user to create a new SmartList from a favourite, instead of just from a SmartList Object:

SmartList

SmartList Designer

The main features of are:

  • A user can create a new SmartList from a favourite using Designer
  • The user doesn’t have to remove all extra columns from the default SmartList

This new feature will ease the creation of a SmartList Object simialr to an existing favourite instead of a copy of the default SmartList Object to which the favourite belongs.

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

MDGP 2016 R1 Feature of the Day: Export/Import in SmartList Designer

Microsoft Dynamics GP 2013 SP2 saw the introduction of SmartList Designer. It was reasonably useful, but nowhere near as useful as SmartList Builder. Little by little Microsoft are making SmartList Designer more useful. Microsoft Dynamics 2016 R1 actually makes it quote a bit more useful by adding the ability to export and import SmartLists:

Export/Import in SmartList Designer

Import in SmartList Designer

Export in SmartList Designer

One f the problems with SmartList Designer was that you had to create the SmartList on the server on which you wanted it; you could not develop it on a test system or development system and if, as a partner, you wanted to give it to another client you had to recreate it. This is why I often created SQL Views and plugged them in rather than creating the SmartList in the Designer.

I’ll need to do some testing to see how good the export/import functionality is, but this feature may allow us to put together SmartLists on our development system and then export them for clients.

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

SQL View To Allow Customer/Item Link In SmartList Builder

Microsoft Dynamics GPOn this one I am open to someone telling me that there is actually a way to do this out of the box, but I haven’t been able to find it. On a SmartList I was creating, I needed to pull the customer item number and description through to a SOP transaction SmartList, but couldn’t find any table to link with.

As a workaround, I created this view which contains the required link using the CUSTNMBR from SOP10100 (Sales Transaction Work) and the ITEMNMBR from SOP10200 (Sales Transaction Amounts Work):


CREATE VIEW uv_AZRCRV_CustomerItemLink AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
	SELECT
		SOP102.SOPNUMBE
		,SOP102.SOPTYPE
		,SOP102.LNITMSEQ
		,SOP101.CUSTNMBR
		,SOP102.ITEMNMBR
	FROM
		SOP10200 AS SOP102 WITH (NOLOCK)
	INNER JOIN
		SOP10100 AS SOP101 WITH (NOLOCK)
			ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPTYPE = SOP102.SOPTYPE
	INNER JOIN
		SOP60300 AS SOP603 WITH (NOLOCK)
			ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GO

GRANT SELECT ON uv_AZRCRV_CustomerItemLink TO DYNGRP
GO

SQL View To Return Purchasing Transactions With Multicurrency

Microsoft Dynamics GPA client I was working with recently was using two SmartLists, one for Payables Transactions and the other from Purchase Order Transactions, to generate a listing of purchasing transactions. However, they had to do manual fiddling around in Excel to get some of the formatting correct and didn;t have all of the information they wanted. Always up for a challenge, I had a go at producing a view we could plug in using SmartList Designer which would give them the information they wanted in the format the needed.

The below is the SQL view which was produced. It includes the PO and Receipt Numbers as well as the Originating Currency (with symbol) and exchange rate; the Functional Currency was left without a symbol so it can be easily totalled in Excel. Where the transaction does not have an Originating Currency I am outputting the Functional Currency.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE VIEW uv_AZRCRV_PurchasingTrxList AS
	SELECT
		PM.VCHRNMBR AS 'Voucher Number'
		,CASE WHEN PM.DOCTYPE = 1 THEN
			PM401.PMTRXDSC_1
		WHEN PM.DOCTYPE = 2 THEN
			PM401.PMTRXDSC_2
		WHEN PM.DOCTYPE = 3 THEN
			PM401.PMTRXDSC_3
		WHEN PM.DOCTYPE = 4 THEN
			PM401.PMTRXDSC_4
		WHEN PM.DOCTYPE = 5 THEN
			PM401.PMTRXDSC_5
		WHEN PM.DOCTYPE = 6 THEN
			PM401.PMTRXDSC_6
		WHEN PM.DOCTYPE = 7 THEN
			PM401.PMTRXDSC_7
		WHEN PM.DOCTYPE = 8 THEN
			PM401.PMTRXDSC_8
		ELSE
			'Unknown'
		END AS 'Document Type'
		,PM004.DOCTYPE
		,CASE WHEN PM004.DCSTATUS = 1 THEN
			'Work'
		WHEN PM004.DCSTATUS = 2 THEN
			'Open'
		WHEN PM004.DCSTATUS = 3 THEN
			'History'
		ELSE
			'Unknown'
		END AS 'Document Status'
		,PM004.DCSTATUS
		,PM.PORDNMBR AS 'PO Number'
		,ISNULL(POP303.POPRCTNM,'') AS 'Receipt Number'
		,PM.VENDORID AS 'Creditor Name'
		,PM002.VENDNAME AS 'Creditor ID'
		,CONVERT(VARCHAR,PM.DOCDATE,103) AS 'Document Date'
		,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END
			+ ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) 
			+ CAST(CAST(ISNULL(MC.OPURAMT,PM.PRCHAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Purchasing Amount'
		,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END 
			+ ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) 
			+ CAST(CAST(ISNULL(MC.ORTAXAMT,PM.TAXAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Tax Amount'
		,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END 
			+ ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) 
			+ CAST(CAST(ISNULL(MC.ORDOCAMT,PM.DOCAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Document Amount'
		,CASE WHEN MC.XCHGRATE IS NULL THEN 0 ELSE MC.XCHGRATE END AS 'Exchange Rate'
		,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.PRCHAMNT ELSE PM.PRCHAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Purchasing Amount'
		,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.TAXAMNT ELSE PM.TAXAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Tax Amount'
		,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.DOCAMNT ELSE PM.DOCAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Document Amount'
	FROM
		(
		-- Transaction History
		SELECT
			VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT
		FROM
			PM30200 WITH (NOLOCK)
		UNION ALL
			-- Transaction Open
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT
			FROM
				PM20000 WITH (NOLOCK)
		UNION ALL
			-- Transaction Work
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT
			FROM
				PM10000 WITH (NOLOCK)
		UNION ALL
			-- Payment Work
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,'',VENDORID,0,0,CHEKTOTL
			FROM
				PM10300 WITH (NOLOCK)
		UNION ALL
			-- Manual Payment Work
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,'',VENDORID,0,0,DOCAMNT
			FROM
				PM10400 WITH (NOLOCK)
		) AS PM
	 INNER JOIN
		PM00400 PM004 WITH (NOLOCK)
			 ON PM004.CNTRLNUM = PM.VCHRNMBR AND PM004.DOCTYPE = PM.DOCTYPE
	 INNER JOIN
		PM00200 AS PM002 WITH (NOLOCK)
			 ON PM002.VENDORID = PM.VENDORID
	 LEFT JOIN
		MC020103 AS MC WITH (NOLOCK)
			 ON MC.VCHRNMBR = PM.VCHRNMBR AND MC.DOCTYPE = PM.DOCTYPE
	 LEFT JOIN
		POP30300 AS POP303 WITH (NOLOCK)
			 ON POP303.VCHRNMBR = PM.VCHRNMBR
	 LEFT JOIN
		DYNAMICS..MC40200 AS MC402 WITH (NOLOCK)
			 ON MC402.CURRNIDX = MC.CURRNIDX
	 INNER JOIN
		MC40000 AS MC400 WITH (NOLOCK)
			 ON MC400.FUNLCURR = MC400.FUNLCURR
	 INNER JOIN
		DYNAMICS..MC40200 AS MC402F WITH (NOLOCK)
			 ON MC402F.CURRNIDX = MC400.FUNCRIDX
	 INNER JOIN
		PM40100 AS PM401 WITH (NOLOCK)
			 ON PM401.UNIQKEY = PM401.UNIQKEY
	ORDER BY PM.DOCDATE
 GO 

GRANT SELECT ON uv_AZRCRV_PurchasingTrxList TO DYNGRP
GO

SQL View For Payables Transaction Distribution Accounts

Microsoft Dynamics GPOne of the odd limitations in SmartList is the inability to run a report and get the distribution accounts for payables transactions; there is a field available for the different distributions, but these fields are from the Vendor Card, not the transaction. This view was the result of a query from someone; it returns the account number and description for the payables distributions.

CREATE VIEW uv_AZRCRV_PayablesTransactionDistributionAccounts AS

SELECT
	['PM Distribution WORK OPEN HIST'].VCHRNMBR AS 'Voucher Number'
	,CASE WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 1 THEN
		'Cash'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 2 THEN
		'Payable'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 3 THEN
		'Discount Available'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 4 THEN
		'Discount Taken'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 5 THEN
		'Finance Charge'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 6 THEN
		'Purchase'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 7 THEN
		'Trade Discount'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 8 THEN
		'Miscellaneous Charge'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 9 THEN
		'Freight'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 10 THEN
		'Taxes'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 11 THEN
		'Writeoffs'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 12 THEN
		'Other'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 13 THEN
		'GST Disc'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 14 THEN
		'PPS Amount'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 16 THEN
		'Round'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 17 THEN
		'Realized Gain'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 18 THEN
		'Realized Loss'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 19 THEN
		'Due To'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 20 THEN
		'Due From'
	END AS 'Distribution Type'
	,['PM Distribution WORK OPEN HIST'].DistRef AS 'Distribution Reference'
	,['Account Index Master'].ACTNUMST AS 'Account Number'
	,['Account Master'].ACTDESCR AS 'Account Description'
FROM
	(SELECT
		VCHRNMBR
		,DISTTYPE
		,DSTINDX
		,DistRef
	FROM
		PM10100 AS ['PM Distribution WORK OPEN HIST']
	UNION ALL
		SELECT
			VCHRNMBR
			,DISTTYPE
			,DSTINDX
			,DistRef
		FROM
			PM30600 AS ['PM Distribution History File']
	) AS ['PM Distribution WORK OPEN HIST']
INNER JOIN
	GL00105 AS ['Account Index Master']
		ON ['Account Index Master'].ACTINDX = ['PM Distribution WORK OPEN HIST'].DSTINDX
INNER JOIN
	GL00100 AS ['Account Master']
		ON ['Account Master'].ACTINDX = ['Account Index Master'].ACTINDX
GO

GRANT SELECT ON uv_AZRCRV_PayablesTransactionDistributionAccounts TO DYNGRP
GO

SQL View For Computer Cheques At Work Status To Check Email Enabled

Microsoft Dynamics GPWhile onsite with a client recently, I was asked if it was possible to view the payments in a computer checks batch and whether the creditors (vendors) were enabled for emailed remittances.

Out of the box no, but SmartList Designer can be used to do this. I created a new SQL view to get payments at a status of work and, in SmartList Designer, combined it with the remittance enabled view and vendor email addresses view I have previously posted.

I could have used the table in GP, but I find using views in SmartList Designer easier so created the view.

CREATE VIEW uv_AZRCRV_PaymentRunWORK AS

SELECT
	PM103.BACHNUMB AS 'Batch Number'
	,PM103.VENDORID AS 'Creditor ID'
	,PM2.VENDNAME AS 'Creditor Name'
	,PM2.VADDCDPR AS 'Primary Address'
	,PM2.VADCDTRO AS 'Remit To Address'
	,PM103.DOCDATE AS 'Document Date'
FROM
	PM00200 AS PM2
INNER JOIN
	PM10300 AS PM103 ON PM103.VENDORID = PM2.VENDORID
GO

GRANT SELECT ON uv_AZRCRV_PaymentRunWORK TO DYNGRP
GO

All of these views could have been created natively in SmartList Builder, but by creating them as views I can reuse them in SmartList Designer or any other reporting tool.