How to create dataview in Sharepoint with data from connection request?

Question

I have 3 lists in Sharepoint.

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

Table 1 is connected to Table 2 on FieldA. Table 2 is connected to Table 3 on FieldB.

Table 1 has duplicate values ​​in FieldA, so I only need to return a single value to connect to 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.Item1). 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 tried to use this method. But it returns duplicate entries. I found this sentence. It suggests using the XPath not filter (@yourvalue = previous-sibling :: dfs: YourRepeatingRowName / @ yourvalue)

But he could not get it to work. I do not know what to enter as YourRepeatingRowName

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

Answer 1

ADO.NET. , ADO.NET , , bamboo Solutions, - Cross List: http://store.bamboosolutions.com/pc-42-1-cross-list-web-part.aspx

LINQ . , .

JOIN, MS ( DataTable, DataTable SPListItemCollection)

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 2

Microsoft , , :

http://office.microsoft.com/en-us/sharepointdesigner/HA103511401033.aspx

Microsoft Office SharePoint Designer 2007 , , , .

Answer 3

SharePoint Designer? , . .

Answer 4

, ​​

Enesys RS Data Extension (, , ,...) SharePoint Reporting Services, . http://www.enesyssoftware.com/

, Sharepoint, .

Answer 5

- , dataview. -, . :

• SPQuery SPListItemCollection . CAML .
• Use the SPListItemCollectionobject method GetDataTable()to retrieve an ADO.NET object DataTablefor each list.
• Add tables to the object DataSet.
• Create relationships between tables.
• Extract the data as you like, using DataListor Repeateror whatever.

Here is a code that shows wide 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 Render() method
}