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.

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