web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
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

I have the same question (0)
  • Victoria Yudin Profile Picture
    Victoria Yudin 22,768 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

  • Suggested answer
    Harold Worby Profile Picture
    Harold Worby 1,331 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

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans