You can retrieve data from any SharePoint list using Enesys RS Data Extension specific query syntax which is based on XML.

In its simplest form, a query string is written as follows:

The list statement makes it possible to obtain data from a SharePoint list.
The attributes and the child elements make it possible to specify the desired list, as well as any selection criteria.

The previous query retrieves all items from the SharePoint list “Products” located on the site “/sites/demo/”.

Granted that you have created a data source of type "Enesys SharePoint Data Extension 2008", this is all there needs to be to run the query within the report designer and retrieve the list items:

All columns are returned because the fields element does not specify any column.

All items are returned because no condition is specified in the query element.

A SharePoint list title can easily be modified by a user. To avoid this situation you may specify the SharePoint list to retrieve items from, by specifying its ID using the listID attribute rather that specifying its title using title attribute. The SharePoint list ID will not change over time.

However, the downside of using a list ID is that you won't be able to use the same report for some other site or for the same site.

Note that if you are using both attributes, listID will take the precedence over title . Even if you rely on list id to specify a SharePoint list, we encourage you to set title attribute as a meaningful reminder of list content.

You can specify the columns you would like to retrieve using the fields element as shown in the following screen shot:

Each column must be separated by a comma.

Instead of specifying each column,you can use a ‘*’ for retrieving all non hidden fields from the specified SharePoint list.

A filter can be applied to a list using Collaboration Application Markup Language query syntax. It doesn't take a long time to understand the basic principles. The CAML Query must be placed within the query element. The following query example will retrieve items where the Category column is equal to "Condiments":

You can also apply a filter at the report level using reporting services features. However, in that case, the whole list data is retrieved before the filter is applied. CAML filter is applied at SharePoint server side and it will improve the performance considerably if you just need a subset of the list. You can also use a mix of those filtering options when necessary.

SharePoint list columns have an internal name and a display name. When a column is initially created, the display name and the internal name of the column are the same (except if the name contains space or accent marks). When you modify the name of a column, it will only modify the display name. The internal name is never modified and you may end up with columns whose internal names no longer have any connections to the display names.

Though using internal names has the advantage of not breaking your report when a column name is changed, you can choose to use display names when specifying columns to retrieve by setting the useDisplayName attribute to true.

Reporting Services lets you define parameters at the report level so that the user may be proposed several options for running the report.

The following example shows how to return the data from the SharePoint "Products" list whose category is equal to the value of the "cat" parameter. The parameter will be replaced by the corresponding value when the report is executed.

As previously indicated, entering the parameter in the query string will automatically create the corresponding report parameter (unless it was already defined), as shown in the following image:

• sqlQuery statement with an IN clause:
  
• Caml Query In operator:
  

It is a well known limitation that Reporting Services is not able to handle html tags within a specific field. Thus, html data within a field will be displayed as plain text as shown in the following image:

Though, you may use your own approach to remove html tags by using Reporting Services embedded code features, we have added the ability to strip html tags for a specific SharePoint list using the stripHtml attribute:

The stripHtml attribute is optional and defaults to false if it's not defined.

Though not really appealing, stripping html makes text at least readable:

Though Reporting Services makes it possible to calculate running values on the fly, it will not let you use aggregate functions on running values (max, min, etc).

To overcome this limitation and being able to meet specific scenarios, Enesys RS Data Extension lets you specify columns for handling running values. In that case, the running value being seen as a column on Reporting Services side, it is possible to apply aggregation function on it.

Columns containing running values are added using a customFields child element of the list element as shown in the following example:

Each field child element of customFields element represents a specific column holding a running value. The attributes let’s you specify the name of the new column that will be holding the running value, the data type of the column, the type of running value, the grouping column and the column holding the value.

To make it simple, the previous query will retrieve the Product and Quantity columns from the “Order Details” SharePoint list, ordered by “Product”. Enesys RS Data Extension will add a custom column named “RunningTotal” (name) of type Integer (dataType) and will calculate a running sum (op) based on the “Quantity” column (param). Each time the “Product” column value changes (groupColumnName), the running sum is reset to 0.

The following image shows what you will obtain when running the previous query within the Data view.

Note that the Running Sum is reset when the product value changes from "Aniseed Syrup" to "Boston Crab Meat".

