Security Views For Use In SmartList Designer: User Access & Granted Security Roles With Tasks & Operations

Microsoft Dynamics GPThe eighth and final view in the series, shows users with their access to companies, roles, tasks and security operations. I’ll state up front that this is probably the least useful fo the views due to the sheer number of rows that it returns.

CREATE VIEW [dbo].[uv_PI_UserAccessAndGrantedSecurityRolesWithTasksAndOperations] AS
SELECT
	['User Master'].USERID AS 'User ID'
	,['User Master'].USERNAME AS 'Username'
	,['User Master'].USRCLASS AS 'User Class'
	,ISNULL(['Class Master'].DSCRIPTN, '') AS 'User Class Description'
	,ISNULL(['Company Master'].INTERID, '') AS 'Intercompany ID'
	,ISNULL(['Company Master'].CMPNYNAM, '') AS 'Company Name'
	,ISNULL(['Security Assignment User Role'].SECURITYROLEID, '') AS 'Security Role ID'
	,ISNULL(['Security Roles Master'].SECURITYROLENAME, '') AS 'Security Role Name'
	,ISNULL(['Security Role Task Assignment'].SECURITYTASKID, '') AS 'Security Task ID'
	,ISNULL(['Security Task Master'].SECURITYTASKNAME, '') AS 'Security Task Name'
	,ISNULL(['Security Task Master'].SECURITYTASKDESC, '') AS 'Security Task Description'
	,ISNULL(['Security Resource Descriptions'].PRODNAME, '') AS 'Product Name'
	,ISNULL(['Security Resource Descriptions'].Series_Name, '') AS 'Series Name'
	,ISNULL(['Security Resource Descriptions'].DSPLNAME, '') AS 'Security Operation Name'
	,ISNULL(['Security Resource Descriptions'].TYPESTR, '') AS 'Security Operation Type'
FROM
	SY01400 AS ['User Master']
LEFT JOIN
	SY40400 AS ['Class Master']
		ON ['Class Master'].USRCLASS = ['User Master'].USRCLASS
LEFT JOIN
	SY60100 AS ['User-Company Access']
		ON ['User-Company Access'].USERID = ['User Master'].USERID
LEFT JOIN
	SY10500 AS ['Security Assignment User Role']
		ON ['Security Assignment User Role'].CMPANYID = ['User-Company Access'].CMPANYID
			AND ['Security Assignment User Role'].USERID = ['User-Company Access'].USERID
LEFT JOIN
	SY09100 AS ['Security Roles Master']
		ON ['Security Roles Master'].SECURITYROLEID = ['Security Assignment User Role'].SECURITYROLEID
LEFT JOIN
	SY10600 AS ['Security Role Task Assignment']
		ON ['Security Role Task Assignment'].SECURITYROLEID = ['Security Roles Master'].SECURITYROLEID
LEFT JOIN
	SY01500 AS ['Company Master']
		ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID
LEFT JOIN
	SY09000 AS ['Security Task Master']
		ON ['Security Task Master'].SECURITYTASKID = ['Security Role Task Assignment'].SECURITYTASKID
LEFT JOIN
	SY10700 AS ['Security Assignment Task Operations']
		ON ['Security Assignment Task Operations'].SECURITYTASKID = ['Security Task Master'].SECURITYTASKID
LEFT JOIN
	SY09400 AS ['Security Resource Descriptions']
		ON ['Security Resource Descriptions'].SECURITYID = ['Security Assignment Task Operations'].SECURITYID
GO

GRANT SELECT ON uv_PI_UserAccessAndGrantedSecurityRolesWithTasks TO DYNGRP
GO

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

Security Views For Use In SmartList Designer: Security Tasks & Operations

Microsoft Dynamics GPThe seventh SQL view in this series does not include users, but instead shows the tasks and the operations. To use this view you will need to have populated the Security Resource Descriptions table.

