Display data from multiple sources in a single Data View

Most enterprises store related data in different locations. For example, product information is stored in an .xml file called products.xml, but information about product categories is stored in another .xml file called categories.xml. The two data sources are related to one another by a field called CategoryID.

With Microsoft Office SharePoint Designer 2007, you can easily link data sources that contain related data and then create a single Data View that displays data from those linked data sources. You can even link different types of data sources — for example, you can link an XML file to an SQL database.

When you link data sources, you create a single data source that appears in the Data Source Library with all your other data sources. Creating a view of this linked data source is much like creating any other Data View. And after you create a Data View of a linked data source, you can filter, sort, group, format, or do anything with that Data View that you can do with any other Data View.

This article shows you how to create two sample XML files to use as data sources, how to link the two data sources, and then how to create a single Data View that displays data from both data sources. This articles uses an .xml file as a data source so that you can easily create the data source and follow along. However, the following procedure works with any data source, including SQL databases.

 Important   This article discusses creating Data Views in SharePoint sites that are located on servers running Windows SharePoint Services 3.0. Not all functionality may be available if your site is not located on a server running Windows SharePoint Services 3.0.

To learn more about Data Views, see Create a Data View.

In this article


Create two .xml files as sample data sources

Northwind Traders maintains their product list in XML format. They also maintain a category list. The two lists are associated by a field called CategoryID. By linking these two XML files and creating a single Data View, you can easily create a list of products that are organized by product category.

Create products.xml

If you already created products.xml for Create a Data View, skip to the next section.

  1. On the File menu, click Open Site.
  2. In the Open Site dialog box, browse to and select your SharePoint site, and then click Open.
  3. If prompted, in the Connect to dialog box, type your user name and password, and then click OK.
  4. On the File menu, click New.
  5. In the New dialog box, double-click Text File.
  6. Copy the following lines of code by selecting them and then pressing CTRL+C.
<?xml version="1.0" encoding="utf-8"?>
<ProductsRoot>
    <Products>
        <ProductID>1</ProductID>
        <ProductName>Chai</ProductName>
        <SupplierID>1</SupplierID>
        <CategoryID>1</CategoryID>
        <QuantityPerUnit>10 boxes x 20 bags</QuantityPerUnit>
        <UnitPrice>18</UnitPrice>
        <UnitsInStock>39</UnitsInStock>
        <UnitsOnOrder>0</UnitsOnOrder>
        <ReorderLevel>10</ReorderLevel>
        <Discontinued>0</Discontinued>
    </Products>
    <Products>
        <ProductID>2</ProductID>
        <ProductName>Chang</ProductName>
        <SupplierID>1</SupplierID>
        <CategoryID>1</CategoryID>
        <QuantityPerUnit>24 - 12 oz bottles</QuantityPerUnit>
        <UnitPrice>19</UnitPrice>
        <UnitsInStock>17</UnitsInStock>
        <UnitsOnOrder>40</UnitsOnOrder>
        <ReorderLevel>25</ReorderLevel>
        <Discontinued>0</Discontinued>
    </Products>
    <Products>
        <ProductID>3</ProductID>
        <ProductName>Aniseed Syrup</ProductName>
        <SupplierID>1</SupplierID>
        <CategoryID>2</CategoryID>
        <QuantityPerUnit>12 - 550 ml bottles</QuantityPerUnit>
        <UnitPrice>10</UnitPrice>
        <UnitsInStock>13</UnitsInStock>
        <UnitsOnOrder>70</UnitsOnOrder>
        <ReorderLevel>25</ReorderLevel>
        <Discontinued>0</Discontinued>
    </Products>
    <Products>
        <ProductID>4</ProductID>
        <ProductName>Chef Anton's Cajun Seasoning</ProductName>
        <SupplierID>2</SupplierID>
        <CategoryID>2</CategoryID>
        <QuantityPerUnit>48 - 6 oz jars</QuantityPerUnit>
        <UnitPrice>22</UnitPrice>
        <UnitsInStock>53</UnitsInStock>
        <UnitsOnOrder>0</UnitsOnOrder>
        <ReorderLevel>0</ReorderLevel>
        <Discontinued>0</Discontinued>
    </Products>
    <Products>
        <ProductID>5</ProductID>
        <ProductName>Chef Anton's Gumbo Mix</ProductName>
        <SupplierID>2</SupplierID>
        <CategoryID>2</CategoryID>
        <QuantityPerUnit>36 boxes</QuantityPerUnit>
        <UnitPrice>21.35</UnitPrice>
        <UnitsInStock>0</UnitsInStock>
        <UnitsOnOrder>0</UnitsOnOrder>
        <ReorderLevel>0</ReorderLevel>
        <Discontinued>1</Discontinued>
    </Products>
 </ProductsRoot>
  1. In Office SharePoint Designer 2007, position the insertion point at the top of the page, and then press CTRL+V to paste the code that you just copied into the page.
  2. On the File menu, click Save As.
  3. In the Save As dialog box, in the File name box, type products.xml.
  4. In the Save as type list, click XML, and then click Save.

