August 2003

The LawsonGuru Letter is a free periodic newsletter providing provocative commentary on issues important to the Lawson Software community.

The LawsonGuru Letter is published by-and is solely the opinion of-John Henley of Decision Analytics.  Visit Decision Analytics at http://www.danalytics.com.  For subscription information, see the bottom of this message.
 

The LawsonGuru Letter is not affiliated with Lawson Software.

---

1. Comments Attachments in Lawson v8

Attachments in Lawson have been dramatically overhauled in v8. In particular, attachments are no longer restricted to each particular application; attachments are now table-specific. Either URLs or comments can be created as attachments to a Lawson record. One other major enhancement is that comment attachments are no longer stored as individual lines in 40-byte records. They are now stored as "chunks" of free-form data, so that long blocks of text can be easily entered. My clients frequently ask "how are comments stored?", "how do I report from them?", and "just what are those Lawson L_* tables?"
 

To download an enhanced copy of this article, including sample code for comment retrieval, go to http://www.danalytics.com/solutions/ (web site registration required), and select the "Comment Attachments in Lawson v8" article).

Defining Comment Attachments

Comments are defined per Lawson base table, e.g. PURCHORDER for Purchase Orders. Using the Lawson dbdef utility, the Attachments option is set to "Yes".

After setting the Attachments option, the Lawson blddbdict and dbreorg utilities need to be run in order to implement the database changes. This adds two tables for each base table that uses attachments. The naming convention is L_Hxxx and L_Dxxx for each table, where xxx is the Lawson prefix for the table. In this example, the prefix is PCR, so L_HPCR and L_DPCR are created. The first 512 (roughly) characters of a comment are stored in L_Hxxx records; the remaining characters in a comment are stored in one or more L_Dxxx records.

Defining Purchase Order Attachments (PO.or)

Attachments are defined as an object window ("OBJWIN") in a system code's object rules file. For example, for PO, they are defined in PO.or. Each attachment is given a "type" field to keep it unique. The Type field can be used (along with a user-defined name for each comment when it is entered) to retrieve the comment:

 

**********************************************************************

*          Attachments for Purchase Orders and Lines

*********************************************************************

DEFINE OBJWIN     "Comments to Print on Headers on Internal Documents"

       FILENAME   PURCHORDER

       COMMENT    TYPE=I

       WINFLDS    COMPANY:       "       Company:"

                  PO-NUMBER:         " PO Number:"

                  PO-RELEASE:    "    PO Release:"

                  PO-CODE:       "       PO Code:

Enabling Attachments on a Lawson Form

Add one or both of the following lines to the $FORM section of a form on which you want to enable attachments:
 

$COMMENTKN KN1, KN2, Knn

$URLKN KN1, KN2, KNnn

The KNnn codes refer, using Lawson's key numbers, to the form fields on which the attachments will be enabled. In this example, DN refers to the PO Number. So, when the cursor is on the PO Number field, the attachment button will be activated on the PO20 form:
 

*******************************************************************************

*                            SCREEN   PO20  (1)

*******************************************************************************

$FORM      PO201    S

   .

   .

   .

$COMMENTKN DN,D1B

$URLKN     DN,D1B

   .

   .

   .

$TRANS     PO201    S

  2     TC                    AL    06          SB

  2     FC                    A     01          SF

  2     PT-XMIT-COUNT         N     01          SB

  2     PCR-COMPANY           N     04          SK    01  C

  2    *PCY-PROCURE-GROUP     A     04          SB    D5W

  2     PCR-BUYER-CODE        A     03          SB    DB

  2    *BUY-BUYER-CODE        A     03          SB    DB0

  2     PCR-PO-DATE           F     08          SB    DY

  2     PCR-PO-NUMBER         A     14          SL    DN

How Lawson Stores Comments

The base database record (i.e. PURCHORDER) contains an attachment "subset flag"  (L_ATxxx_SW_SS), which is used to indicate that a particular record has attachments.  For each base record having attachments, the L_ATxxx_SS_SW (L_ATPCR_SS_SW in this example) is set to "Y".  A pointer field (L_INDEX) points to the attachment records. The value of L_Index is a Lawson-generated value, unique to each base record in the given table. 

