How to create a dataview In Sharepoint with data from a join query?

Question

I have 3 Lists in Sharepoint.

I want to create a dataview that is a join of 3 tables.

Table1 is joined with Table2 on FieldA Table 2 is joined to Table3 on FieldB

Table1 has duplicate values in FieldA so I need to only return one value to join with Table2.

In Access my query looks like this: SELECT DISTINCT WRK_InputWorkOrders.WorkOrder, Production1.[Part Number], Production1.[Work Order], Production1.Location, StationItems.Station, Production1.Description, Production1.Revision, WRK_InputWorkOrders.Status FROM StationItems INNER JOIN (WRK_InputWorkOrders INNER JOIN Production1 ON WRK_InputWorkOrders.WorkOrder = Production1.[Work Order]) ON StationItems.Item = Production1.[Part Number] WHERE (((WRK_InputWorkOrders.Status)<>"closed"));

Is there a way to write sql-like queries for dataviews?

I have Sharepoint Designer 2007 and Access.

The goal is to get a report that a user can view in Internet Explorer. I have tried using this method. But it returns duplicate records I found this suggestion. It suggests using an XPath Filter not(@yourvalue = preceding-sibling::dfs:YourRepeatingRowName/@yourvalue)

But wasn't able to get it to work. I don't know what to enter as YourRepeatingRowName

I found this link. Does anyone know if it can be used to perform such a join?

Answer 1

you want to show the query result in SharePoint Designer? I believe, SPD has merged data sources. Look into that.

Answer 2

I found this third part add on

Enesys RS Data Extension lets you query (retrieve, join, merge,...) data from any SharePoint list and use the result for building "Reporting Services" reports as you would do with any other data sources. http://www.enesyssoftware.com/

I can't use it because I am currently running the basic Sharepoint version that uses the internal database.

Answer 3

Your question is more of an ADO.NET question. Unfortunately ADO.NET doesn't have an easy way to do this, which is why companies like bamboo Solutions builds theirCross List Web Part: http://store.bamboosolutions.com/pc-42-1-cross-list-web-part.aspx

Otherwise I would attempt to use LINQ to query the tables. You might have more luck doing that.

Here is an example of a JOIN query provided by MS (I only changed the first two DataTable lines to represent filling a DataTable with an SPListItemCollection object)

DataTable orders = spListCol1.ToDataTable();
DataTable details = spListCol2.ToDataTable();

var query =
    from order in orders.AsEnumerable()
    join detail in details.AsEnumerable()
    on order.Field<int>("SalesOrderID") equals
        detail.Field<int>("SalesOrderID")
    where order.Field<bool>("OnlineOrderFlag") == true
    && order.Field<DateTime>("OrderDate").Month == 8
    select new
    {
        SalesOrderID =
            order.Field<int>("SalesOrderID"),
        SalesOrderDetailID =
            detail.Field<int>("SalesOrderDetailID"),
        OrderDate =
            order.Field<DateTime>("OrderDate"),
        ProductID =
            detail.Field<int>("ProductID")
    };

DataTable orderTable = query.CopyToDataTable();

Answer 4

Microsoft has a video demo and a writeup that may be just what you want:

Display data from multiple sources in a single Data View http://office.microsoft.com/en-us/sharepointdesigner/HA103511401033.aspx

With Microsoft Office SharePoint Designer 2007, you can link two or more data sources that contain related data and then create a single Data View that displays data from those linked data sources.

Answer 5

I've done something like this, but I wasn't able to use a dataview. I ended up writing a custom web part to do it. The approach was:

1. Use an SPQuery object to get an SPListItemCollection for each list. Use the CAML query to restrict the items returned.
2. Use the SPListItemCollection object's GetDataTable() method to retrieve an ADO.NET DataTable object for each list.
3. Add the tables to a DataSet object.
4. Create relationships between the tables.
5. Render the data however you like, using DataList or Repeater or whatever.

Here's some code that shows the broad strokes:

protected DataTable GetDataTableFromQuery(string camlQry, SPList theList) {
  SPQuery listQry = new SPQuery();  
  listQry.Query = camlQry;  
  SPListItemCollection listItems = theList.GetItems(listQry);  
  return listItems.GetDataTable();
}

protected void BuildDataSet() {  
  // get SPList objects for the lists in questions ... left as an exercise for the dev -- call them list1, list2, and list3  
  string camlQry = "the CAML necessary to retreive the ites from list1";  
  DataTable table1 = GetDataTable(camlQry, list1);  
  table1.TableName = "Table1";  
  camlQry = "the CAML necessary to retreive the ites from list2";  
  DataTable table2 = GetDataTable(camlQry, list2);  
  table1.TableName = "Table2";  
  camlQry = "the CAML necessary to retreive the ites from list3";  
  DataTable table3 = GetDataTable(camlQry, list3);  
  table1.TableName = "Table3";  

  // now build the DataSet
  DataSet ds = new DataSet();  
  ds.Tables.Add(table1);  
  ds.Tables.Add(table2);  
  ds.Tables.Add(table3);  
  ds.Relations.Add("Table1_2", ds.Tables["Table1"].Columns["FieldA"], ds.Tables["Table2"].Columns["FieldA"]);  
  ds.Relations.Add("Table2_3", ds.Tables["Table2"].Columns["FieldB"], ds.Tables["Table3"].Columns["FieldB"]);  

  // now you can do something with these, like store them in the web part class and bind them to repeaters in the web part's Render() method
}