Shiva Blog Center

Thursday, July 7, 2011

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.

Wednesday, June 29, 2011

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.

Tuesday, June 7, 2011

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.

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:

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.

Wednesday, November 26, 2008

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

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.