The attachments are stored in two tables for each base table that uses attachments.  The naming convention is L_Hxxx (header) and L_Dxxx (details) for each table, where xxx is the Lawson prefix for the table. In this example, the prefix is PCR, so L_HPCR and L_DPCR are created.

In the comment tables, the first 512 (approximately, allowing for some header text) bytes are stored in the "header" L_Hxxx (L_HPCR in this example), where xxx is the file prefix (e.g. PCR).  The remaining bytes (up to 32,000 according to the Lawson-published limit) are stored in "detail" L_Dxxx (L_DPCR in this example) records.  Each L_DPCR record stores up to 1024 characters.  Multiple L_Dxxx records are used up to the Lawson-published 32,000 byte limit.  I tested on 8.0.2 apps against 7.3.3 environment, and found that it broke at about 20,000 characters; I assume this has been fixed in 8.0.2 environment.

Lawson Environment Utilities

You can use the Lawson lstprodatch utility to see which tables have attachments enabled; use the -C option to display attachment records counts.

Interesting, there is not an environment utility to load or dump comments.  There are some module-specific conversion/interface tables if they fit your needs (i.e. APCATTACH).  You can use rngdbdump and importdb to dump and load the comment tables, but they don't maintain integrity and/or update the base records.

If you're serious about using comments, and you need to load them for a lot of tables, you can do it indirectly via either a COBOL program or database/SQL. In fact, enough clients have inquired about this that I may write a general-purpose "loader" tool...let me know if you're interested.

Extracting and Reporting Comments

There is no particularly-useful utility within the Lawson toolset to extract and/or report comments. There are some task-specific comments, such as "Comments to print on Purchase Orders". These are inherently handled by the individual applications. However, there is no generic comment printing or extraction utility in Lawson.

In particular, I needed to devise a solution for a client who needed to print comments on their Crystal reports. One of my goals was to create something that was generic and fairly reusable; in other words, I wanted to be able to plug it into various reporting tools, and use it for any type of comments.

Reporting Comments with Lawson OLEDB Provider/Enterprise Reporting

My first option was to use the Lawson Query Builder/OLEDB Provider, part of the Lawson Enterprise Reporting toolset. One of the features of the Lawson OLEDB Provider is that you can retrieve comment attachments:

The problem with this approach is that, in order to include it in a Crystal Report, you have to hard-code the Lawson query string. So, I'd have to create a separate query for each and every PO, not exactly a usable solution.

Visual Basic ActiveX/COM DLL for Crystal

Since the Lawson Query Builder provides the means to retrieve the comments, I decided to create a Visual Basic ActiveX/COM DLL that could be called as a custom user-defined function (UFL) from Crystal. Since Crystal would call this function for each purchase order being reported, the Lawson query string could be built "on-the-fly" in the VB code, and I could still use the Lawson Query Builder to retrieve the query. I could still keep it generic, since I wrote the UFL to accept the base table name and up to 10 key fields to use for retrieving the comment. Ultimately, this solution turned out to work fine, except for one major limitation.

Only certain fundamental data types can be returned from a UFL to Crystal, and the string data type is limited to 255 characters, and while useful perhaps for some attachments, was not acceptable for purchase order comments.

Using an Oracle Package to retrieve Purchase Order Comments

The final approach, which ultimately has proved very useful, was to create an Oracle procedure that returned each comment as a set of records. The Oracle procedure is then queried as a data source on a Crystal subreport for each purchase order. This approach does have a few drawbacks, of course.
You have to create a separate Oracle package for each table from which you want to retrieve the comments. And, it's specific to Oracle.

2. Rosetta, the Crystal Killer?

I recently got a chance to see a pre-release version of Microsoft's SQL Server 2000 Reporting Services, That's a pretty long name, which is probably why it was formerly known as "Rosetta". I call it the "Crystal Killer".

Unifying Disparate Data Sources

SQL Server Reporting Services will augment the existing analytical (OLAP) capabilities provided in SQL Server 2000 Analysis Services. Reporting Services can report on data from any OLEDB, or ODBC Data Source, be it SQL Server, Oracle, DB2, etc., and can include data from multiple data sources on the same reports. Using Microsoft Visual Studio .NET, developers can connect to custom data sources. I'll bet we see a Lawson-delivered native .NET data source--built on the Lawson OLE DB provider--which embraces Lawson security.

