Shiva Blog Center

Creating Pivot Table in Qlikview to display Account Receivable Outstanding Amount from Lawson M3 data

2011-07-07T15:46:00.001-07:00

In my last blog, I showed how to build a straight table using the Lawson M3 AR data. Continuing with the same document, I will show how to create Pivot Table showing the outstanding amount in 0-30-60 days breakup.

In the worksheet right click on the mouse button to bring the popup context menu, and click on New Sheet Object –> Chart to bring the Chart object designer as shown below.

Type Pivot Table Outstanding Amount in the Window Title Text Box, and select the Pivot table in the chart type (last icon on First row) .

Click Next button to bring the next window Dimensions. Here we can select the Dimension fields to be displayed in the pivot table. Highlight the fields CustomerNumber and CustomerName in the Available Fields/Group box, and click Add. The highlighted fields will be moved into the Used Dimension box.

Click the next button to bring the Expression Window and will display a popup window Edit Expression. Type Sum(If(DueDays <= 0 ,OutStandingAmount,0)) in the box and click OK button .

Add additional 3 more Expression as shown below.

Add the Labels to all the four expressions

Click the next buttons and we will accept defaults in the Sort, Presentation and Visual Cues,Style windows.

On the number windows, select Money for Number Format Setting for all the expression as shown below.

Click finish to display the Pivot table.

Create Straight Table in Qlikview to display Account Receivable Outstanding Amount

2011-06-29T16:22:00.001-07:00

In my last blog posting http://shivakamathblog.blogspot.com/2011/06/open-outstanding-account-receivable-ar.html , I showed in Qlikview, how to load data from the Lawson M3(Movex) system using the sales ledger table FSLEDG and customer master table OCUSMA. Once the data is load and saved in the Qlikview document, we can use the data to display in different Qlikview objects like table,list or charts. We will create a simple Straight table object to display the data.

In the worksheet right click on the mouse button to bring the popup context menu, and click on New Sheet Object –> Chart to bring the Chart object designer as shown below.

Type Outstanding Amount in the Window Title Text Box, and select the straight table in the chart type (last icon on second row) .

Click Next button to bring the next window Dimensions. Here we can select the Dimension fields to be displayed in the straight table. Highlight the required fields in the Available Fields/Group box, and click Add. The highlighted fields will be moved into the Used Dimension box.

Click the next button to bring the Expression Window and will display a popup window Edit Expression. Type Sum(OutStandingAmount) in the box and click OK button . Here we are adding the out standing amount to be displayed.

Type Outstanding Amount in the Label box.

Click the next buttons and we will accept defaults in the Sort, Presentation and Visual Cues,Style windows.

On the number windows, select Money for Number Format Setting as shown below.

Click on the Finish button to complete and the straight table will be displayed with the dimension fields and the calculated amount fields.

Open outstanding Account Receivable (AR) from Lawson M3 (Movex) system in Qlikview

2011-06-27T16:05:00.000-07:00

Today, I will show how to load the data in Qlikview from the Lawson M3 (Movex system). Please refer to my previous posting for the SQL statement http://shivakamathblog.blogspot.com/2008/11/open-outstanding-ar-from-lawson-m3.html.

Start the Qlikview application and open the script editor. Add the connection to the AS/400 or iseries ( on any other data source for M3). Check my previous posting for more details http://shivakamathblog.blogspot.com/2011/06/loading-data-from-as400iseriesi5os-into.html for more details.

Enter the following statements in the script window after the connection statement:

OutStanding:
Load     ESCONO as Company,
        ESDIVI as Division,
        ESCUNO as CustomerNumber,
        ESINYR as InvoiceYear,
        ESCINO as InvoiceNumber,
        Date(Date#(ESDUDT,'YYYYMMDD')) as DueDate,
        Today() - Date(Date#(ESDUDT,'YYYYMMDD')) as DueDays,
        Date(Date#(ESIVDT,'YYYYMMDD')) as InvoiceDate,
        ESCUAM as OutStandingAmount,
        OKCUNM as CustomerName,
        OKCRL2 as CreditLimit2,
        OKCRL3 as CreditLimit3 ;
SQL
with CTE_AR as
(
SELECT ESCONO,ESDIVI, ESCUNO, ESINYR,ESCINO,
MAX(ESDUDT) AS ESDUDT, MAX(ESIVDT) AS ESIVDT, SUM (ESCUAM) AS ESCUAM
FROM M3LIBRARY.FSLEDG
GROUP BY ESCONO,ESDIVI, ESCUNO, ESINYR, ESCINO HAVING SUM (ESCUAM) <> 0
)
select ESCONO,ESDIVI, ESCUNO, ESINYR,ESCINO, ESDUDT,ESIVDT,ESCUAM,
OKCUNM,OKCRL2,OKCRL3
from CTE_AR
LEFT JOIN M3LIBRARYNAME.OCUSMA ON ESCONO=OKCONO AND ESCUNO=OKCUNO ;

The statements is made up of two part:

One will read the data from the M3 data source

SQL
with CTE_AR as
(
SELECT ESCONO,ESDIVI, ESCUNO, ESINYR,ESCINO,
MAX(ESDUDT) AS ESDUDT, MAX(ESIVDT) AS ESIVDT, SUM (ESCUAM) AS ESCUAM
FROM M3LIBRARYNAME.FSLEDG
GROUP BY ESCONO,ESDIVI, ESCUNO, ESINYR, ESCINO HAVING SUM (ESCUAM) <> 0
)
select ESCONO,ESDIVI, ESCUNO, ESINYR,ESCINO, ESDUDT,ESIVDT,ESCUAM,
OKCUNM,OKCRL2,OKCRL3
from CTE_AR
LEFT JOIN M3LIBRARYNAME.OCUSMA ON ESCONO=OKCONO AND ESCUNO=OKCUNO ;

The second part will load data from succeeding input table.

Here we are performing following additional steps:

Rename all the fields
Convert the M3 date fields into Qlikview dates type
Add calculated field DueDays.

When the script is reload, it will create a table Outstanding that can be used with Qlikview objects.

Once the data is reloaded, save the Qlikview file. In the next blog I will discuss on creating table charts and other objects using the loaded data.

Loading data from AS/400(iseries,i5/OS) into Qlikview

2011-06-07T18:56:00.001-07:00

Qlikview is an in-memory management BI technology allows to connect and combine data from different data sources. You can download and install a free personal edition from the website http://www.qlikview.com/. I will be posting about my experience in working with Qlikview.

Qlikview can connect to AS/400 (iSeries,i5/OS) and reload data using the SQL scripts. You will need to install client access for iSeries i5/OS from IBM and refer to my previous post http://shivakamathblog.blogspot.com/2008/11/configure-odbc-to-access-iseriesas400.html for more details.

Once the Qlikview and iSeries access is installed we can connect to AS/400 using the OLE DB provider. Start the Qlikview application and open a new document from the File menu. Open the scripting window using the option Edit Script… on the File Menu. At the bottom of the scripting window, select the Data tab as shown below

From the Database, select the OLE DB from the dropdown list and click on the connect button. This will open a Data link Properties dialog box:

Select the IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider, and click on the connection tab:

On the Data Source textbox, enter the Server name or the IP address of the AS/400 machine. Enter the AS/400 sign on user name and password and the Catalog name of the AS/400. Please contact your AS/400 administrator for any help. Make sure “Allow saving password” is checked and “Blank password” is unchecked.
Click OK button and it will insert the connection string (starting with OLEDB Connect …. ) in the scripting window:

After the connection string, type the SQL statement as required. Click the Save option from the Toolbar and then Click OK button below to close the scripting window.
From the File menu, select Reload option to run the script. After reloading is finished, the data is available in Qlikview document to create objects like Charts or Lists.

In the next posting, I will write on creating the charts and other objects.

Cross Reference System Table in Movex/M3

2008-12-18T07:46:00.003-08:00

One of the most important tables in Movex/M3 is CSYTAB, the cross reference system table. Lot of the cross reference programs (CRSxxx) stores data in CSYTAB.

The primary key for CSYTAB is the following fields:
CTCONO Company
CTDIVI Division
CTSTCO Constant Value
CTSTKY Key Value
CTLNCD Language

The following SQL statement will show all the records entered in Movex/M3 program or function CRS040 (Item Type. Open):

Select CTSTCO, CTSTKY, CTTX15, CTTX40 from CSYTAB where
CTSTCO = 'ITTY'

CTSTCO CTSTKY CTTX15 CTTX40
ITTY 010 Raw materials Raw materials
ITTY 030 Finished Goods Finished Goods
ITTY 040 Packaging Packaging
ITTY 070 Subcontract Subcontract

The CTSTCO indicated the Constant Value referencing to a group of values. The value ‘ITTY’ in CTSTCO indicates for item type.

The field CTSTKY indicates a key value, which is a value included in the group of Constant Value CTSTCO.

The SQL statement “Select distinct CTSTCO from CSYTAB” will list all the Constant value used in your system.

Some Cross functional programs will use the field CTDIVI, allowing different values to be entered at Division level.

Cross function programs like CRS070 (Delivery method. Open) will use CTLNCD to allow entering same key value for different Language. The SQL statement below includes the CTLNCD:

Select CTSTCO, CTSTKY, CTLNCD, CTTX15, CTTX40 from CSYTAB where CTSTCO = 'MODL'
CTSTCO CTSTKY CTLNCD CTTX15 CTTX40
MODL A00 GB LTL shipment LTL shipment
MODL ONS GB On-Site On-Site

WPF - Working with XML file using Linq to XML

2008-12-01T16:34:00.000-08:00

Building WPF ListView application to read XML file using Linq to XML

Recently I published my first article on codeproject.com and the link access article is http://www.codeproject.com/KB/WPF/WPFandXML.aspx

The article discuss on building a user interface using Microsoft 's Windows Presentation Foundation (WPF) using customer master table as datasource.

Let me know your comments and feedback on the article.

Configure ODBC to access iSeries(AS/400) system

2008-11-26T14:38:00.000-08:00

IBM provides software like WRKQRY, STRSQL on the iSeries (AS/400) system to access and query the database DB2 UDB and needs green screen terminal emulation to work with it Another way to access the DB2 database is to use iSeries navigator that provides option to run SQL statements.

The iSeries database can also be accessed using the ODBC and OLE DB drivers that can be installed on the PC and configured in the ODBC data source administration. Once the drivers are installed and configured you can use SQL to access and query the DB2 database using Excel or Access or any third party database management tool like WinSQL (http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp).

The OLE DB drivers allows developer to write application using OLE DB connection in VB or VB.NET/C# using Microsoft Visual studio etc.

To install ODBC or OLE DB driver, you will require the installation media iSeries Access for Window from IBM and is normally provided as a part of standard softwares when iSeries system are purchased from IBM. If you do not have the installation media contact the vendor or IBM for more information and cost involved in purchasing it.

During the installation process of iSeries Access for Window software, select the custom setup that allows to select the required program features you want to install on the PC. Below is the screenshot when performing custom setup and make sure you have selected the ODBC and OLE DB provider as required for installation.

Once the installation is complete, the drivers will be available for use for your tools or development.

To configure ODBC to access perform the following step (I am using Window XP):
Click on Start->Control Panel->Administrative Tools that will bring the Window to setup a Data Source on your PC.

Open the Data Source (ODBC) by double click on the shortcut to bring up the ODBC Data Source Administrator.

Select the System DSN and Click on the Add button to create a new Data source.

Select Client Access ODBC Driver (32-bit) and click on the Finish button to continue.

Enter a name in the Data source name field and the IP address in the System field.

Click on the Server tab and enter the SQL default library.

Click OK button to complete the process.

For additional references to the other options and tabs refer the documentation or user guide of the iSeries Access to Windows.

Once the setup is complete, the ODBC data source is available to access from other tools.

In next time post I will write on tools to access the ODBC data source.

Open Customer Order and Delivery Information in Lawson M3 (Movex)

2008-11-24T15:15:00.000-08:00

The Customer order Management (COM) and Warehouse (SCE) users requests a quick data extract for Open Customer orders and delivery information from M3 (Movex).

The delivery data are stored the delivery header table MHDISH and delivery line table MHDISL. The customer order lines are stored in the table OOLINE and Customer master table is OCUSMA.

The SQL below will list all the open customer order and delivery information

SELECT OBWHLO as Warehouse,
OQCONN as ShipmentNumber,
OQDLIX as DeliveryNumber,
OBORNO as OrderNumber ,
OBPONR as Line ,
OBCUNO as CustomerNo,
OKCUNM as CusotmerName,
OBADID as AddressID,
OBCUOR as CustomerPO,
OQTEDF as DeliveryTerm,
OQMODF as DeliveryMethod,
OQPGRS as DeliveryStatus,
OQDSDT as DepartureDate,
OQDSHM as DepartureTime,
OBITNO as ItemNumber,
OBITDS as ItemDescription,
URTRQT as DeliveryTransactionQty,
URGRWE as GrossWeight,
OBORQT as OrderQty,
OBALQT as AllocatedQty,
OBPLQT as PickQty,
OBORST as Status
FROM MHDISH
LEFT JOIN MHDISL ON OQCONO=URCONO AND OQDLIX=URDLIX
JOIN OOLINE ON URCONO=OBCONO AND URRIDN=OBORNO AND URRIDL = OBPONR*100
JOIN OCUSMA ON OBCONO=OKCONO AND OBCUNO=OKCUNO
WHERE OQCONO=1 AND OQRORC='3' AND
OQWHLO = 'WH1' AND OQPGRS < '90'

The field OQRORC (Reference order category) = 3 will list only customer orders and the status field OQPGRS < ‘90’ will list deliveries that are not fully reported.

Substitute Company and Warehouse value with the required value for your company and warehouse.

The SQL is applicable to Movex version 12 or later. Depending up on the version the field listed in the SQL may require be adding or removing from the SQL Statement.

Open outstanding AR from Lawson M3 (Movex) ERP system

2008-11-20T17:52:00.000-08:00

One of the repeated request received from the finance users (specially AR manager or Supervisor) a list of outstanding AR Invoices.

The sql statement will query the M3 (Movex) table FSLEDG to provide AR invoices that are not paid:

SELECT ESDIVI, ESCUNO, ESINYR,ESCINO, ESDUDT, MAX(ESDUDT) AS DUEDATE, MAX(ESIVDT) AS INVOICEDATE, SUM (ESCUAM) AS OUTAMT
FROM FSLEDG WHERE ESCONO=1 AND ESDIVI = '100' GROUP BY ESDIVI, ESCUNO, ESINYR, ESCINO, ESDUDT HAVING SUM (ESCUAM) <> 0

Substitute the ESCONO and ESDIVI value with the required Company number and Division.