LBI

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

Building an LDAP Infoset in Lawson Smart Notes

There is quite a bit of value in bringing your Lawson S3 LDAP or other LDAP data into Smart Notes. Once it’s in an infoset, you can use the data in other Smart Notes for bursting, delivery, etc. We actually use it as an auditing tool for security changes. We build infosets on the Lawson security data and check for new keys each day. This assures the auditors that any changes we make are through the established change control process. Building an LDAP infoset is actually quite simple, but maddeningly hard if you don’t know some of the specifics. Gary Garner from Lawson was the one who originally showed me how to do it. The examples below are of a simple infoset that we use for bursting.

First create a new infoset with an LDAP source as the below image shows.
SNLDAPConnection

  • The user id and password should be for a user that has the ability to query the LDAP directly and is not an ID set up in the Lawson Security Admin tool (although it might be).
  • Provider URL is the URL to your LDAP with the port number. I believe the standard port is 389. Your LDAP administrator should be able to provide this.
  • Enter the Context Factory exactly as you see it here.
  • See below for Search Base.
  • Unless you are familiar with LDAP searches, you’ll probably want to leave the Query string as I have it here. There are several good web resources for LDAP search if you’re not getting the results you want.

Keep in mind that the Lawson LDAP was designed by someone who either never wanted anyone to be able to use it or they didn’t actually know how LDAP works. I’m sure they had a reason for designing they way they did, and I’m pretty sure it’s a bad reason. But I digress…

The Search base follows the LDAP search standards. The full path can be determined by using a tool like JXPlorer. Note that the path is in reverse order from the way you descend the tree.
JXplorerPath

After you click next, you will need to add the field mappings. I would recommend that you do NOT allow SN to auto create the fields. The source name (highlighted) should correspond to the attribute name you see in JXPlorer. Once upon a time when dealing with an LDAP source, if the name didn’t match the source name it caused issues. We continue to do that and I can’t comment as to whether it has been fixed. In this case, we’re bringing in the user’s RMID (cn), Full Name and Email.
SNFieldDefinition

Here’s a screen shot of the Attributes in JXPlorer.
(For those who are wondering – yes the first four entries here correspond to the Query string in the first screen shot.)
JXplorerAttributes

If you want to be able to query the data set using a SQL tool, Crystal reports, etc you should check the box indicating that it’s a large infoset when you save it. This will create a table called INFOSET_NNNN in the LawsonSN database where NNNN is the id of the infoset. The fields in the table for this example will be COLUMN0 (id), COLUMN1 (name), COLUMN2 (email).

HTH

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

Barcodes in LBI

I’ve been getting hits lately for “barcodes in LBI”, so I thought I’d mention how we do barcodes in LBI. When I say LBI, I’m really just talking about Reporting Services and how to do barcodes in the context of Crystal Reports.

The most important thing to consider is what format your reports are going to be delivered in. There are two choices: PDF and any other file format (including crystal). When delivering the report in PDF format, it is not necessary for the report user to have any special setup or configurations. Any other format would probably require special setup. I say “probably” because I’m not certain. The only method I’m aware of to generate barcodes in Crystal is by using a barcode font. This means that for any delivery format besides PDF, the end user must have the barcode font installed on their local machine. However, there is a way to use CSS in Crystal reports, so it’s entirely possible that you may be able to generate a barcode that way, like I discuss here, but I’ve never tried it using it like that.

We currently use two different types of barcodes: Code39 and Code128. The Code39 barcode is from Azalea and the Code128 is from Dobson software.

Despite what the Azalea site says, Code39 does not require check digits. Many scanners also don’t require that you put the leading and trailing * characters (at least the three different brands of scanners that we have do not require them). This means that you can just use the Azalea font for the Crystal field and that’s it. I honestly can’t say whether we paid for Code39 or not as I’m not the one who implemented it’s use. For more on Code39, see the Wikipedia page.

Code128, on the other hand, is fairly complicated. It has three different flavors (A,B,C) and requires different characteristics depending on the type. Using this font in Crystal requires that you use a formula (or UFL) to properly generate the barcode. See the Wikipedia page for more info on Code128. The formula I use to generate correct Code128 is below.

