I have done a lot of reporting in Lawson using the Addins Query Wizard, but because I always have the need to make Lawson Reporting faster and automated, the Query Wizard shows its weaknesses. You simply can't automate Lawson Reporting through the Addins. But one thing that is a feature of the Addins that opens up a lot of Lawson automated reporting solutions is that you can get the URL that is generated by the Addins to use from any method that can access a web site. I will show you how to automate Lawson Reporting using DME from perl.
Sometimes, Lawson reporting calls for more powerful measures than excel can handle through the Office Add-ins, and well, I know that a scripting language like perl is more than capable through my years of prior experience doing data analysis. Sometimes, 65536 data records is a limitation of Excel that eliminates it as a solution option when trying to solve a Lawson reporting problem. Bring on perl!
So my goal was to figure out how to use perl to query Lawson data through the DME that is the same method used by the Office Addins. All the Office Add-ins really do is generate a web URL that passes arguments to the Lawson Reporting DME interface. This is important in our environment where our Lawson system is hosted, so we don't have the database connections or server-side programming that in-house Lawson setups would have. So this means that I need to use HTTP access from perl to navigate through the HTTP basic authentication to query using the DME. This is the only option that we have that would use Lawson security, as our ODBC connection is an all-access connection. Any Lawson Reporting solution should obey security access rules if you ever plan to distribute it.
So let me show you how to use perl to whip the Lawson data into shape.
Step 1)
Find a DME query. Easy. Just use the Lawson Office Add-ins to develop the query, run the query once, click on the little magnifying glass at the top of the Office Add-ins tool to get the full DME call. Done!
Step 2)
Very simple perl code. For this example, pulling Company, Employee, first and last name from the EMPLOYEE table in our PROD environment via the DME query.
#!/usr/bin/perl
use strict;
use LWP::UserAgent;
Next goes your DME query that you pulled from the Office add-ins.
# copy and paste the URL directly from the Office Add-ins Query Wizard
# IMPORTANT: if your server has a port, you NEED to add it in, otherwise the auth won't work
my $simpleURL = "http://my.lawson.server.com:30077/cgi-lawson/dme.exe?PROD=PROD&
FILE=EMPLOYEE&INDEX=EMPSET1&KEY=1&
FIELD=COMPANY;EMPLOYEE;FIRST%2DNAME;LAST%2DNAME&MAX=600&
OUT=CSV&DELIM=%09&NOHEADER=TRUE";
Next, initialize the perl LWP::UserAgent module to do the HTTP request. If you don't know your authentication realm, copy and paste the DME query into a blank web browser window, hit enter, and see what the prompt says. Or just run this script, and an error will pop up that shows you, more details on that down below.
my $ua = LWP::UserAgent->new;
# set the basic HTTP auth credentials
# arguments are server:port, authentication realm, username => password
$ua->credentials('my.lawson.server.com:30077','Lawson Restricted Access','username'=>'password');
my $response = $ua->get($simpleURL);
Now we will verify the response, and print out an error that will contain the reason for failure.
if($response->is_success) {
print $response->content;
}
else {
# this will print out any errors
# IMPORTANT: This also will tell you what your auth realm is if you don't know it
die "Error: ", $response->header('WWW-Authenticate') ||
'Error accessing', "\n", $response->status_line, "\n at $url\n Aborting";
}
Here is the full code listing for you copy and pasters.
#!/usr/bin/perl
use strict;
use LWP::UserAgent;
# copy and paste the URL directly from the Office Add-ins Query Wizard
# IMPORTANT: if your server has a port, you NEED to add it in, otherwise the auth won't work
my $simpleURL = "http://my.lawson.server.com:30077/cgi-lawson/dme.exe?PROD=PROD&
FILE=EMPLOYEE&INDEX=EMPSET1&KEY=1&
FIELD=COMPANY;EMPLOYEE;FIRST%2DNAME;LAST%2DNAME&MAX=600&
OUT=CSV&DELIM=%09&NOHEADER=TRUE";
my $ua = LWP::UserAgent->new;
# set the basic HTTP auth credentials
# arguments are server:port, authentication realm, username => password
$ua->credentials('my.lawson.server.com:30077','Lawson Restricted Access','username'=>'password');
my $response = $ua->get($simpleURL);
if($response->is_success) {
print $response->content;
}
else {
# this will print out any errors
# IMPORTANT: This also will tell you what your auth realm is if you don't know it
die "Error: ", $response->header('WWW-Authenticate') ||
'Error accessing', "\n", $response->status_line, "\n at $url\n Aborting";
}
