Reporting Services

Automating LBI Reporting Services rights

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):

  1. ERS_RULEMASTER – build the right framework based on an existing structure
  2. ERS_RULEDETAIL – build the filters e.g. how the elements match
  3. ERS_RULEVALUES – set the filter values
  4. 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

Advertisements

Lawson Security Reporting

Reporting over LAUA was easy because everything was in the database. With later versions of Lawson and the implementation of Lawson Security, everything is stored in your LDAP. (Personally, I think that storing the data in the LDAP is a relative non-issue. However, the implementation that Lawson has done is epically stupid; specifically how it handles the services (Employee, Vendor, Requester and Customer) as strings that must be parsed and cannot be logically searched. Seriously?) Since all of the data is in the LDAP, you have two choices: use the reporting options in the Lawson Security tool or dump the data to a database table. Chances are that you’ll actually use both options, but for very different reasons.

It wasn’t long after we implemented RM (Resource Manager) that we started to understand the potential (and realized) issues. Consequently, we started dumping sections of the LDAP to database tables. Primarily we used the functionality in Lawson Smart Notes to read the LDAP and save the data to a database table. (There is a strong argument for this approach. If you’re interested in how to do it, leave a comment and I’ll make a follow up post). There are several potential problems with this though, namely that we have to have a separate table for each tree in the LDAP and there is no relation between them. A secondary problem is that our LBI is on SQL Server and our Lawson S3 environment is on Oracle. Combining the data is possible in Crystal Reports but cumbersome. Outside of Crytal Reports (and MS Access) it’s “impossible”. We started going down the path of using Lawson Process Flow and Transformation Extender to read the LDAP and write to tables in our Lawson schema, but that’s easier said than done (specifically when you have M:1 relationships like in an employee proxy situation or with a contractor to vendor scenario). We were also doing an “implement as required” approach, which lead to an in-cohesive amalgamation of tables with a fair amount of duplicated data. If you know me at all, you’ll know that this kind of thing makes me crazy. Enter AVAAP. For the record, AVAAP (and their solution) came to us on a very high recommendation from a very trustworthy source (Moffitt Health whom we have a personal connection with and who routinely presents at CUE/Inforum).

If you have implemented Lawson security, or you are planning to implement it, and you own LBI, you should contact AVAAP and buy their Lawson Security Dashboard. As part of the product, you will get a pre-built Framework Services dashboard and 25+ reports that have a fair amount of customization available. I haven’t run across any aspects that it doesn’t report over and they also offer the ability to do Segregation of Duties reporting. Even more importantly, they deliver a COBOL program (that calls a Java program) and dumps the ENTIRE LDAP to a set of tables that can be run on demand (Ours takes about 20 minutes for 36,000 users). I honestly can’t say whether the reports that were delivered are being used except by our Auditing department, but we use the tables for all kinds of things.

Primarily our uses are to add user names to reports. For example, we have a report that we send out for “Unapproved Invoices”. When you run the query over the LOGAN tables, it only has the user name. This is of little help to most people in the case of jbrown45. However, when we join our reports to the tables that AVAAP creates, we’re able to provide the user’s name (James Brown) on the report with the same level of performance that we’ve come to expect from LBI.

My single strongest argument (and how I believe you can justify ROI) is the “Copy User” functionality that is the ultimate point of this post. I’m sure that everyone in a security role has at some point been asked to “set up user x to be exactly like user y”.
This is how we do it:

  • An LBI report is run that accepts a user name
  • The LBI report returns the full security setup in a CSV output (which is formatted to be opened in Excel)
  • The Security Group modifies necessary fields (name, employee, etc)
  • The output is passed through a Perl script that reformats into XML to be loaded by the Lawson “loaduser” script

The report could be formatted to produce the XML required by “loaduser”, but we have several other automated processes built around the same CSV format that are subsequently passed into the Perl script. (If you’re interested in the Perl script, leave a comment and I may post it.)

Without further ado, let me give you the single best reason to buy the AVAAP solution (Note that I am not affiliated with AVAAP, nor am I receiving compensation for my recommendation):
–This is Oracle 11g syntax, although I believe that much of it (like PIVOT) will work on SQL Server as well, but the Hierarchy queries will not

