SharePoint provides a powerful method to query for data in its sites, that is based on CAML (Collaborative Application Markup Language). The result of a CAML query is a relational table, which makes it a perfect target for pivotal data analysis.
A SharePoint Site Query can be suitable in many scenarios, including:
- Concatenating data from a dynamic set of SharePoint lists (automatically concatenate data from multiple lists, that are having common columns, template, or fall within a certain web scope).
- Concatenating data from a fixed set of SharePoint lists (automatically concatenate data from a manually enumerated set of lists).
The SharePoint Site Query connection panel is displayed when you select the SharePoint Site Query option from the Data Source Type combo:
The connection is defined by the following settings:
Site Mode - lets you select the way in which the web part will connect to the site and web. The possible options are:
Static Site - the site is specified explicitly from the Site setting. The web is specified relatively to the site.
Current Site - only the site is obtained dynamically (the site is the current site in which the web part resides). The web is specified relatively to the site from the Web setting.
Current Web - both the site and web are obtained dynamically (e.g. binds to the current web of the current site in which the web part resides).
Site - only available in Static Site mode. The web part will automatically enumerate the currently available sites in all web applications for you. If you see the site in the combo just pick it - otherwise manually specify it in the site text box.
Web - only available in Static Site and Current Site modes. For the site that you have chosen, the web part automatically enumerates the server relative paths of all webs beneath it. If you see the web just pick it - otherwise manually specify it in the web text box.
Lists CAML - contains the CAML string, that lets you select the lists, from which to retrieve data. See SPSiteDataQuery.Lists for more information.
When you want to connect to a fixed set of lists, you need to know the lists GUIDs.
One easy way to obtain the list GUID, is to go to the List Settings Page and get it from the browser address bar. Following is how the URL looks like - the bold part is the list GUID:
http://testpc2003:2020/_layouts/listedit.aspx?List=%7B6701DF77-5DCE-4D75-99C4-451EA57E827F%7D
View Fields CAML - contains the CAML string, that defines the fields (columns) that you want the query to return. See SPSiteDataQuery.ViewFields for more information.
When enumerating the names of lists columns, you need to specify the columns Internal Names, which are different from their Display Names.
One easy way to obtain the field internal name, is to first go the List Settings Page, and then click on the field to go to the Edit Field Page. Following is how the URL looks like - the bold part is the field Internal Name:
http://testpc2003:2020/_layouts/FldEdit.aspx?List=%7B6701DF77%2D5DCE%2D4D75%2D99C4%2D451EA57E827F%7D&Field=RFI%5Fx0020%5FStatus
You need to then replace the "%5Fx" with "_", so for the "RFI%5Fx0020%5FStatus" we have obtained from the URL, we finally get the Internal Field Name, which is: RFI_x0020_Status.
Webs CAML - contains the CAML string, that defines the sub webs in which to search for lists. See SPSiteDataQuery.Webs for more information.
Query CAML - contains the CAML string, that defines the query filtering and sorting rules. See SPSiteDataQuery.Query for more information.