CREATE VIEW [dbo].[uv_PI_SecurityRolesWithTasksAndOperations] AS
SELECT
	['Security Task Master'].SECURITYTASKID AS 'Security Task ID'
	,['Security Task Master'].SECURITYTASKNAME AS 'Security Task Name'
	,['Security Task Master'].SECURITYTASKDESC AS 'Security Task Description'
	,['Security Resource Descriptions'].PRODNAME AS 'Product Name'
	,['Security Resource Descriptions'].Series_Name AS 'Series Name'
	,['Security Resource Descriptions'].DSPLNAME AS 'Security Operation Name'
	,['Security Resource Descriptions'].TYPESTR AS 'Security Operation Type'
FROM
	SY09000 AS ['Security Task Master']
INNER JOIN
	SY10700 AS ['Security Assignment Task Operations']
		ON ['Security Assignment Task Operations'].SECURITYTASKID = ['Security Task Master'].SECURITYTASKID
INNER JOIN
	SY09400 AS ['Security Resource Descriptions']
		ON ['Security Resource Descriptions'].SECURITYID = ['Security Assignment Task Operations'].SECURITYID
GO

GRANT SELECT ON uv_PI_SecurityRolesWithTasksAndOperations TO DYNGRP
GO

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

MDGP 2015 R2 Feature of the Day: Default SmartList Visibility

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 has been released and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The twenty fifth, and last, Feature of the Day is Default SmartList Visibility. When creating a new SmartList Favorite, the user has the option to select to whom the SmartList Favorite will be visible, including the following options: System, Company, User Class, and User ID. System is the default option selected for the visibility.

Add or Remove Favorites

Continue reading

MDGP 2015 R2 Feature of the Day: Display Debits before Credits

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 has been released and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The twenty second Feature of the Day is . A top suggestion on the MS Connect site, this feature moves the Debit column to display before the Credit column by default in account transaction reporting options in which the Credit column previously displayed before the Debit column.

This applies to SmartLists and Excel Reports, as well as the database tables and views on which the reports are based.

Click to show/hide the MDGP 2015 R2 Feature of the Day Series Index

