You can easily retrieve data from any SharePoint list by using a List Statement.
· Right click on the “Query Statements” panel.
· Click on “Add list statement…” to add a new list statement.
· Enter the relative site URL of your SharePoint site containing the desired list and click on “Check available lists” to get all the lists available in the site.
· Select the desired list(s) and click on the “Add” button
The Query Builder will automatically create the query string for you. When adding a List, the default behavior is to retrieve all items for all columns from the specified list. As you will see, you can be more specific about the data to retrieve from the list (columns, conditions,...).
You only need to run the query and retrieve the list items:
You can specify the columns you would like to retrieve using the Columns tab in Query Builder.
Instead of checking each column box, you can check the ‘*’ box for retrieving all non hidden fields from the specified SharePoint list.
Leaving all boxes unchecked will return all columns from the SharePoint list, including hidden ones.
It is recommended to specify the exact columns you need in your query. Not only it will perform better but it will also avoid possible unwanted side effects.
Indeed, the columns you specify in your query will serve as the basis for specifying your report’s dataset schema. Just know that this schema definition is static and that all the columns specified will need to exist when you execute your report even if you don’t use some of them in your report.
By specifying all columns or all non hidden columns, your schema will contain all columns (or non hidden columns). At a later time, if some unimportant column is deleted from your SharePoint list, your report will not work anymore even though you were not using the deleted column in your report’s layout.
A filter can be applied to a list using Enesys Query Builder’s Columns/Filters/Sorting tab.
A large range of conditions is available to design specific filters as shown in the following screenshot:
The following condition will retrieve items where the Category column is equal to "Condiments".
Note that conditions applied at the List level are written in Collaboration Application Markup Language. Enesys SharePoint Query Builder will generate the necessary CAML for you as shown in the following image:
Filtering at the list level is not your only option. You can also filter using a SqlQuery (see later in the documentation) statement or even at the report level using Reporting Services features. In some cases, for more complex queries, it is not possible to rely on conditions that apply on a single SharePoint list. You can use a mix of those filtering options as necessary.
As a rule of thumb, you should filter at the List level as much as possible. CAML filter is applied at SharePoint server side and will improve the performance considerably.
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.
You can choose to use display names when specifying columns to return by setting the useDisplayName attribute to true.
We recommend using internal names rather than display names. It has the advantage of not breaking your report when a column name is changed. This is something that can easily happen with SharePoint lists.
Reporting Services lets you define parameters at the report level so that the user may be proposed several options for running the report.
Parameters may be used in various ways in the context of Enesys RS Data Extension queries (attributes, query…). A parameter is composed of a name surrounded by the characters @ and ! (e.g. @product!).
When you specify a parameter in a query and the parameter is not already defined at the report level, it will be automatically created when you select the Layout view.
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.
When running the query in Enesys SharePoint Query Builder, you will be asked to enter the value of the “cat” parameter:
The parameter in the query string will automatically create the corresponding report’s parameter (unless it was already defined), as shown in the following image:
Note that you can provide the parameter’s values by using another dataset that will return values from another SharePoint list. A common usage is to create a dataset that returns distinct values from the column filtered by the parameter so that the user running the report can select a value amongst the existing values.
Multi-value parameters are supported. However their use only makes sense in the following constructs:
· sqlQuery statement with an IN clause:
· Pseudo CAML Query In operator:
· The value of the filter element from the rollUpLists statement:
Starting from version 3.2, Enesys RS Data Extension supports variables at the data source level. Variables are limited to the title and relativeSiteUrl attributes.
Data source variables are variables that you define in the data source connection string. A data source variable is composed of a name surrounded by the “$” and “!” characters (e.g.: $siteUrl!). The following screen shot shows a shared data source connection string that defines two variables (siteUrl and recEvensUrl). The values of those variables are respectively “/sites/ersdedemo/” and “/sites/ersdedemo/some recurring event/”.
What’s the point of using data source variables? Basically, it will let you parameterized the relative site URLs used for any number of reports. If you would like to move your reports to some other SharePoint server, you would just need to change the value of the data source variables instead of going through each report and change the relative site URL.
The rest of this chapter will describe how you can use the data source variables in combination with Enesys SharePoint Query Builder.
Using a variable in the relativeSiteUrl attribute
When adding new list, mergeLists or rollUpLists statements, you can specify a variable instead of the relative site URL:
By doing this, your query will use the “$siteUrl!” variable in the relativeSiteUrl attribute instead of the relative site URL specified as a literal value:
Using a variable in the title attribute
You can also use variables instead of literal values in title attributes.
Considering that I already have a variable “$list!” that is equal to “Products”, we will show how to add a list named “Products” and replace its title by “$list!”.
· In the “Add List Statement” form, select the “Products” list and add it to your project.
· Right click on the Products list and click on the “Use Variable in Title” menu
· The query now uses “$list!” in the list title:
You can revert to the literal value by un-checking the “Use Variable in Title” menu:
Your query will then re-use the “Products” title instead of “$list!”:
The expandRecurrent attribute let's you expand recurring events for an event list.
The expandRecurrent attribute goes along with expandFirstDate and expandLastDate required attributes that lets you define the range of dates for which recurring events will be expanded. Parameters may be used to set expandFirstDate and expandLastDate values.
The expandRecurrent attribute possible values are:
· Automatic: retrieves both recurring and non-recurring events for the date range specified by the expandFirstDate and expandLastDate attributes
· Standard: retrieves all events according to the CAML query and expands the recurring events for the date range specified by the expandFirstDate and expandLastDate attributes.
· None: retrieves events without expanding recurring events
In order to obtain events between two dates including the recurring events, you would set up the following query, using the “Automatic” mode:
The “Automatic” mode is the easiest way to retrieve events (both recurring and non-recurring events) for a given date range. Unless you have a very specific scenario, you will mostly use this mode when retrieving events from an event list.
Note that all events overlapping the specified date range are taken into account. If you only want to discard the overlapping events, you must add a sqlQuery statement as shown in the following picture.
SELECT *
FROM Events
WHERE datetime(EventDate) >= datetime(@firstDate!)
AND datetime(EndDate) <= datetime(@lastDate!)
It is a well known limitation that Reporting Services 2005 (this is not the case anymore with SSRS 2008) 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:
SSRS 2008 is able to render HTML tags (a subset). In most cases, this feature should not be necessary if you are using SSRS 2008.
The default behavior when retrieving items using a list statement is to return all items within all folders and subfolders. You can override this behavior by specifying a value for the optional folder and viewScope attributes.
The folder attribute lets you specify the folder name from which the items will be retrieved. When not specified, the root folder is the default value.
The viewScope attribute lets you specify which items will be retrieved. Valid values are:
· Default: retrieves only the files and subfolders in a specific folder (default value when a folder is specified)
· FilesOnly: retrieves only the files of a specific folder.
· Recursive: retrieves all files of all folders starting from the specified folder (default value when no folder is set)
· RecursiveAll: retrieves all files and all subfolders of all folders starting from the specified folder
When specifying a folder attribute, you will retrieve items located in the specified folder as well as subfolders 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 can use a report parameter (e.g.: folder="@someReportParameter!").
If you want to retrieve all items and all folders, just leave the folder attribute empty and set the viewScope attribute to RecursiveAll:
The rowLimit attribute lets you specify the number of items to retrieve.
The paging attribute lets you specify how many items at once should be retrieved from a SharePoint list. Enesys RS Data Extension will retrieve list’s items in chunks of the number of specified items by the paging attribute.
By default, Enesys RS Data Extension will retrieve items from a SharePoint list in chunks of 4000 items. However, if you want to put less pressure on your SharePoint server, especially if you are retrieving a large number of columns, you may want to specify a lower number.
Please note that the paging attribute overrides the rowLimit attribute. Therefore, it is not possible to specify a rowLimit attribute when specifying a paging attribute. It is however possible to mimic the rowLimit feature at the list level by adding a sqlQuery statement as shown in the following sample that shows a query returning 1500 items from the product list:
The following type of SharePoint columns may have multiple values:
· Choice,
· LookupMulti,
· UserMulti
· 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 screenshot:
By running this query, you will get each student skill and spoken language separated by two slashes as shown in the following screen shot:
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 as if you were doing a “Join” with those values. The desired multiple values column is specified using the expandMultiValuesColumn attribute as shown in the following screenshot:
By running this query, the “Skills” column (which is a lookup column accepting multiple values) will only get one value for each student. On the other hand, students are duplicated accordingly to reflect each skill 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!").
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 statement 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 Extension 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 screenshot shows how to retrieve all versions of items from SharePoint “Issues” list.
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 fields 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.
When retrieving items from lists within a meeting workspace, you may specify the meeting instance for which you would like to retrieve items.
To retrieve items for all meeting occurrences, you can set the meetingInstanceId attribute value to “-1” as shown in the following screenshot:
You are not limited to build reports based on a single SharePoint list. Enesys RS Data Extension lets you apply specific operations between SharePoint lists.
You can define as many list statements as necessary and apply as many operations (sqlQuery statements) as you like in order to obtain the desired Dataset from which you will build your report.
Each list statement defined within a query returns a set of data items from a SharePoint list for which we will use the generic term "result set". The tableName attribute is used to give a unique name to this "result set". The unique name of the result set will serve as the basis for specifying result sets involved in operations.
You can manipulate "result sets" by using sqlQuery statements. The data resulting from a sqlQuery statement is considered a "result set" as well and it can be further manipulated using other sqlQuery statements.
The following query shows three list statements used to retrieve data from three specific SharePoint lists as well as a sqlQuery statement used to further manipulate (join and group) the data retrieved from the SharePoint lists.
The result of the sqlQuery statement will serve as the dataset needed by Reporting Services.
The SQL JOIN operation lets you join matching items between two result sets based on their joining columns. The tableName attribute lets you give a unique name to the set of data resulting from the inner join operation.
The following image displays a JOIN statement between the Products and the Suppliers SharePoint lists. Rather than using the supplier value pointed to by the lookup column, the query relies on the supplier ID.
You will notice that the “SupplierID” column has not been selected in the “Products” list. It has been automatically created by Enesys RS Data Extension for holding the ID of the “Supplier” pointed to by the lookup column.
Joining lists is useful (though not limited to that usage) when dealing with SharePoint lists linked with a lookup field.
Note that a SharePoint lookup column will return 2 columns in the resulting data for holding both the actual value and the target list item’s ID. It is recommended to join lists using those ID rather than the actual value.
You can merge two result sets by using a SQL UNION operation:
The DISTINCT keyword is especially useful for creating a report’s dataset used to set the available values of a report’s parameter.
Say you have a SharePoint Products list with a Category column and you would like to let the user of your report select at run time the category of products that will be displayed in the report. You can easily achieve this by creating a specific report’s dataset as shown in the following image:
You can merge a variable number of list in a single operation using the mergeLists statements.
The mergeLists statement will let you merge 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 selecting it from you SharePoint site:
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. Note that the lists may be located in any SharePoint site.
The SharePoint lists that need to be merged are not necessarily completely identical as you can specify the columns that will be merged by selecting the fields you want to merge. 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 mergeLists query statement. 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:
You can use the rollUpLists statement for merging lists items within an entire site collection based on a specific list’s name.
When using the rollUpLists statement, the lists from the site you specify and from all sub-sites will be displayed:
You are not bind to merge lists starting from the root of the site collection. It is possible to specify a child site as the starting point (i.e.: relativeSiteUrl=”/sites/ersdedemo/demosubsite01/”).
When rolling up items using the rollUpList statement, two columns will be added to the result set. The first column “relativeSiteUrl” contains the relative site URL of the list while the second, “rstLabel” contains the name of the list and the relative site URL of the list from which the items have been retrieved as shown in the following screenshot:
Rather than specifying the starting site in the query, you may use a report parameter (e.g.: relativeSiteUrl=”@StartingSiteUrl!”).
Filtering the sites
Starting from version 3.2, you can filter the sites that will be used to roll up items.
The filter property let’s you specify the sites to include. Multiple sites may be specified by separating them with a comma. You can also use a report’s parameter instead.
Note that the site names are relative to starting site (relativeSiteUrl) specified.
For more information about the other properties (includeRoot, recursive and strict), see the reference manual.
Starting from version 3.1, Enesys RS Data Extension (Standard and Enterprise editions only) provides specific query statements for retrieving sites and lists information such as list collection, list permissions, site permissions, etc.
All those statement share a common approach to specify the sites or lists you are interested in using the from properties.
The following image shows an aListCollection statement which retrieves information about all lists available in the site “/site/ersdedemo/”:
You can filter the resulting data by using the where property. This property can contain a SQL SELECT clause:
The following table describe the various statements available in this category.
|
|
|
|
List collection
|
The aListCollection query statement lets you retrieve information about all the lists in a site or a set of sites.
|
|
List columns
|
The aListColumns query statement lets you retrieve the columns schema for one or more lists.
|
|
List permissions
|
The aListPermissions query statement lets you retrieve permissions from one or multiple lists.
|
|
Site collection
|
The aSiteCollection query statement lets you retrieve all the sites that are contained in a site collection.
|
|
SharePoint groups
|
The aSiteCollectionGroups query statement lets you retrieve all the SharePoint groups that are available in one or more site collections.
|
|
Site columns
|
The aSiteColumns query statement lets you retrieve the column definitions for each column available on the specified sites.
|
|
Site content types
|
The aSiteContentTypes query statement lets you retrieve the content type definitions for each content type available on the specified sites.
|
|
Site permissions
|
The aSitePermissions query statement lets you retrieve the permissions given at the site level for a specific site or a set of sites.
|
|
User collection
|
The aUserCollection query statement lets you retrieve the information for the specified users, and the information for the users in the specified groups.
|
Because it makes sense for instance to get list collection for all sites in a collection or list permissions for all lists in a site, it is possible to use the result of a specific statement as the input of another statement.
The following example explains how to get the list collection from all sites in a specific site collection.
You will add two administrative statements: aSiteCollection and aListCollection.
Modify the “From” property of the aSiteCollection statement in order to retrieve all site in a specific site collection (/sites/ersdedemo/ in our example):
In order to retrieve information about all lists available in all sites of your site collection, you will need to specify the from type of aListCollection to “ResultSet” and then choose the name of your previous statement (aSiteCollection1) as value when type=”ResultSet” as shown in the following screenshot:
Starting from version 3.1, Enesys RS Data Extension (Standard and Enterprise editions only) makes it possible to join SharePoint data and external data by using the XmlQuery statement.
The XmlQuery statement lets you query an URL you specify for retrieving data in a XML format. It is therefore possible to write some .aspx (or any other technology) page that will query for instance a SQL database and return the result in XML format. The data can then be consumed by an XmlQuery statement and joined with SharePoint data.
We will now describe this process in mode details.
In the following scenario we will see how to create an .aspx page that will query a SQL table and return XML data.
Here is a sample ASPX page (named SqlData.aspx) that will do the following:
· query a SQL table,
· fill a dataset,
· convert the dataset to XML using WriteXml method,
· write the XML data to the response stream.
<%@ Page Language="C#" AutoEventWireup="true" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<script type="text/C#" runat="server">
const string ServerName = "<SERVERNAME>";
const string DatabaseName = "<DATABASE>";
const string SqlQuery = "SELECT [CategoryID], [CategoryName], [Description] FROM [Categories] WHERE [CategoryName] IN ('Beverages', 'Condiments')";
// You can choose between Integrated Security and the specified SQL User/Password
//const string ConnectionStringFormat = "Data Source={0};Initial Catalog={1};User Id={2};Password={3}";
const string ConnectionStringFormat = "Data Source={0};Initial Catalog={1};Integrated Security=true";
void Page_Load(object sender, EventArgs e)
{
string connectionString = string.Format(ConnectionStringFormat, ServerName, DatabaseName);
try
{
DataSet ds = GetDataSet(SqlQuery, connectionString);
WriteResponse(ds);
}
catch (Exception ex)
{
Response.Write(string.Format("An error occurred while retrieving data:<br/>{0}", ex.Message));
}
}
static DataSet GetDataSet(string sqlQuery, string connectionString)
{
DataSet ds = new DataSet();
using (SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, connectionString))
{
adapter.Fill(ds);
}
return ds;
}
void WriteResponse(DataSet ds)
{
StringWriter sw = new StringWriter();
ds.WriteXml(sw, XmlWriteMode.WriteSchema);
Response.Write("<?xml version=\"1.0\" encoding=\"utf-8\" ?>");
Response.Write(sw.ToString());
}
</script>
You can publish the page in its own web site, in an existing web site or even within SharePoint’s Layout folder (%commonprogramfiles%\Microsoft Shared\Web Server Extensions\12\TEMPLATE\LAYOUTS\ – that’s the approach we have taken while building this sample). Once the page is correctly published, you will be able to access it within the browser and get XML data as shown in the following screen shot:
Now that you have a page that will query the Categories table and returns the result as XML data, you can use the XmlQuery statement to consume this data.
In order to complete the query you will need to add a list statement to retrieve data from a SharePoint list and add a SqlQuery statement for joining both the SharePoint list and the SQL data retrieved using a XmlQuery statement:
Run the query and you get your data connected together:
The XmlQuery statement is very flexible and you can use it to build reports that join SharePoint data with any kind of data as long as you can write a simple program to get the data you would like to join.