Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Sunday, March 30, 2014

Who are my Dynamics GP Power Users !


I have previously been asked to provide a report for Dynamics GP Power Users in several GP companies, here is an SQL scrip which provides the power users in all you GP Companies.
1

Tables Included:
·         SY01500 | Security Assignment User Role



SELECT  'Power USER' AS SecurityPrivilage ,
        [USERID] AS UserID ,
        [TWO] AS FabrikamDB ,
        [THREE] AS YourCompanyDB
FROM    ( SELECT    [INTERID] ,
                    [USERID] ,
                    [SECURITYROLEID]
          FROM      [DYNAMICS]..SY10500 AS A
                    INNER JOIN [DYNAMICS]..SY01500 AS B ON A.[CMPANYID] = B.[CMPANYID]
          WHERE     [SECURITYROLEID] = 'POWERUSER'
                    AND [INTERID] IN ( 'TWO', 'THREE' )
        ) P PIVOT ( COUNT([SECURITYROLEID]) FOR [INTERID] IN ( [TWO], [THREE] ) ) AS PVT


Explanation :
In order to run the above statement against your live GP Companies, you need to consider the following three steps;
1- On the select statement, add as many columns to reflect the GP Companies, in the example below we have (company two, three, four and five)

SELECT  'Power USER' AS SecurityPrivilage ,
        [USERID] AS UserID ,
        [TWO] AS FabrikamDB ,
        [THREE] AS YourCompanyDB
       [FOUR] AS YourCompanyDB,       
        [FIVE] AS YourCompanyDB
----------------------------------------------------------------------------

2- Secondly, On the where clause, include the company names as “TEXT” values under the[INTERID] field.
WHERE     [SECURITYROLEID] = 'POWERUSER'
         AND [INTERID] IN ( 'TWO', 'THREE', 'FOUR', 'FIVE' )


----------------------------------------------------------------------------

3- Finally, the pivot statement has to be modified to include the company values as shown below;

( COUNT([SECURITYROLEID]) FOR [INTERID] IN ( [TWO], [THREE], [FOUR], [FIVE] ) ) AS PVT



Best Regards,
Mahmoud M. AlSaadi

No comments:

Post a Comment