MDGP 2015 R2 Feature of the Day: SmartList Designer View Workflow Type

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 will soon be released (due late May/early June and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The fourteenth Feature of the Day is SmartList Designer View Workflow Type.

Within SmartList Designer a user can send a SmartList through workflow to create a SQL view based on the SmartList query. One active workflow will need to be setup in order for the SQL view to go through the approval process. SmartList workflows will be setup under the Administration series within the Workflow Maintenance window.

I’m interested in seeing this one in practice, but I am sceptical of the utility as there are not that many clients with technical users who would be able to do this type of thing who would also not be part of the IT Department with the ability and permissions just to create a view for SmartList.

Click to show/hide the MDGP 2015 R2 Feature of the Day Series Index

Security Views For Use In SmartList Designer: Security Roles With Tasks & Operations

Microsoft Dynamics GPThe sixth SQL view in this series does not include users, but instead shows the security roles, tasks and also the operations. To use this view you will need to have populated the Security Resource Descriptions table.

CREATE VIEW [dbo].[uv_PI_SecurityRolesWithTasksAndOperations] AS
SELECT
	['Security Roles Master'].SECURITYROLEID AS 'Security Role ID'
	,['Security Roles Master'].SECURITYROLENAME AS 'Security Role Name'
	,['Security Role Task Assignment'].SECURITYTASKID AS 'Security Task ID'
	,['Security Task Master'].SECURITYTASKNAME AS 'Security Task Name'
	,['Security Resource Descriptions'].Series_Name AS 'Series Name'
	,['Security Resource Descriptions'].DSPLNAME AS 'Security Operation Name'
FROM
	SY09100 AS ['Security Roles Master']
INNER JOIN
	SY10600 AS ['Security Role Task Assignment']
		ON ['Security Role Task Assignment'].SECURITYROLEID = ['Security Roles Master'].SECURITYROLEID
INNER JOIN
	SY09000 AS ['Security Task Master']
		ON ['Security Task Master'].SECURITYTASKID = ['Security Role Task Assignment'].SECURITYTASKID
INNER JOIN
	SY10700 AS ['Security Assignment Task Operations']
		ON ['Security Assignment Task Operations'].SECURITYTASKID = ['Security Task Master'].SECURITYTASKID
INNER JOIN
	SY09400 AS ['Security Resource Descriptions']
		ON ['Security Resource Descriptions'].SECURITYID = ['Security Assignment Task Operations'].SECURITYID
GO

GRANT SELECT ON uv_PI_SecurityRolesWithTasksAndOperations TO DYNGRP
GO

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

Security Views For Use In SmartList Designer: Populating Security Resource Descriptions

Microsoft Dynamics GPIn this post I’m taking a slight diversion and not posting a SQL view. The reason is that the next view I will be posting requires that the security resource descriptions be available which, by default, they’re not. However, they can be made available, which is what I will be covering in this post.

SY09400 is the Security Resource Descriptions table which is, by default, empty as the names are stored within the Microsoft Dynamics GP client itself. However, this table can be populated, perhaps somewhat counter-intuitively, by using the Clear Data window (Microsoft Dynamics GP menu >> Maintenance >> Clear Data).

To populate the table, open Clear Data, click on Display and then select Physical:

Clear Data

Continue reading

MDGP 2015 R2 Feature of the Day: SmartList Designer Create View

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 will soon be released (due late May/early June and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The thirteenth Feature of the Day is SmartList Designer Create View .

Within SmartList Designer a user can send a SmartList through workflow to create a SQL view based on the SmartList query.

This SQL view can then be used outside of GP for such purposes as creating reports using SQL reporting services or Power BI reports in Excel.

The view will also allow the user to publish the SmartList as a refreshable excel report from inside of Dynamics GP.

SmartList Designer

Click to show/hide the MDGP 2015 R2 Feature of the Day Series Index

MDGP 2015 R2 Feature of the Day: Analytical Accounting Transaction Lists

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 will soon be released (due late May/early June and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The fourth Feature of the Day is Analytical Accounting Transaction Lists.

This feature provides improvements to SmartLists and Excel Reports for Analytical Accounting (AA) transaction information. The first improvement is an expansion of the amount of information available to be included as columns in the AA SmartLists and Excel Reports. AA tracks a lot of information for each record, and we’ve included more of this data in the SmartLists and Excel Reports.

Microsoft have also improved the way the AA dimension code information displays in the lists. In prior releases, the lists contained a single column for the AA transaction dimension code. Thus, if the company is tracking more than one dimension on a transaction, multiple lines were required in the list for a distribution entry.

In GP2015 R2, they have updated the SmartLists and Excel Reports to display a column for each transaction dimension, showing the code assigned for that dimension.

For example, the below SmartList shows how the list would display for a company tracking Cost Center and Project as AA transaction dimensions.

Analytical Accounting SmartList

The change in how the data displays improves readability of the report, and also improves the ease with which users can summarize and work with the data in Excel using pivot table functionality.

Analytical Accounting is one of the modules which is not much used, but can add substantial analysis without needing the chart of accounts to be overly complicated.

If you’re thinking of starting to use Analytical Accounting, more information is available in my second book, Microsoft Dynamics GP Financial Management:

Amazon.com Amazon.co.uk

Click to show/hide the MDGP 2015 R2 Feature of the Day Series Index

Security Views For Use In SmartList Designer: Security Roles With Tasks

Microsoft Dynamics GPThe fourth view being posted in this series takes a step back from the users and just shows the role and tasks within.

CREATE VIEW [dbo].[uv_PI_SecurityRolesWithTasks] AS
SELECT
	['Security Roles Master'].SECURITYROLEID AS 'Security Role ID'
	,['Security Roles Master'].SECURITYROLENAME AS 'Security Role Name'
	,['Security Role Task Assignment'].SECURITYTASKID AS 'Security Task ID'
	,['Security Task Master'].SECURITYTASKNAME AS 'Security Task Name'
FROM
	SY09100 AS ['Security Roles Master']
INNER JOIN
	SY10600 AS ['Security Role Task Assignment']
		ON ['Security Role Task Assignment'].SECURITYROLEID = ['Security Roles Master'].SECURITYROLEID
INNER JOIN
	SY09000 AS ['Security Task Master']
		ON ['Security Task Master'].SECURITYTASKID = ['Security Role Task Assignment'].SECURITYTASKID
GO

GRANT SELECT ON uv_PI_SecurityRolesWithTasks TO DYNGRP
GO

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