It is important to note also that Enesys RS Data Extension will not automatically order the data on the groupColumnName column ("Product" in that case). It's up to you to order accordingly the SharePoint list using an OrderBy element within the CAML query. The groupColumnName attribute will only direct ERSDE to reset the running value when the groupColumnName's column value changes.

The default behaviour when retrieving items using a list element is to return all items within all folders and subfolders.

The optional folder attribute lets you retrieve items and folders in a specific SharePoint list folder as shown in the following query:

When specifying a folder attribute, you will retrieve items located in the specified folder as well as subfolder as shown in the following screenshot:

To retrieve root items and folders, set the folder attribute to a “/” (slash) value.

Instead of specifying the folder using a literal value, you may use a report parameter (e.g.: folder="@someReportParameter!").

The rowLimit attribute let’s you specify the number of items to retrieve.

The following query will retrieve only the first five less expensive products:

The following type of SharePoint columns may have multiple values:

• Choice
• Lookup
• Person or Group
• Attachments

By default, multiple values will be separated by a comma. You may specify your own separator by setting the multiValuesSeparator attribute value like shown in the following query:

Running this query, you will get each student skill and spoken language separated by two slashes as shown in the following screen shot captured from the report designer:

Retrieving multiple values separated by some separator will not help if you would like to group items based on the possible values of such a column.

To address this scenario, it is possible to duplicate items for each value stored in the desired multiple values column. The desired multiple values column is specified using the expandMultiValuesColumn attribute as shown in the following query:

By running this query, the “Skills” column which is of type choice and holds multiple values will only get one value for each student. On the other hand, students are duplicated accordingly to reflect each skill they have as shown in the following screenshot:

Without using expandMultiValuesColumn attribute, you would retrieve items in the following form:

Rather than specifying the column to expand as a literal, you may use a report parameter (e.g.: expandMultiValuesColumn="@ChoosenColumn!")

Important notice:
Be aware that items, being duplicated (somewhat like a join operation), some calculations may not be appropriate when building reports using this approach.

Starting from version 2.1, the urls to the files that have been attached to a specific list item are available through the “Attachments” column. Prior to the version 2.1, the “Attachments” column was only returning the number of attachments.

Multiple attachments will be separated using the separator specified by multiValuesSeparator attribute or a comma if the attribute is not specified.

When you would like to display individual attachments in a report and possibly allow the user to click on the attachment for displaying the associate file, you may set the expandMultiValuesColumn attribute value to “Attachments” as shown in the following screenshot:

Items with several attachments will be duplicated thus allowing you to group items and display each attachment.

The following report layout uses this approach:

 The important things to note are:

• A level one group is used to display item details. The item’s ID is used as the grouping expression:

• The table detail group is used to display attachments
• It has been made possible to open the attachment when displaying the report by specifying a jump to URL Navigation expression as shown in the following screenshot:

It is possible to retrieve information about users in a site collection by specifying “UserInfo” as the value of the title attribute of a list query element as shown in the following screenshot:

One interesting use of the “UserInfo” list is to join it to some other list in order to get more details about users specified using a “Person or Group” column.

The query shown in the following screenshot will join an Issue list and the "UserInfo" list based on the user assigned to the issue:

Starting from Enesys RS Data Extensions version 2.1, it is possible to retrieve item versions using the itemVersions attribute.

For retrieving all versions, set itemVersions attribute value to “0”. For retrieving a specified number of item versions, set itemVersions attribute value to the number of versions you would like.

The following query shows how to retrieve all versions of items from SharePoint “Issues” list:

Important notice:

For obvious reasons, Enesys RS Data Extension relies on SharePoint Web Services rather than SharePoint object model for retrieving data. Due to SharePoint Web Services limitations, retrieving item versions is a very time consuming process. In order to improve performance, it is recommended to specify explicitly the SharePoint fields that need to be retrieved rather than keeping the field child element empty, thus retrieving all fields (even hidden ones).

Specifying a Caml query within the query child element for filtering items, when possible, will also help to improve performance when retrieving item versions.

Depending on your specific needs, an interesting option may be to jump to another report from a specific item in order to display item versions.

• The first one is based on a SharePoint list that enumerates all the lists you want to merge.
• The second approach lets you merge all lists sharing a common title within a site collection.

