Create LBI user access rights in Crystal

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3288
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1348
Roger French	1311
mark.cook	1244

imty

New Member

Posts: 2

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

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

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

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

Greg Moeller

Veteran Member

Posts: 1498

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

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

2/16/2012 7:59 PM

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

Greg Moeller

Veteran Member

Posts: 1498

9/13/2012 8:06 PM

For those that have asked.

Attachments

001_WhoHasAccess.rpt

Chris

Basic Member

Posts: 8

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

002_WhoHasAccess.rpt