Who's On?

	Membership:
	Latest: Starr
	Past 24 Hours: 0
	Prev. 24 Hours: 0
	Overall: 5327

	People Online:
	Visitors: 138
	Members: 0
	Total: 138

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

User structure / rights

5 Replies

0 Subscribed to this topic

22 Subscribed to this forum

Sort:

Author

Messages

Greg Moeller

Veteran Member

Posts: 1498

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

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

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

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

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

Greg Moeller

Veteran Member

Posts: 1498

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!