Professional > Data management scripting > Data Management Script (DMS) file > Sections in the DMS file > Event section > OnAfterJobEnd Event section
 
OnAfterJobEnd Event section
Used to define procedural code that is to be executed after the data source connections are closed. For example, code to create tables or call the ShellExecute function to open a report file.
Provided you have specified an input metadata source, the Job object is available in the OnAfterJobEnd Event section as an intrinsic variable called dmgrJob. You can use the Job object to access all of the other objects to which it gives access (with the exception of the Questions collection). For more information, see Using objects in the Event sections.
You can use the CreateObject function to access other objects in this section.
If you have UNICOM Intelligence Reporter and want to script tables in a DMS file, you typically include the code in the OnAfterJobEnd Event section. For more information, see Table scripting in a data management script.
Examples
1. Creating tables in Excel
This example uses ADO to access the case data in the output data source and advanced SQL queries to set up some tables in Excel. Note each table is created on a separate worksheet. For more information, see Advanced SQL Queries in the UNICOM Intelligence Developer Documentation Library.
Event(OnAfterJobEnd, "Create tables")
Dim ConnectionString, sqlQuery, adoConnection, adoRS, i
Dim xlApp, xlWorkbook, xlWorksheet

ConnectionString = "Provider=mrOleDB.Provider.2; Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\
MSExcelTables.mdd; MR Init Category Names=1"

Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Open(ConnectionString)

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = true
Set xlWorkbook = xlApp.Workbooks.Add()

' Table of age by gender

Set xlWorksheet = xlWorkbook.Worksheets[1]
sqlQuery = "SELECT groupby.col[0] AS Age, SUM(gender = {male}) AS Male, SUM(gender = {female}) AS Female FROM vdata GROUP BY
age ON age.DefinedCategories() WITH (BaseSummaryRow)"
Set adoRS = adoConnection.Execute(sqlQuery)
DisplayTable(adoRS, xlWorksheet)

' Table of interest by gender

If xlWorkbook.Worksheets.Count > 1 Then
Set xlWorksheet = xlWorkbook.Worksheets[2]
Else
Set xlWorksheet = xlWorkbook.Worksheets.Add(, xlWorkbook.Worksheets[1])
End If
sqlQuery = "SELECT groupby.col[0] AS Interest, SUM(gender = {male}) AS Male, SUM(gender = {female}) AS Female FROM vdata WHERE
interest IS NOT NULL GROUP BY interest ON interest.DefinedCategories() WITH (BaseSummaryRow)"
Set adoRS = adoConnection.Execute(sqlQuery)
DisplayTable(adoRS, xlWorksheet)

' Visits summary data

If xlWorkbook.Worksheets.Count > 2 Then
Set xlWorksheet = xlWorkbook.Worksheets[3]
Else
Set xlWorksheet = xlWorkbook.Worksheets.Add(, xlWorkbook.Worksheets[2])
End If
sqlQuery = "SELECT SUM(visits) AS TotalVisits, AVG(visits) AS AverageVisits, MIN(visits) AS MinimumVisits, MAX(visits) AS
MaximumVisits FROM vdata WHERE visits > 0"
Set adoRS = adoConnection.Execute(sqlQuery)
DisplayTable(adoRS, xlWorksheet)

xlWorkBook.SaveAs("C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\MSExcelTables.xls")

adoConnection.Close()

Sub DisplayTable(RecordSet, WorkSheet)
Dim xlStartRow, xlStartColumn, xlCol, xlRow, i

xlStartRow = 1
xlStartColumn = 1
xlCol = xlStartColumn
xlRow = xlStartRow
Dim xlCell

For i = 0 to (RecordSet.Fields.Count - 1)
Set xlCell = WorkSheet.Cells[xlRow][xlCol]
xlCell.FormulaR1C1 = RecordSet.Fields[i].Name
xlCol = xlCol + 1
Next

If Not RecordSet.EOF Then
xlRow = xlStartRow + 1
Do Until RecordSet.EOF
xlCol = xlStartColumn
For i = 0 to (RecordSet.Fields.Count - 1)
WorkSheet.Cells[xlRow][xlCol].FormulaR1C1 = RecordSet[RecordSet.Fields[i].Name]
xlCol = xlCol + 1
Next
xlRow = xlRow + 1
RecordSet.MoveNext()
Loop
End If
End Sub

End Event
Note This example is provided as a sample DMS file (called MSExcelTables.dms) that is installed with the UNICOM Intelligence Developer Documentation Library. For similar examples that create charts in Excel and top line tables in Word, see the MSExcelCharts.dms and MSWordToplines.dms sample Include files. For more information, see Sample DMS files that integrate with Microsoft Office.
Here is the table on the first Excel worksheet:
2. Opening a report file
This example calls the ShellExecute function to open the report file set up in the cleaning example in Example 2: A complete example. (This example is provided as a sample DMS file called Cleaning.dms. For more information, see Sample DMS files.)
If you add the following code to the sample, the report file will open at the end of the job.
Event(OnAfterJobEnd, "Open report file")
  ShellExecute("[INSTALL_FOLDER]\IBM\SPSS\DataCollection\7\DDL\
  Output\Cleaning.txt")
End Event
See also Using objects in the Event sections
See also
Event section