Many times your customers may want reports that combine data from different sources.  For instance, maybe you need to get your customer data from ERP data and that data comes from a web service.  That customer data needs to be combined with the data from your Project Management Software, stored in a database.  Another scenario may be when you want to report off of 2 different databases, say Access and Oracle.  This article focuses on something similar to the first scenario, but there can be other scenarios this may apply to.

There are many ways to combine different data sources for reporting.  The tools to do this are varied.  One could do a lot of this in the web service itself, combining the data.  I do not believe that is most effective for most enterprise reporting.

In using these different tools I came across Business Objects Business View Manager.  This is part of the Business Objects Enterprise software that contains many other components. 

In this article we will show how to connect to a web service and to a SQL Server database.  We will use Business Objects Business Views to combine and link the web service to the Database.  In this first part we will go over setting up the database and creating the web service used as a data source.

·         SQL Server 2000

·         Business Objects Enterprise XI

·         Visual Studio 2005

·         Windows XP Professional

This article assumes you are comfortable coding in .NET, we use C# as the primary language, T-SQL as the language for SQL statements.  Familiarity with Crystal Reports is recommended also.

For this article, we will use the pubs sample database for SQL Server.  For the purpose of this report we create a report showing the sales of each book, including information from where the book was manufactured.   To create this report, we will use the following tables:

·         Authors

·         Publishers

·         Title/author

·         Titles

·         Sales

·         Stores

See Figure 1 for a graphic of those tables.  One thing that is not included in the pubs database is a manufacturing ID.  We will need that to join our Web Service data to our data from this database.  So we will need to add that field to a table.  Normally it might be included in the actual orders table, but for the purposes of this article, we will add this to the Sales table.  We are creating this ID so that there we can link between the manufacturing information in our "ERP" system and the data in our Database.

Figure 1

To create the new column called "mfgid" we need to go into the query analyzer and execute the following commands:

Listing 1

Use pubs
alter Table dbo.sales add mfgid int

These add the foreign key that we will connect our Web Service to.

Now that our database source has a column we can connect, we can create a web service that pulls data from our ERP.  Let me make a side note here.  In many situations like this we may not have a proper foreign key to connect our ERP data to.  Sometimes we might need to join the different data sources on a less than ideal key like a location name instead of an ID.  To keep that to a minimum, try to add the unique ID during design, if possible.

Now let us create the web service.  We are actually going to create the web service using Test Driven Development techniques. 

To start out, we create the interface that both the mock objects and the real methods will utilize.  In this case, I have created a dataset iMfgLocation.xsd that includes a Datatable with the columns that the method will return.  During this article we will be utilizing the mock object class, but will assume that you can create a web service using those same interfaces and returning data in the same way.

For this example we create a class project called ERPReturnCustomers.  The main class for our mock object is called MfgLocationMock.  This object mocks what would be returned by a web service (in this case a datatable).  In other words, it is loading some bogus data, but the data is returned in the same manner real data will be returned.

The sample download included in this article includes the nunit tests used to verify that the mocks work as they should.  Once a web service is implemented, you can then implement another class that returns the real data in the same way. See the code that returns data for the mock object in Listing 2.

Listing 2

public iMfgLocation.MfgLocationDataTable ReturnLocations()
{
  iMfgLocation.MfgLocationDataTable dtNewTable = new
    iMfgLocation.MfgLocationDataTable();
  DataRow drLoad1 = dtNewTable.NewRow();
  drLoad1["ID"] = 1;
  drLoad1["LocationName"] = "Here";
  drLoad1["PlantManager"] = "Larry Bird";
  dtNewTable.Rows.Add(drLoad1);
 
  DataRow drLoad2 = dtNewTable.NewRow();
  drLoad2["ID"] = 2;
  drLoad2["LocationName"] = "There";
  drLoad2["PlantManager"] = "George Bush";
  dtNewTable.Rows.Add(drLoad2);
 
  DataRow drLoad3 = dtNewTable.NewRow();
  drLoad3["ID"] = 3;
  drLoad3["LocationName"] = "Every where";
  drLoad3["PlantManager"] = "George Bush";
  dtNewTable.Rows.Add(drLoad3);
  return dtNewTable;
}

To then set up your web service you would create the class MfgLocation.  In that class, you would have the same method ReturnLocations.  In ReturnLocations the connection to the web service happens.  So the Code may look something like Listing 3.

Listing 3

public iMfgLocation.MfgLocationDataTable ReturnLocations()
{
  iMfgLocation.MfgLocationDataTable dtNewTable = new
    iMfgLocation.MfgLocationDataTable();
  ws_RealWebService wsRealWS = new ws_RealWebService();
  DataSet dsGetData = wsRealWS.ReturnDataMethod();
  // Load Data into dtNewTable
  return dtNewTable;
}

Now that we have created this class, we will later use it to connect to our web service via the ADO.NET data connection in Business Views, which we will cover in part 2 of this article.

[Sample Download]

To sum up what we went over in this article, we wanted to show how to combine two data sources for our reports.  We decided in this part of the article that our report will show sales data on our books.  This report should include information on where the book was "manufactured," even though that information is not in our main database.  To get that information we needed to utilize a Web Service.  This part of the article showed how to create a mock web service and how we might then implement a real web service.  We also had to modify our database to add a foreign key that will allow Business Views to join the 2 different data sources.  In Part 2 we will go in depth on how this is set up in Business Views.  Until then, Happy Coding!