As mentioned in Part 1 of this article, there are many times your customers may want reports that combine data from different sources.  In this article we will concentrate on putting the data together in Business Objects Business Views.

In Part 1 of this article, we showed how to combine a web service with some sales data in our SQL Server database.  We created a mock web service and then gave a quick example of how we might then implement a real web service from our ERP.  We also had to modify our database to add a foreign key that will allow Business Views to join the 2 different data sources.

The report we are working on shows sales data about the books we have sold.  It also includes information on where the book was "manufactured," even though that information is not in our main database.  The data sources are set, now we just need to connect them and show that in a report.

Requirements: SQL Server 2000, Business Objects Enterprise XI, Visual Studio 2005, Windows XP Professional, and Crystal Reports XI.  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.

As mentioned previously, the pubs sample database for SQL Server is the basis of our SQL Server data.  Before we can add tables to the view, we need to set up a connection.  To set up our tables in Business Views we will first make a data connection to the SQL Server.  Simply open the Business Views application and connect to your server.  You will see a screen similar to the one in Figure 1.  Now select Data Connection and click on Create.  This process is similar to setting up data connections in Crystal Reports, so your knowledge there will help you here.

Figure 1

A wizard steps you through the steps of creating the connection to SQL.  In my case I selected OleDB (ADO) as the Data Source type and then SQL Native Client.  After selecting my server and the pubs database, that first data connection is set up.  Let us name this connection DataConnection1. 

Now let us quickly set up the connection to our web service class.  For this I use the ADO.NET data type.  When it prompts you for a filepath, point to the path containing the dll to the class we created.  You will need to select "File of Type" and ".NET DataSet Provider."  Once this is selected then check the box "Use Classes from Project."  From this there is a drop down box and you can select ERPReturnCustomers.Mocks.MfgLocationMock.  Finally, check the box "use dataset from class" and select ReturnLocations.  We will name this DataConnection2.  Look at Figure 2 for a sample of what this should look like.

Figure 2

Now that our data connections are set up let us get the tables linked!  To do that, we create a Data Foundation.

To create a data foundation, select Data Foundation from the New File Menu option.  You then step through a wizard to help define the Foundation.  In this sample we select DataConnection1 to help select the tables. See Figure 3 for an example of how to set up that connection.  Once the pubs database is selected pick the following tables:

·         Authors

·         Publishers

·         Titleauthor

·         Titles

·         Sales

·         Stores

Figure 3

After selecting these tables they should display graphically for you.  The Insert Data Tables dialog box should still show.  Now we can add the web service table by clicking on add connection.  Next, select DataConnection2 from the available connections.  After clicking OK (you may need to click cancel after that), you should be back in the Insert Data Tables dialog box, with DataConnection2 showing up (or whatever you named your data connection for the web service).  Click on the table MfgLocation and then click "Add" to add this to the other tables in the Data Foundation.

You should be able to link using drag and drop or using the option Smart Linking by Key.  Using that option links all tables correctly, except the mfgLocation table.  You need to manually link this by dragging the mfgid field from sales onto the ID field in the mfgLocation table.  Now your Data Foundation is ready!

Now that the Data Foundation is set up, you just need to create two more objects.  First we will set up the Business Element and then the Business View.  Once we do these, we are ready to use this in a report.  The easiest way to set this up is to follow the wizard after selecting New Business Element from the menu.  See Figure 4 for a list of the fields we have used in the Business Element.

Figure 4

To create a Business View with these Business Elements in it, simply go to Create a new Business View and follow that wizard.  Using the wizard, select the business element you just created.  For future reference you can add multiple business elements to your Business Views.  Once you have added this, you can save the View, which we will use in our report.

Now we can create our Crystal Report.  To create this report, first start the report wizard.  For the connection, select the Business objects Repository.  Once you connect to the repository (you will be prompted for connection information), you can select the Business View we just created.  When you select that Business View you can now add fields to the report.  For instance, we added the following fields to our report (see Figure 5).

Figure 5

From here we can group this by our Location Name then by title. This information might be interesting to view. 

[Sample Downloads]

In this article, we have looked over how to combine SQL Server data and a Web Service using Business Objects Business Views software.  We stepped through creating the Data Connection, the Data Foundation, the Business Element and Business View.  Finally we created a Crystal Report that utilized this data.  There is a lot more that can be done utilizing Business Views; hopefully this article shows you where you can start.  Remember to keep on creating reports and keep on coding!