Use the multiList element with the type attribute set to “MergingList” for merging a set of SharePoint lists that are enumerated in a specific SharePoint list. The term "merging list" will be used to refer to this list.

You specify the merging list that is to be used by setting its title and relativeSiteUrl attributes.

The merging list is a SharePoint list with four mandatory columns. Each item of this list will provide information about a SharePoint list that is to be merged.

The SharePoint lists that need to be merged are not necessarily completely identical as you can specify the columns that will be merged using the fields element. Moreover, you can specify a filter that will be applied to each list before being merged:

A specific column named " rstLabel" is added to the result set obtained from the multiList query element. This column will be filled with the value of the merging list's column information depending on the list from which the item is retrieved:

Use the multiList element with the type attribute set to “RollUpList” for merging lists having the same “title” within an entire site collection.

The following query will merge all SharePoint lists named “Contacts 01” within the entire “/sites/demo/” site collection:

You are not obliged to merge lists starting from the root of the site collection. It is possible to specify a child site as the starting point of the merging process (e.g.: relativeSiteUrl=”/sites/demo/demosubsite01/”).

When running such a query, a column named “rstLabel” will be added to the result set. This column will contain the relative site Url of the list from which the items have been retrieved as shown in the following screenshot where the previous query is run from the report designer:

Rather than specifying the starting site in the query, you may use a report parameter (e.g.: relativeSiteUrl=”@StartingSiteUrl!”).

The listCollection query element lets you retrieve information about all the lists in a site or a set of sites.

Optionally, you can retrieve the permissions as well for each list by setting the includePermissions attribute to true.

The listPermissions query element lets you retrieve permissions from a specific list.

Parameters may be used for the listID and relativeSiteUrl attributes values.

The webPermissions query element lets you retrieve the permissions given at the site level for a specific site or a set of sites.

Optionally, you can also retrieve the users (or AD Groups) belonging to each group by setting the expandGroups attribute to true.

The webGroups query element lets you retrieve SharePoint groups from a site collection.

Optionally, you can also retrieve the users (or AD Groups) within each group by setting the expandGroups attribute to true.

Except listPermissions, all those commands are sharing a common approch for specifying sites for which they apply.

You can specify the sites for which the commands apply in one of these three ways:

• By writing directly the relative url of the site (eg: /sites/demo/) within the query

• By using a report parameter so that the user might select the site when running the report.

• By specifying a SharePoint list that contains the list of sites to handle.

We have included several sample reports that lets you display list, groups and permissions for your SharePoint sites. The provided reports can be used as is or as a starting point for creating your own reports.

You can use the reports as the basis for permissions and groups analysis and have an overall vision of the permissions set for the lists of a site. You can also use the reports as an interactive tool as we have implemented Reporting Services Navigation features that lets you jump directly from the report to SharePoint Site and List Settings pages or to other reports for details refinements.

The IT - List Collection from sites in a reference list report will display information about lists for the set of sites defined in the list named "Site collection" (the SharePoint list named "Site Collection" contains an item for each site) . While viewing the report, you can click on the name of a list to launch a report that will display permissions details for the list. You can click on "Display Group details" to launch a new report that will display permissions (roles) for the specific site.:

List Collection Report - Click for larger image

The " IT - Lists permissions " and " IT - Lists and permissions for a specific site " reports lets you display in one report both lists collection and permissions for each list. While viewing the report, you can click on the list title to open the SharePoint Customize page fo the list (see the image). You can click on " Display Group details " to launch a new report that will display permissions (roles) for the specific site. Permissions are not displayed by default, you must click on the plus sign (+) to display the permissions associated with the lists of a specific type:

Lists and Permissions report - click for larger image

The " IT - SharePoint Groups and Users for a site " report lets you display site groups and users for a specific site. While viewing the report, you can click on the " Manage Site Groups " link to open the SharePoint "Manage Site Groups" page for the selected site (see the image below).

Web Permissions report - click for larger image

All credential options available with Reporting Services have been implemented unless it would not make sense in the context of Enesys RS Data Extension .

You can run the report using an account for which credentials are stored securely on the Report server:

You can run the report under the credentials of the current user running the report :

You can ask for the user to enter its credentials when running the report :