Saturday, August 01, 2009

SharePoint, Excel, and Backend Systems (like JD Edwards on AS400 / iSeries)

With BDC, it maybe easier to combine backend and SharePoint data than doing so in WSS.  Nevertheless it is still a pretty hard task to accomplish, especially when working in IBM environment or more generally, in non-SQL Server based systems.
SharePoint Designer (SPD) is as difficult when it comes to Unix or IBM based data sources.

I use Excel to do the magic and will demonstrate how to build an Excel model that combines iSeries data managed in JD Edwards with complementary data entered in a SharePoint list.

Assume the following scenario: production quantities are entered in JDE by machine and job. Weekly Goals are entered in a SharePoint list. The data matching is done by machine to get the weekly quantity aggregation .

One way to achieve this is:
  1. Export the appropriate SharePoint list's view to a sheet (1).
    This is a standard SharePoint feature available for every list.
  2. Import AS400 data into another sheet (2).
    It is possible to use Microsoft query to define the SQL statement as done in Access but when I filter by a specific year entered in Excel, I use VBA.
  3. Create a formula just after the last list's column in sheet 1 to lookup the quantity from sheet 2.
    There is a parameter letting you define that formulas will be spread out when the SharePoint linked list is refreshed, over the new set of rows returned from SharePoint.
  4. Name the extended list and create a pivot table pointing to that name.
    The range of this name changes dynamically with the list size upon each refresh.
Get data from SharePoint
This is the list entered in SharePoint:

Export to Spreadsheet (as explained in another post):

After choosing Open you click on Properties:

Check the 'Fill down formulas:'

Excel will bring the List View data into the selected sheet.
The VBA code to bring the iSeries (AS400) data:
You first need to ensure that you have the appropriate ODBC driver for the environment where your data resides. If it is missing, you will need to get it from the Internet and install.
In Windows XP:
Start / Control Panel / Performance and maintenance / Administrative Tools / Data Sources (ODBC)

Adjust the following VBA code:
Sub GetJDEData()

' Clean 5000 rows in target area to make ready for a new retrieve
' Leave title in first row
Sheets("Data-Prod").Select
Range("A2:W5000").Select
Selection.ClearContents
Range("A1").Select

' Declare variables
Dim MyDatabase As ADODB.Connection
Dim MyCommand As ADODB.Command
Dim MyRecordset As ADODB.Recordset
Dim Column As Long

' Open database connection
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=IBMDA400.DataSource.1;Data Source=[ReplaceWithAS400ServerURLorIPaddress];Persist Security Info=True;User ID=[ReplaceWithAS400User];Password=[ReplaceWithPassword];Initial Catalog=[e.g.: S1030182];;Force Translate=0;"
' Build a custom query using command object
' and filter only the selected year in the function: RequestedYear
' This function takes its value from cell C2 in Admin sheet (see at the bottom).
Set MyRecordset = New ADODB.Recordset
Set MyCommand = New ADODB.Command

With MyCommand
Set .ActiveConnection = MyDatabase
.CommandType = adCmdText
.CommandText = "SELECT * "
.CommandText = .CommandText & " From NECPRDDTA.F553104"
.CommandText = .CommandText & " WHERE (wYEAR = " & RequestedYear() & ") “
.CommandText = .CommandText & " ORDER BY wWeek DESC, wMachine"

End With

MyRecordset.Open MyCommand, , adOpenDynamic, adLockReadOnly

' Test for no records
If MyRecordset.BOF And MyRecordset.EOF Then
MsgBox "No records found"

Else

If MyRecordset.RecordCount > 0 Then
Sheets("Data-Prod").[A2].CopyFromRecordset MyRecordset
MyRecordset.Close
MyDatabase.Close
MsgBox "Production Data have been Refreshed "

Sheets("Admin").Select

End If
End If

End Sub

Function RequestedYear()
RequestedYear = Range("Admin!C2").Value
End Function

After running the code, froma button for example, you have the other sheet with refreshed data.


Create the Pivot Table

I assume that you know how to apply formulas and use lookup as well as name a range and create a pivot table.


The end result may look like this:

No comments:

Post a Comment

Enter your comment here