(Note: this code generates a Code128B and uses the Code128bWin font from Dobson Software. This particular code happens to also be from one of the reports – W4 – that is part of our new hire onboarding application. It is generated from LBI and the barcode is read in ImageNow where it’s linked and filed automatically by Recognition Agent.)

Local StringVar strText := "Payroll_Employee" & "^" & {xml.ssn} & "^" {xml.EMPLOYEE_ID} & "^" & {xml.last_name} & "^" & {xml.first_name} & "^PR_Federal_W4";
//Replacing spaces in text string.  For some reason the barcode is unreadable when I leave them in
strText := Replace (strText, " ", "+");
Local NumberVar intRunTotal := 104;  //Start code for Bset of Code128
Local NumberVar intLength := Length(strText);
Local Numbervar i;
Local Numbervar intCodeValue;   //Code128 value of current character
Local Numbervar intCheckValue;  //Check value in Code128
Local NumberVar intCheckValueA;  //Hold ASCII value for Code128 value

for i:= 1 to intLength Do
(
    Local StringVar strChar := strText[i];
    //Get code128 value
    If ASC(strChar) = 128 Then
        intCodeValue := 0
    Else 
    (
        If ASC(strChar) >= 33 And ASC(strChar) = 1 And intCheckValue <= 94 Then
        	intCheckValueA := intCheckValue + 32
    	Else
        	intCheckValueA := intCheckValue + 50
    );

    //Add to running total for check char
    intRunTotal := intRunTotal + (intCodeValue * i)
);

//Get check value which is remainder of running total / 103
intCheckValue := intRunTotal Mod 103;

//Convert back to ASCII to display
If intCheckValue = 0 then
    intCheckValueA := 128
Else
(
    If intCheckValue >= 1 And intCheckValue <= 94 Then
        intCheckValueA := intCheckValue + 32
    Else
        intCheckValueA := intCheckValue + 50
);
strText := chr(154) & strText & chr(intCheckValueA) & chr(156)

HTH

LBI: Smart Notification queries

Have you ever worked in Smart Notes and just HATED life? This happens to me quite a bit, but mostly because the stupid User Picker is in no particular order. Another pain point is that I can’t see everything that another user is subscribed to. Here’s how I deal with it…

How to figure out what a user is subscribed to and lessen the pain of finding the user in User Picker.

--Get list of subscribed items for user 
SELECT ACS.ACCESSID as Userid, ENTRYSTRING as SN_Name, ENTRYDESC as Description
FROM ENPBATREE TREE 
INNER JOIN ENPENTRYACCESS ACS 
ON TREE.TREEENTRYID = ACS.TREEENTRYID 
WHERE ACS.ACCESSID IN ( 
  SELECT USERID 
  FROM ENPUSERMAP 
  WHERE USERNAME = 'jdoe') 

The result looks something like:

SN_Name Description
FIN_PeriodClosed Period Closed for company
FIN_Accounting Unit Changes Accounting Unit Changes
--Get list of groups for user 
SELECT U.USERNAME as GROUP
FROM ENPUSERMAP U 
INNER JOIN ENPGROUPMAP G 
ON U.USERID = G.GROUPID 
WHERE G.USERID IN ( 
  SELECT USERID 
  FROM ENPUSERMAP 
  WHERE USERNAME = 'jdoe') 

The result is something like this:
GROUP
Finance
SUPERLUSER
LBI AP

If your organization is anything like mine, with several hundred groups, at least you can use the IE “find” function to quickly find your group. With 50 per page, it isn’t so awful…

(Note: While I recognize that EFS/ERS has a “Manage User” screen, this is of limited help sometimes in SN. As of 9.0.3.1, Smart Notes synchronizes independently of EFS. Unfortunately, SN syncs the ENTIRE LDAP into the tables, regardless of what EFS has. We have EFS set to only sync a group called LBIUSER where we add all LBI users. We only have about 3000 users in this group so the sync is several minutes, but not unwieldy. Smart Notes synchronization on the other hand, takes over two hours on a good day. If we run it during busy periods it can bring down the system. Therefore we only sync once a week. Hence why I need to figure out SN groups separately.)

HTH