Report Authoring Features

If you've ever used Microsoft Access' report designer or wizards, you'll feel right at home with the authoring tools in SQL Server Reporting Services. In addition to the layout tools, you use a tool very similar to the SQL Query Analyzer to develop your queries. You get all the features you'd expect, such as summary/total fields, formatting, subreports, etc.

A report developer will need Microsoft's .NET Framework 1.1, Visual Studio .NET 2003, and a SQL Server connection/license on their desktop in order to create reports. By integrating SQL Server Reporting Services into .NET, you have the entire .NET toolset available when creating reports.

Report Delivery

SQL Server Reporting Services supports both on-demand (pull) and event-based (push) delivery of reports. You can create traditional page-oriented reports as well as highly-interactive reports with drill-down. Reports can be delivered to a portal, e-mailed or accessed via a Web-based application. Reports can be exported to all the usual standards, including HTML, Adobe PDF, TIFF, XLS, CSV, and XML.
Navigation and search features help users locate and run the reports they need. Personalized subscriptions let them select the rendering formats and schedules they prefer. I also expect that Reporting Services will be combined in some way with SQL Server Notification Services, to provide timely delivery of targeted information. So, you could build your own notifications, similar in functionality to Lawson's Smart Notification.
One of the "Crystal Killer" features that I saw and liked the most is that you can provide your users with dynamic data-driven parameters, something you can't do without custom programming in Crystal Enterprise.

Report Management

SQL Server Reporting Services also includes comprehensive functionality for managing reports in a wide variety of environments. Report Manager is a Web-based tool for managing reports as a Web service. Managed reports can be executed either on demand or on a specified schedule, and are cached for consistency and performance. Security can be tailored to meet a variety of needs. Users may be granted access to run reports on demand or tailor individual report subscriptions. Administrators can delegate content management functions to specific individuals for different reports.
The server requirements are standard Microsoft: Windows Server 2000/2003 and the IIS Web Server. SQL Server 2000 is required for the reporting repository, which can be deployed either on the same server or a different one.

Visual Studio Integration

In addition to developing and delivering your reports, you can use SQL Server Reporting Services to integrate reports into your custom applications as well. Because Reporting Services are written as an XML-based Web service, IT organizations can easily embed reports in any application and make them available to users over any Web interface. The Web model allows organizations to make reports available to customers and partners over an extranet, isolating these audiences from the complexity of the underlying data sources while providing personalized, interactive access to data that's critical to their own decisions.

Crystal Killer

Microsoft is working with other reporting software vendors, such as Crystal, Cognos, and Brio to standardize on a common ".RDL" XML-based report definition file. This will allow each vendor's products to interoperate across your various reports. So, if you have Crystal ".rpt" reports, you could open them in Crystal, save them as ".rdl" files, and deploy them with SQL Server 2000 Reporting Services. Or vice versa.

When it's released (expected to be towards the end of the year), rumor has it that Reporting Services will be a downloadable add-on to SQL Server 2000. Once you understand the functionality it provides, this alone makes it an obvious choice if you're running Lawson on a Windows platform. In addition, if you cringe at the licensing cost for Crystal Enterprise named users (which are required if you want to properly implement report security), you should evaluate Reporting Services.

Is it a "Crystal Killer"? Well, time will certainly tell. With the acquisition of Crystal Decisions by Business Objects, and Brio by Hyperion, it looks like we're in for some significant changes in the reporting software business.
 

3. Worthwhile Reading

5. Lawson Tips & Tricks
Share your tips. Send them to mailto:letter-tips@lawsonguru.com.

When Lawson security is turned on, an empty semaphore file is written to the directory above $LAWDIR (e.g., /opt/lawson/).  This file is called lawson.sec in Environment v7.3, and apps.sec in v8.  If security cannot be turned on or off (via laua or the lawsec command), and you do not get an error message, check the permissions on the directory above $LAWDIR.  Even though a user is designated as a Lawson administrator in laua, they must have write access to that directory in order for the semaphore file to be created.