Create LBI user access rights in Crystal

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.

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.

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.

[code] 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 [/code]


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

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.

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.

Thank you, Matt!! This works well!! Great job you and Chris!!!

For those that have asked.

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.