Prev
Next
Forums Lawson Business Intelligence/Reporting/Crystal

User structure / rights

Sort:
You are not authorized to post a reply.
Author
Messages
Greg Moeller
Veteran Member
Posts: 1498
Veteran Member
3/7/2013 9:46 PM
    Anyone out in Guru-Land have a Crystal report or a SQL statement used to extract the 'Maintain Rights' for all of the users that they would be willing to share? Structures / Groups / Etc

    I'm not even sure where to start, I've been away from this for too long.

    Thanks in Advance,
    -Greg
    Georgette
    Veteran Member
    Posts: 52
    Veteran Member
    3/8/2013 11:20 AM
      Hi Greg,

      I use Crystal. The tables are ERS_RULEMASTER, ERS_RULEDETAIL and ERS_RULEMAPPINGS. The link is RULEID for all three tables.
      JeanneS
      Veteran Member
      Posts: 49
      Veteran Member
      3/8/2013 11:26 AM
        Hi Greg,

        Here is my SQL for reporting on rights:

        SELECT DISTINCT "ERS_RULEMASTER"."RULEID", "ERS_RULEMASTER"."RULENAME", "ERS_RULEMASTER"."STRUCTUREID", "ERS_RULEMASTER"."RULEOWNER", "ERS_RULEMASTER"."STARTDATE", "ERS_RULEMASTER"."ENDDATE", "ERS_RULEVALUES"."ELEMENTVALUE1", "ERS_RULEVALUES"."ELEMENTID", "ERS_ELEMENTS"."ELEMENTNAME", "ERS_RULEMAPPINGS"."CONSUMERID"
        FROM ((("RSUSER"."ERS_RULEMASTER" "ERS_RULEMASTER" LEFT OUTER JOIN "RSUSER"."ERS_RULEDETAIL" "ERS_RULEDETAIL" ON "ERS_RULEMASTER"."RULEID"="ERS_RULEDETAIL"."RULEID") INNER JOIN "RSUSER"."ERS_RULEMAPPINGS" "ERS_RULEMAPPINGS" ON "ERS_RULEDETAIL"."RULEID"="ERS_RULEMAPPINGS"."RULEID") INNER JOIN "RSUSER"."ERS_RULEVALUES" "ERS_RULEVALUES" ON "ERS_RULEMAPPINGS"."RULEID"="ERS_RULEVALUES"."RULEID") LEFT OUTER JOIN "RSUSER"."ERS_ELEMENTS" "ERS_ELEMENTS" ON "ERS_RULEVALUES"."ELEMENTID"="ERS_ELEMENTS"."ELEMENTID"
        ORDER BY "ERS_RULEMASTER"."RULEID", "ERS_RULEVALUES"."ELEMENTVALUE1"


        Greg Moeller
        Veteran Member
        Posts: 1498
        Veteran Member
        3/8/2013 2:58 PM
          Thank you BOTH! I've combined both of your code into my own Crystal report. Just what the business analysts needed!!
          Matthew Nye
          Veteran Member
          Posts: 514
          Veteran Member
          3/8/2013 4:58 PM
            dont forget the issues:
            * If CONSUMERID is a group, you will need to join to ENPUSERMAP to get the members
            * If you are using the "|" to concatenate multiple values you will get multiple records for the same RULEID. If you want 1-to-1 youll need to do a subselect, store proc or in-memory table (WITH function)
            * The same goes for multiple Groups, Elements, Structures in the same right

            In case you forgot you had the same question last year

            https://www.lawsonguru.co...ts-in-crystal/#21466

            If any of my answers were helpful an endorsement on LinkedIn would be much appriciated! www.linkedin.com/pub/matthew-nye/1a/886/760/
            Greg Moeller
            Veteran Member
            Posts: 1498
            Veteran Member
            3/8/2013 5:58 PM
              Matthew: I realize that my question was similar last year... but what I got was a different report last year. Who has access to modules, dashboards, and links is what that last post produced for me.
              Not sure why I didn't get both reports from that same thought last year, but for whatever reason... probably me playing fireman or something - I never did get both reports.. until now.
              Many thanks again!
              You are not authorized to post a reply.