Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

Create LBI user access rights in Crystal

 8 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
imty
New Member
Posts: 2
New Member
2/16/2012 5:15 PM
    Hello All,

    I am trying to create a crystal report that shows the users' and their bursting rights. This report is necessary so that payroll reports which are in our AS400 system can be mapped to this crystal report in LBI so that each user has access to the payroll report for their cost center only.

    I would appreciate if you could kindly enlighen me as to how I can locate the tables in LBI or whereever it may reside that holds the users' and their bursting rights in order to create the Crystal report.

    Your kind assistance and guidance in this regard is much appreciated.

    thanks,
    imty.
    Greg Moeller
    Veteran Member
    Posts: 1498
    Veteran Member
    2/16/2012 6:09 PM
      imty: I've been trying to write such a report for a while now. The closest I've come is who has access to Dashboards, Modules and Links on LBI itself. I ended up linking tables ENPBATREE, ENPENTRYACCESS and ENPUSERMAP. I suspect that you'd have to use some if not all of these tables.
      The closest I can come to helping you out is to point you to the ERDS section of this site for LBI Schema visual documentation, I'm afraid. Unless you'd like me to share my report, which I have no problem doing.
      imty
      New Member
      Posts: 2
      New Member
      2/16/2012 6:42 PM
        Thanks Greg for the quick response. If I may ask where can I find these tables. I don't see any of the tables you have indicated. Are these tables accessible from crytal. I am looking at the tables that precede with dbo in our PROD ODBC database table listing. Is this where it should be? Please kindly clarify.

        thanks,
        Imty.
        Matthew Nye
        Veteran Member
        Posts: 514
        Veteran Member
        2/16/2012 6:50 PM
          This is not such a difficult task depending on your implementation. If your rights are simple (one or two elements per structure, one user per right, one group per right). However if you are utilizing groups assigned to rights, uing multiple groups and multiple structures per right and element values are using the "|" concatenation to assign multiple values to an element and youre using a lot of the different operators (=, Range, like, etc) this query becomes very complicated. 

           
SELECT CONSUMERID User_Group, RULENAME RightName, STRUCTURENAME StructureName, ELEMENTNAME ElementName, ELEMENTVALUE1 ElementValue1, ELEMENTVALUE2 ElementValue2, OPERAND Operand, rd.RULEGROUPING StructureGroup
FROM dbo.ERS_RULEMASTER rm
	INNER JOIN dbo.ERS_RULEMAPPINGS rmp ON rm.RULEID=rmp.RULEID
	INNER JOIN dbo.ERS_STRUCTURE s ON rm.STRUCTUREID = s.STRUCTUREID 
	INNER JOIN dbo.ERS_ELEMENTREFERENCES er ON s.STRUCTUREID=er.REFERENCEID
	INNER JOIN dbo.ERS_ELEMENTS e ON er.ELEMENTID=e.ELEMENTID
	LEFT OUTER JOIN dbo.ERS_RULEDETAIL rd ON rm.RULEID=rd.RULEID
	LEFT OUTER JOIN ERS_RULEVALUES rv ON rm.RULEID=rv.RULEID AND rd.RULEGROUPING=rv.RULEGROUPING AND rd.STRUCTUREID=rm.STRUCTUREID AND rd.ELEMENTID=rv.ELEMENTID



          This will get you started. What it doesnt do:
          * 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

          Thanks to Chris Martin for sharing his script that got me started on this.

          hth
          Matt
          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
          2/16/2012 6:53 PM
            No, actually they are inside the LBI Framework services schema/database not within the Lawson schema/database.

            so ours are LAWFS.ENPBATREE etc.

            Yes, they would be accessible from Crystal should you create a datasource to point to them.
            mark.cook
            Veteran Member
            Posts: 444
            Veteran Member
            2/16/2012 6:56 PM
              I know this post is slightly off topic on where to find the bursting rights, but wanted to share how we handle this as we utilize data elements to pull reports for individuals like employee number, supervisor code, indirect supervisor code. This allows us to roll out reports to the users without bursting rights set up.

              For example, I have a PTO report that show transactions and a running balance. The report pulls only my information as we are using the user id tied to the employee number field to limit the records.

              I have a report based on my supervisor code that gives me employee details about my direct reports. This again doesn't require bursting as we are using the data in the report to filter the results.

              There is two schools of thought on this topic as many use bursting rights effectively and swear by them. I have pushed to use the data to filter the reports so I don't have to maintain another set of security in rights. Either gets you to the same place, its the work effort and resources that give you the options. By having an HR and Finance staff that keeps up with who has access to management reports either in the Supervisor table or GL Attributes, i can use what they do and save the effort of bursting or knowing who has access to a paricular report.
              Greg Moeller
              Veteran Member
              Posts: 1498
              Veteran Member
              2/16/2012 7:59 PM
                Thank you, Matt!! This works well!! Great job you and Chris!!!
                Greg Moeller
                Veteran Member
                Posts: 1498
                Veteran Member
                9/13/2012 8:06 PM
                  For those that have asked.
                  Attachments
                  Chris
                  Basic Member
                  Posts: 8
                  Basic Member
                  9/27/2012 1:27 PM
                    Greg -- thanks for that. I was thinking about something like this a week or two ago.

                    I took your query and modified it as follows:

                    SELECT ENPBATREE_1.ENTRYSTRING, ENPUSERMAP_1.USERNAME, ENPUSERMAP_2.USERNAME, ENPUSERMAP_1.ENTRYTYPE
                    FROM ENPENTRYACCESS ENPENTRYACCESS_1
                    JOIN ENPBATREE ENPBATREE_1 ON ENPENTRYACCESS_1.TREEENTRYID=ENPBATREE_1.TREEENTRYID
                    JOIN ENPUSERMAP ENPUSERMAP_1 ON ENPENTRYACCESS_1.ACCESSID=ENPUSERMAP_1.USERID
                    LEFT JOIN ENPGROUPMAP ENPGROUPMAP_1 ON ENPUSERMAP_1.ENTRYTYPE = 2 AND ENPUSERMAP_1.USERID = ENPGROUPMAP_1.GROUPID
                    LEFT JOIN ENPUSERMAP ENPUSERMAP_2 ON ENPGROUPMAP_1.USERID = ENPUSERMAP_2.USERID
                    ORDER BY ENPBATREE_1.ENTRYSTRING

                    This allows you to drill into the 2nd username when username is really a groupname, showing you the group members if desired. Just a way to go one step deeper to see who is a member of a group and truly has access...I don't always remember who is in what group.

                    Attachments