SELECT Trim(RES.ZSUID) AS ID, SSOP.RMID AS RMID, OS.OSID AS OSID, OS.OSPASSWORD AS OSPASSWORD,
  Trim(RES.ZSNAME) AS NAME, Trim(RES.FNAME) AS FirstName, Trim(RES.LNAME) AS LastName, RES.CHECKLS AS CheckLS,
  ZROLES.ROLE_LIST AS "ROLE", ZGROUPS.GROUP_LIST AS "GROUP", Trim(RES.DEFPRODLINE) AS ProductLine, RES.ZSACCESS AS "ACCESS", Trim(RES.ACCTUNTCTRL) AS AccountingUnitControl, Trim(RES.ACTVYGRPCOLCT) AS AcgrpCollect,
  Trim(ATTR.CUST_ATTR3) AS ACTimeApprover, Trim(RES.ACTVYLIST) AS ActivityList, RES.ADDINS AS ADDINS, RES.JOBQUEACCESS AS AllowJobQueue, '' AS COMMENTS, Trim(RES.COMPANYCTRL) AS CompanyControl,
  Trim(EMAIL) AS Email, Trim(ATTR.CUST_ATTR1) AS ESSGroup, Trim(ATTR.CUST_ATTR5) AS GLNode1, Trim(ATTR.CUST_ATTR6) AS GLNode2, GLS.GLS AS GLStructure, Trim(ATTR.CUST_ATTR7) AS HRNode1,
  Trim(ATTR.CUST_ATTR8) AS HRNode2, HRS.HRS AS HRStructure, Trim(ATTR.CUST_ATTR2) AS MSSGroup, 
  Trim(RES.LWSNOLEDB) AS OLEDBC, Trim(RES.PORTALADMIN) AS PortalAdmin, Trim(RES.PORTALROLE) AS PortalRole, '' AS PrimaryGroup, '' AS PrimaryStructure, Trim(RES.PRCSLVLCTRL) AS ProcessLevelControl,
  Trim(ATTR.CUST_ATTR10) AS PROXYACCESS, Trim(RES.WORKFLOWUSR) AS WFUser,
  EMP.SERVICE AS "EMPLOYEE^SERVICE", EMP.COMPANY AS "EMPLOYEE^COMPANY", EMP.EMPLOYEE AS "EMPLOYEE^EMPLOYEE", REQ.SERVICE AS "REQUESTER^SERVICE", REQ.REQUESTER AS "REQUESTER^REQUESTER",
  VEN.SERVICE AS "VENDOR^SERVICE", VEN.VENDOR_GROUP AS "VENDOR^VENDOR_GROUP", VEN.VENDOR AS "VENDOR^VENDOR"
FROM LAWSON.ZSRESOURCE RES
LEFT OUTER JOIN LAWSON.ZSCUSTATTR ATTR
  ON RES.ZSUID = ATTR.ZSUID
LEFT OUTER JOIN (
          SELECT ZSUID, Trim(ATTR_VALUE) AS GLS
          FROM LAWSON.ZSCUSTATR2
          WHERE ATTR_NAME = 'GLStructure'
  ) GLS ON RES.ZSUID = GLS.ZSUID
LEFT OUTER JOIN (
          SELECT ZSUID, Trim(ATTR_VALUE) AS HRS
          FROM LAWSON.ZSCUSTATR2
          WHERE ATTR_NAME = 'HRStructure'
  ) HRS ON RES.ZSUID = HRS.ZSUID
INNER JOIN (
    SELECT ZSUID, LTrim(SYS_CONNECT_BY_PATH(ZSROLE, ','),',') AS ROLE_LIST
    FROM (
      SELECT ZSUID, Trim(ZSROLE) ZSROLE, Row_Number() OVER (PARTITION BY ZSUID ORDER BY ZSUID, ZSROLE) ROWNUMBER
      FROM LAWSON.ZSLDAPRES
    )
    WHERE CONNECT_BY_ISLEAF = 1
    START WITH ROWNUMBER = 1
      CONNECT BY ZSUID = PRIOR ZSUID
        AND ROWNUMBER = (PRIOR ROWNUMBER + 1)
      ORDER SIBLINGS BY ZSUID, ZSROLE
  ) ZROLES ON RES.ZSUID = ZROLES.ZSUID
INNER JOIN (
    SELECT ZSUID, LTrim(SYS_CONNECT_BY_PATH(ZSGROUP, ','),',') AS GROUP_LIST
    FROM (
      SELECT ZSUID, Trim(ATTR_VALUE) ZSGROUP, Row_Number() OVER (PARTITION BY ZSUID ORDER BY ZSUID, ATTR_VALUE) ROWNUMBER
      FROM LAWSON.ZSRESATTR
      WHERE ATTR_NAME = 'Group'
    )
    WHERE CONNECT_BY_ISLEAF = 1
    START WITH ROWNUMBER = 1
      CONNECT BY ZSUID = PRIOR ZSUID
        AND ROWNUMBER = (PRIOR ROWNUMBER + 1)
      ORDER SIBLINGS BY ZSUID, ZSGROUP
  ) ZGROUPS ON RES.ZSUID = ZGROUPS.ZSUID
