Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / I am trying to create ...
Microsoft Dynamics GP (Archived)

I am trying to create a custom report in Crystal or SQL for GP10 that will display the user, the company(ies) they have access to, the role(s) for each company and the Alternate/Modified Forms and Reports ID.

(0) ShareShare
ReportReport
Posted on by howieb4 60

I am trying to create a custom report in Crystal or SQL for GP10 that will display the user, the company(ies) they have access to, the role(s) for each company and the Alternate/Modified Forms and Reports  ID. A Nolan customization we have has some users with access to a company but no roles or Reports ID. So in my report that is referencing the SY01500, SY60100, SY10550 and SY10500 tables, I am getting incorrect data. Any thoughts? Thanks

So the SY60100 shows a user has access to company X, Y, and Z. The SY10500 show only one entry for the user for X, because in User Security the users does not have anything assigned to them. But when I make my joins, my results are showing that the user has a role for Y and Z. Our auditors would like to see this detail. Thanks in advance.

HB

*This post is locked for comments

  • Suggested answer
    Harold Worby Profile Picture
    Harold Worby 1,325 on at
    RE: I am trying to create a custom report in Crystal or SQL for GP10 that will display the user, the company(ies) they have access to, the role(s) for each company and the Alternate/Modified Forms and Reports ID.

    SELECT S.USERID Login,N.USERNAME Name,N.USRCLASS Class, S.CMPANYID CompanyID,SECMODALTID Alt_Mod_ID,

    C.CMPNYNAM CompanyName, S.SecurityRoleID,

    coalesce(T.SECURITYTASKID,'') SecurityTaskID,

    coalesce(TM.SECURITYTASKNAME,'') SecurityTaskName,

    coalesce(TM.SECURITYTASKDESC,'') SecurityTaskDescription

    FROM SY10500 S   -- security assignment user role

    LEFT JOIN SY01400 N on N.USERID=S.USERID

    LEFT JOIN SY10550 M on M.USERID=S.USERID and M.CMPANYID=S.CMPANYID

    LEFT OUTER JOIN SY01500 C   -- company master

    ON S.CMPANYID = C.CMPANYID

    LEFT OUTER JOIN SY10600 T  -- tasks in roles

    ON S.SECURITYROLEID = T.SECURITYROLEID

    LEFT OUTER JOIN SY09000 TM  -- tasks master

    ON T.SECURITYTASKID = TM.SECURITYTASKID

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,766 on at
    Re: I am trying to create a custom report in Crystal or SQL for GP10 that will display the user, the company(ies) they have access to, the role(s) for each company and the Alternate/Modified Forms and Reports ID.

    HB,

    This might not be everything you need, but these scripts I have posted on my blog will hopefully give you a good start:

    Security Roles and Tasks in GP 10.0 and 2010: victoriayudin.com/.../sql-view-to-show-security-roles-and-tasks-in-dynamics-gp-10

    Security Details in GP 10.0 and 2010: victoriayudin.com/.../sql-view-with-security-resource-details-for-dynamics-gp-10

    Security and SmartList Details in GP 10.0 and 2010: victoriayudin.com/.../sql-view-with-security-and-smartlist-details-in-gp

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans