Hello Jackson,

How do I combine data from two different data sources into one Data Object for Reporting ?

Connect each data object to the appropriate data source (Notes and ODBC. Now create an unconnected data object (Data3) and add local columns with columns (and data types) to match the columns in your two connected data objects (primary key from data1 needs to map to foreign key in data2). Please review the example…

In the following example, the AppWindow Init script executes queries for Data1 and Data2, and then combines the data in Data3. You can then use Data3 as the source for your report.

Dim row&, nrow&, status%, error&, col1$, col2$, col3$

' Run queries with the connected data objects.

Call Data1.ExecuteQuery()

Call Data2.ExecuteQuery()

' Put data from Data1 into Data3 (you can change the number of columns).

For row&=1 To Data1.ResultRowCount

status%=Data1.RowGet(row&, error&, col1$, col2$, col3$)

nrow&=Data3.RowNew()

status%=Data3.CellSetData(nrow&, 1, col1$)

status%=Data3.CellSetData(nrow&, 2, col2$)

status%=Data3.CellSetData(nrow&, 3, col3$)

status%=Data3.ExecuteUpdate()

Next row&

' Put data from Data2 into Data3 (you can change the number of columns).

For row&=1 To Data2.ResultRowCount

status%=Data2.RowGet(row&, error&, col1$, col2$, col3$)

nrow&=Data3.RowNew()

status%=Data3.CellSetData(nrow&, 1, col1$)

status%=Data3.CellSetData(nrow&, 2, col2$)

status%=Data3.CellSetData(nrow&, 3, col3$)

status%=Data3.ExecuteUpdate()

Next row&

In other cases, the connected data objects contain related information that you want to combine by using key fields to perform joins. For information about joining data from two data sources in an unconnected data object, see Chapter 13, "Data Integration," in the Revelation ViP 2.0 Application Developer's Guide.

PS. make sure you have the DB2 ODBC driver!

Hope this helps,

VIPDX Tech Support

View this thread on the forum...