LEFT OUTER JOIN (
          SELECT ZSUID, Trim(ZSIDENTITY) AS SERVICE, Trim(ZSVALUE) AS REQUESTER
          FROM LAWSON.ZSLDAPIDEN
          WHERE ZSIDENTITY = 'PROD_REQUESTER'
  ) REQ ON RES.ZSUID = REQ.ZSUID
LEFT OUTER JOIN (
          SELECT * FROM (
            SELECT ZSUID, ZSIDENTITY SERVICE, ZSFIELD, Trim(ZSVALUE) AS ZSVALUE FROM LAWSON.ZSLDAPIDEN
            WHERE ZSIDENTITY = 'PROD_EMPLOYEE'
          )
          PIVOT
          ( Max(ZSVALUE) FOR ZSFIELD IN ('COMPANY' AS COMPANY, 'EMPLOYEE' AS EMPLOYEE) )
  ) EMP ON RES.ZSUID = EMP.ZSUID
LEFT OUTER JOIN (
          SELECT * FROM (
            SELECT ZSUID, ZSIDENTITY SERVICE, ZSFIELD, Trim(ZSVALUE) AS ZSVALUE FROM LAWSON.ZSLDAPIDEN
            WHERE ZSIDENTITY = 'PROD_VENDOR'
          )
          PIVOT
          ( Max(ZSVALUE) FOR ZSFIELD IN ('VENDOR_GROUP' AS VENDOR_GROUP, 'VENDOR' AS VENDOR) )
  ) VEN ON RES.ZSUID = VEN.ZSUID
LEFT OUTER JOIN (
          SELECT * FROM (
            SELECT ZSUID, ZSIDENTITY SERVICE, ZSFIELD, Trim(ZSVALUE) AS ZSVALUE FROM LAWSON.ZSLDAPIDEN
            WHERE ZSIDENTITY = 'PROD_CUSTOMER'
          )
          PIVOT
          ( Max(ZSVALUE) FOR ZSFIELD IN ('CUSTOMER_GROUP' AS CUSTOMER_GROUP, 'CUSTOMER' AS CUSTOMER) )
  ) CUST ON RES.ZSUID = CUST.ZSUID
LEFT OUTER JOIN (
          SELECT * FROM (
            SELECT ZSUID, ZSIDENTITY SERVICE, ZSFIELD, Trim(ZSVALUE) AS ZSVALUE FROM LAWSON.ZSLDAPIDEN
            WHERE ZSIDENTITY = 'PROD'
          )
          PIVOT
          ( Max(ZSVALUE) FOR ZSFIELD IN ('LOGIN' AS OSID, 'PASSWORD' AS OSPASSWORD) )
  ) OS ON RES.ZSUID = OS.ZSUID
LEFT OUTER JOIN (
          SELECT ZSUID, Trim(ZSVALUE) AS RMID
          FROM LAWSON.ZSLDAPIDEN
          WHERE ZSIDENTITY = 'SSOP'
  ) SSOP ON RES.ZSUID = SSOP.ZSUID
WHERE RES.ZSUID IN (
  SELECT ZSUID FROM LAWSON.ZSRESOURCE
  WHERE ZSUID = 'jdoe'
  )

Using this report, the subsequent Perl script, and the Lawson provided loaduser, we can produce an EXACT copy of a user from production to test in about 2 minutes. A copy from Prod to Prod takes a little more, as there is some time spent editing the file (username, employee, etc). Still, how does 2 minutes compare to YOUR user “copy” process?

HTH

LBI: Reporting Services Miscellany

This post is about LBI Reporting services tips and tricks. I didn’t have enough from each topic to make individual posts, so I’ve consolidated them. Buyer beware, most of what follows isn’t supported by Lawson. I haven’t had any problems with it, but you need to make sure you test it in your environment before you do it in production. I’ve marked the ones that are not supported with **.

— Renaming Reports** —
Just so we’re clear, I’m not talking about renaming the file produced, only the instance name that is displayed to the users. If you open the details page of an historical instance, you can change the name on the ‘General Properties’ screen. This is nice, but I don’t really like to do things manually. Instead, you can update the table directly. The SQL would look something like this:

UPDATE ERS_REPORTS
SET INSTANCENAME = 'MY INSTANCE NAME'
WHERE REPORTID = 64
  AND INSTANCEID = 879