Create categories.xml

  1. If you have a SharePoint site open, skip to step 4. Otherwise, on the File menu, click Open Site.
  2. In the Open Site dialog box, browse to and select your SharePoint site, and then click Open.
  3. If prompted, in the Connect to dialog box, type your user name and password, and then click OK.
  4. On the File menu, click New.
  5. In the New dialog box, double-click Text File.
  6. Copy the following lines of code by selecting them and then pressing CTRL+C.
<?xml version="1.0" encoding="utf-8"?>
<CategoriesRoot>
    <Categories>
        <CategoryID>1</CategoryID>
        <CategoryName>Beverages</CategoryName>
        <Description>Soft drinks, coffees, teas, beer, and ale</Description>
    </Categories>
    <Categories>
        <CategoryID>2</CategoryID>
        <CategoryName>Condiments</CategoryName>
        <Description>Sweet and savory sauces, relishes, spreads, and seasonings</Description>
    </Categories>
    <Categories>
        <CategoryID>3</CategoryID>
        <CategoryName>Confections</CategoryName>
        <Description>Desserts, candies, sweetbreads</Description>
    </Categories>
    <Categories>
        <CategoryID>4</CategoryID>
        <CategoryName>Dairy Products</CategoryName>
        <Description>Cheeses</Description>
    </Categories>
    <Categories>
        <CategoryID>5</CategoryID>
        <CategoryName>Grains/Cereals</CategoryName>
        <Description>Breads, crackers, pasta, and cereal</Description>
    </Categories>
    <Categories>
        <CategoryID>6</CategoryID>
        <CategoryName>Meat/Poultry</CategoryName>
        <Description>Prepared meats</Description>
    </Categories>
    <Categories>
        <CategoryID>7</CategoryID>
        <CategoryName>Produce</CategoryName>
        <Description>Dried fruit and bean curd</Description>
    </Categories>
    <Categories>
        <CategoryID>8</CategoryID>
        <CategoryName>Seafood</CategoryName>
        <Description>Seaweed and fish</Description>
    </Categories>
</CategoriesRoot>
  1. In Office SharePoint Designer 2007, position the insertion point at the top of the page, and then press CTRL+V to paste the code that you just copied into the page.
  2. On the File menu, click Save As.
  3. In the Save As dialog box, in the File name box, type categories.xml.
  4. In the Save as type list, click XML, and then click Save.

Top of Page Top of Page

Link the data sources

Before you can create a Data View that displays data from multiple sources, you must first link the data sources by using the Data Source Library. You want to create a list of products that are organized by product category, so you need to create a data source that links the products.xml file to the categories.xml file.

  1. On the Task Panes menu, click Data Source Library.
  2. In the Data Source Library task pane, click the plus sign (+) next to Linked Sources, and then click Create a new Linked Source.
  3. In the Data Source Properties dialog box, click Configure Linked Source.
  4. In the Link Data Sources Wizard, under Available Data Sources, click categories.xml, and then click Add. Next, click products.xml, and then click Add.
  5. Click Next.
  6. Under Select the link type that best represents the relationship between the selected sources, click Join the contents of the data sources by using the Data Source Details to insert data views and joined subviews.

