• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

User structure / rights

 5 Replies
 0 Subscribed to this topic
 23 Subscribed to this forum
Sort:
Author
Messages
Greg Moeller
Veteran Member
Posts: 1498
Veteran Member
MVP
MVP
You're an MVP!
New Poster
New Poster
Congrats on posting!
Engaged Reader
Engaged Reader
You are an engaged reader!
Avid Reader
Avid Reader
Avid Reader art thou!
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
MVP
MVP
You're an MVP!
New Poster
New Poster
Congrats on posting!
Engaged Reader
Engaged Reader
You are an engaged reader!
Avid Reader
Avid Reader
Avid Reader art thou!
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
MVP
MVP
You're an MVP!
New Poster
New Poster
Congrats on posting!
Engaged Reader
Engaged Reader
You are an engaged reader!
Avid Reader
Avid Reader
Avid Reader art thou!
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!