This will update the name displayed to the users for report 64, instance 879. Chance are you’re going to script this, so you’ll need to determine for yourself how to determine the correct instance (if it even matters).

— Re-instating Historical Instances** —
Inevitably, even after many discussions regarding archiving policies and report retention, someone will either want an old report that’s been archived or they will want to change the policy. While changing the policy is fine, it doesn’t bring back the old reports. If the reports are time sensitive (as in, they are dependent upon the time they were run), you can’t just re-run the reports. If the physical report files still exist in your archive directory, you can restore them for the users. The archive directory is set in the “System Settings” setup under Report Administration.
There are four steps to re-instating an archived report.

  1. Change your archive policy for this report. If you don’t, after you’ve re-instated the reports they will just get archived again.
  2. Copy the entire instance folder and sub-folders back into your repository location. If your archive directory was called “Archive”, it might look like this: Archive/[ReportName]/Instances/[InstanceName]. You want to copy the [Instance Name] folder back into the “Instances” folder in the document repository directory.
  3. Insert a record into the ERS_REPORTS table so that LBI knows it’s there. The best thing to do is to find an existing record for the report and insert another record. Make sure that you update the INSTANCEID to match what you copied back in #2. It is helpful to the users to have the correct dates on it, so you should also change the CREATED and LASTMODIFIED fields to the appropriate dates based on the files in #2 as well.
  4. Give users access. You will need to add back access records in ERS_REPORTACESS so the users can see the report. Again, the best thing to do is to find another report that has the appropriate setup and copy those records. The only thing you should have to change this time is the Instance ID on the records.

If your report has bursting, you may have to add back additional records as well. I’ve never had to do it, so I could only speculate on what would need to be done.

— Linking to other LBI reports —
This is one of my favorite tricks and it’s a huge value-add. Many times, when discussing report requirements, users will want a high-level summary to start, but they also want to have the ability to drill down into the exact transactions that make up the total. As Lawson users, this is something that we’ve become accustomed to and is one of the best features of using Lawson applications. However, it creates a real problem when you’re writing reports. The last thing you want to do is to run a report with a lot of detail when people may not look at the detail. For one thing, it takes a lot of time (and resources), and for another, it becomes very difficult to actually write the report. My personal opinion is that if you exceed two group levels in Crystal then you need to give very serious consideration as to WHY you’re including that many levels.

How I deal with this is to write simple reports over basic information and provide the user with links to other LBI reports to get more information. For example, I might write a report that shows a list of activities (from the ACACTIVITY table) where the current user is the Project Manager. Each activity listed is a hyperlink that links to an Account Category totals view. In the Account Category totals view, you can then drill to the actual transactions that make up the total for the account category. This method had many advantages. It makes each report run very quickly (which makes the users happy), and it means that we can gather additional information from the user prior to trying to pull detail information (like data from the ACTRANS table) which makes the detail report more efficient. For each level that we need to drill, we can pass additional information. Each “drill” report has parameters that correspond to our drill information. Perhaps not surprisingly, users don’t seem to care that it’s not drilling in the same report as it’s actually hard to tell that it’s not. Our users more often comment that they prefer it because it opens in a new window.

You can add the hyperlinks by right-clicking on a field and selecting “Format Field”, then selecting the “Hyperlink” tab and clicking on the Formula editor next to the hyperlink box.
For example, in the “Manager Summary” report mentioned above, each activity has a link like so:

"ersRequestHandler.jsp?&Destination=Browser&Refresh=TRUE&InstanceId=0&DoBeforeEvent=False&NewReport=TRUE&ReportID=277&InstanceName=VIEW&ParmActivitySelect=" + {cmd_Activity.ACTIVITY}

This will open the Account Category view report. Note that you’ll need to point to the correct report id and change the parameter to your parameter name. The Account Category view has links on each Account Category that look like:


"ersRequestHandler.jsp?&Destination=Browser&Refresh=TRUE&InstanceId=0&DoBeforeEvent=False&NewReport=TRUE&ReportID=278&InstanceName=VIEW&ParmActivitySelect=" + {ACACTIVITY.ACTIVITY} + "&ParmCatSelect=" + UrlEncode(Space(5-len({cmdAmounts.ACCT_CAT})))

This report then has another link that opens another report…and you get the idea. Each report is highly specific as to the detail it pulls, which makes it more efficient and makes each report easier to manage, but it makes managing the group of reports more difficult. Any parameter or report id changes could result in the need to update/change reports that link to/from it.

I have another post on scripting LBI reports that I had to pull because it contained inaccurate information. I will update it and post again. I will update this post after that happens.

HTH