You have two options: merge or join. You merge data when you have different data sources with the same fields that you want to merge into a single data source. You join data when you have two different data sources that have a field in common that associates one data source with another data source. Because products.xml and categories.xml are two different data sources with the CategoryID field in common, you want to join the two data sources.

  1. Click Finish.
  2. In the Data Source Properties dialog box, click the General tab.
  3. In the Name box, type Categories and Products.
  4. Click OK.

Linked sources section of Data Source Library showing new data source called Categories and Products

In the Data Source Library, under Linked sources, you can see your new linked data source.

Top of Page Top of Page

Create a Data View

Now that you have linked the data sources, you can create a Data View that displays data from the linked data source by using the Data Source Details task pane.

You want to create a Data View of the product list that is organized by product category. For each category in categories.xml, you can show the products in each category from products.xml.

Because you want to create a Data View that displays each product by category, you first want to create a view of categories.xml.

Data Views are based on Microsoft ASP.NET 2.0 technology. To create a Data View, you must start with an ASP.NET page (.aspx file). In this example, you will create a new ASP.NET page and then insert a Data View into the page.

  1. On the File menu, click New.
  2. In the New dialog box, double-click ASPX.

A new page with a FORM tag opens.

ASP.NET form tag on page

  1. On the Data View menu, click Insert Data View.

An empty Data View is displayed on the page, and the Data Source Library task pane opens.

Empty Data View Web Part

Now that you have inserted the Data View, you are ready to add the data.

  1. In the Data Source Library task pane, click the plus sign (+) next to Linked Sources, click Categories and Products, and then click Show Data.

You first want to create a Data View of categories.xml.

  1. In the Data Source Details task pane, in the Categories folder, click CategoryName. Press and hold down CTRL while you click Description.

Two fields selected in the Categories folder in the Data Source Details task pane

  1. Click Insert Selected Fields as, and then click Multiple Item View to insert the selected data into the Data View.

 Note   Single Item View displays a single record in a Data View. Multiple Item View displays multiple records, with the fields in columns and headings for each column.

The fields that you selected in the Data Source Details task pane now appear in the Data View.

Data View showing records from categories.xml

In the next step, you will create a new column that displays a subview (a Data View that appears inside a cell of another Data View) of the data in products.xml.

  1. In the Data View, right-click the Description column, click Insert on the shortcut menu, and then click Column to the Right.
  2. Position the insertion point inside a cell in the new column.
  3. In the Data Source Details task pane, in the Products folder, click ProductName. Press and hold down CTRL while you click UnitsInStock, UnitsOnOrder, and ReorderLevel.

Products folder in Data Source Details task pane

  1. Click Insert Selected Fields as, and then click Joined Subview.

A joined subview is a subview of a data source that is joined to another data source by a common field. In the next step, you will link the CategoryID field in categories.xml to the CategoryID field in products.xml.

  1. In the Join Subview dialog box, under Categories, click CategoryID. Under Products, click CategoryID, and then click OK.

Join Subview dialog box

The fields that you selected in the Data Source Details task pane now appear in the new column. Because the CategoryID fields between the two sources are linked, only the beverage products appear in the Beverages list, only condiments appear in the Condiments list, and so on.

Data View showing a subview of product list within associated category in list

Top of Page Top of Page

Suggested next steps

Data Views are powerful, fully customizable views of live data sources. After you create a Data View of a linked data source, you can customize that Data View the same as any other Data View. Now that you have learned how to create a single Data View that displays data from multiple sources, you might want to learn how to:

  • Filter data in a Data View    You can easily filter data in a Data View. For more information, see Filter data in a Data View.
  • Sort and group data in a Data View     You can quickly sort or group data in a Data View. For more information, see Sort and group data in a Data View.
  • Apply conditional formatting to a Data View    You can apply specific formatting to a Data View when data in the Data View meets certain conditions. For more information, see Apply conditional formatting to a Data View.
  • Create a formula column in a Data View    Just as you can with a spreadsheet, you can add formulas to your Data View that perform calculations on your data and display the results in a separate column. For more information, see Create a formula column in a Data View.

Top of Page Top of Page

 
 
Applies to:
SharePoint Designer 2007