Lawson Business Intelligence (LBI) Reporting Services (RS) offers a lot of great functionality through the concept of “rights”. Rights allow you to do bursting and variable prompts within Crystal Reports. We use this functionality quite a bit for all kinds of things such as creating a right with an employee’s company and employee number so that they only see their own information or loading Activity codes for a project manager and setting that as a prompt so they can select from a list of their activities vs having to type in a cryptic code. This is important to us because we tend to use ODBC rather than DME, so we can’t use the inherent Lawson security that DME offers.
The big downside to using rights is that they’re difficult to load. As of now (LBI 10.4), you can either create the right manually or you can create a file and load it manually. I dislike both of those options because they both involve the word “manual”. We have an enhancement request in to create web calls to allow us to update rights, but it doesn’t exist yet. Consequently, if I really want to automate the loading of rights to LBI, I’m going to have to break the rules.
PLEASE NOTE THAT WHAT I AM ABOUT TO DISCUSS IS NOT SUPPORTED BY LAWSON/INFOR (OR ME). IF YOU CHOOSE TO IMPLEMENT THIS, YOU ARE ON YOUR OWN. YOU HAVE BEEN WARNED.
We have chosen to update the RS rights tables in LBI directly using SQL. You can choose any manner of methodologies to do the updates, but my preference is using TX as part of a process flow to do the updates. We do a full compare between what is currently in Lawson and what is in LBI and then perform the updates accordingly.
In order to add rights to LBI, you need to perform four table updates in the following order (these tables should be in your LawsonRS database):
- ERS_RULEMASTER – build the right framework based on an existing structure
- ERS_RULEDETAIL – build the filters e.g. how the elements match
- ERS_RULEVALUES – set the filter values
- ERS_RULEMAPPINGS – Associate the user to the right
If you are deleting rights, you should perform the updates in the reverse order as above.
In the below examples, I will be creating a right for my id called EMPLOYEE. The structure has two elements: COMPANY and EMPLOYEE which correlate to the primary key for the Lawson EMPLOYEE table and will help me to limit HR records for just the person running the report. I’ve chosen to show an example with two elements so that you can see the harder version. It should be easy to to either scale it up or down depending on your needs. These examples are for SQL Server.
--ERS_RULEMASTER
INSERT INTO [LawsonRS].[dbo].[ERS_RULEMASTER]
([RULEID]
,[RULENAME]
,[RULEDESCRIPTION]
,[STRUCTUREID]
,[RULEOWNER]
,[STARTDATE]
,[ENDDATE]
,[STATUS]
,[ALLOWPARTIAL]
,[RULEORDER])
VALUES
(867
,'EMPLOYEE-12345'
,'Employee Filter'
,11
,'APPSUPPORT'
,CURRENT_TIMESTAMP
,'2080-08-14 04:00:00.000'
,'A'
,'Y'
,0)
GO
--ERS_RULEDETAIL
INSERT INTO [LawsonRS].[dbo].[ERS_RULEDETAIL]
([RULEID]
,[ELEMENTID]
,[RULEGROUPING]
,[STRUCTUREID]
,[OPERAND]
,[ELEMENTVALUE2]
,[EXCLUDEINCLUDE]
,[ELEMENTORDER])
VALUES
(867
,1
,1
,11
,'equal to'
,null
,'i'
,1),
(867
,8
,1
,11
,'equal to'
,null
,'i'
,2)
GO
--ERS_RULEVALUES
INSERT INTO [LawsonRS].[dbo].[ERS_RULEVALUES]
([RULEID]
,[ELEMENTID]
,[RULEGROUPING]
,[STRUCTUREID]
,[SEQUENCEID]
,[ELEMENTVALUE1])
VALUES
(867
,1
,1
,11
,1
,'1'), --company number
(867
,8
,1
,11
,1
,'12345') --employee id
GO
--ERS_RULEMAPPINGS
INSERT INTO [LawsonRS].[dbo].[ERS_RULEMAPPINGS]
([CONSUMERID]
,[RULEID]
,[CONSUMERTYPE])
VALUES
('myid'
,867
,1)
GO
Obviously there are some hard-coded values in here that you’re going to have to figure out. First things first, let’s figure out the RULEID that we’re going to create. You will need to add 1 to the value returned by this query.
SELECT MAX(RULEID) AS LASTRULEID FROM [LawsonRS].[dbo].[ERS_RULEMASTER]
Next we need to get the Structure ID for the EMPLOYEE structure that we’re updating. You should be able to retrieve this by checking the ERS_STRUCTURE table.
SELECT STUCTUREID FROM [LawsonRS].[dbo].[ERS_STRUCTURE] WHERE STRUCTURENAME = 'EMPLOYEE'
Finally, we need to get the Element IDs for the elements of the structure we updating/creating. This is in the ERS_ELEMENTS table.
SELECT ELEMENTID FROM [LawsonRS].[dbo].[ERS_ELEMENTS] WHERE ELEMENTNAME = 'EMPLOYEE'
Finally, here is a sample query on existing rights for you to compare your Lawson data to to create the updates.
SELECT MAP.CONSUMERID, RM.RULENAME, S.STRUCTUREID, S.STRUCTURENAME, E.ELEMENTID,
E.ELEMENTNAME, RD.OPERAND, RV.ELEMENTVALUE1, RD.RULEGROUPING, RD.ELEMENTORDER,
RM.RULEOWNER, RM.STARTDATE, RM.ENDDATE
FROM ERS_RULEMASTER AS RM
INNER JOIN ERS_RULEDETAIL AS RD
ON RM.RULEID = RD.RULEID
INNER JOIN ERS_RULEVALUES AS RV
ON RM.RULEID = RV.RULEID
AND RD.ELEMENTID = RV.ELEMENTID
AND RD.RULEGROUPING = RV.RULEGROUPING
AND RD.STRUCTUREID = RV.STRUCTUREID
INNER JOIN ERS_STRUCTURE S
ON RD.STRUCTUREID = S.STRUCTUREID
INNER JOIN ERS_ELEMENTS E
ON RD.ELEMENTID = E.ELEMENTID
INNER JOIN ERS_RULEMAPPINGS MAP
ON RD.RULEID = MAP.RULEID
WHERE S.STRUCTURENAME = 'EMPLOYEE'
ORDER BY MAP.CONSUMERID
So there you have it. The ability to automate the loading of rights into Lawson LBI Reporting Services.
HTH
What I learned today 