Enesys RS Data Extension - Query Construction
In this article you will be presented with the overall concept of query construction and
design with Enesys RS Data Extension.
Enesys RS Data Extension is a custom data extension that makes it
possible to report over SharePoint lists by using Microsoft Reporting Services. Whatever
the data source you are using (SQL Server, XML, Enesys RS Data Extension, …), you will
always have the same approach for building and designing a report. Only the query string
will change depending on the data source you are using. Therefore, even if you have a
strong background in building reports using Microsoft report’s designer, you will need to
understand the specifics of designing a query using our Extension.
This is exactly the intent of this article. Note that we won’t go into the details of
building and designing a report. We have published on our web site a screen cast that
shows how to build a report from scratch for those who are interested. We won’t go either
into the details of the various features we are providing for dealing with specific
SharePoint features like multiple values columns, lookup columns, recurring events,
folders and so on. Our goal here is to provide you with a good understanding of the
overall query concepts.
Enesys RS Data Extension lets you retrieve, join, merge and aggregate data from
SharePoint lists using a specific query language based on XML. Because we are providing a
Visual Query Designer, you will certainly not deal with the query syntax itself. This is
why we will explain the query design concepts through the use of the Visual Query
Through the course of this article we will refer to a simple query that joins two
SharePoint lists (Orders and Customers) into a single dataset as shown in the following
Reporting Services Datasets
Though we decided not to go through Microsoft Reporting Services details, it is important
to provide some information about a few important concepts.
When building a report in reporting services, you will create at least one dataset that
points to a specific data source (SQL Server, Oracle, Enesys Data Extension, ...).
Data source types
A dataset definition contains the fields, from a specific data source, that you are
expecting to use in your report. It also contains the query used to retrieve data from
your data source.
The query language you will use depends on the type of data source you are using. For
reporting over SQL Server tables, you will use a SQL Server data source, and will use SQL
Query language for retrieving data. For reporting over SharePoint lists using our data
extension, you will use a data source of type "Enesys SharePoint Data Extension
2008" (available when you have installed and configured our extension), and will use a
specific Query language for retrieving data from SharePoint lists.
Enesys RS Data Extension Query language syntax is based on XML. The syntax is simple and
straightforward. However, we are providing a Visual Query Designer that makes it
unnecessary to deal with the details of the syntax.
Enesys RS Data Extension queries
A query is composed of several statements. The following Query Designer screenshot shows
a query using 3 statements:
Query composed of 3 statements
Each statement executes a specific function depending on its type. For instance, the
"List" statement will retrieve data from a specific SharePoint list.
The “RollUpLists” statement will retrieves items in an entire site
collection. The "SqlQuery" statement makes it possible to manipulate the
resulting data from other statements using SQL SELECT syntax.
Statements are executed sequentially starting from the first statement. The resulting
data from each statement is stored in a temporary table so that it can be further
manipulated by a "SqlQuery" statement. Therefore, in order to join two
SharePoint lists, you will need to add a “List” statement for each list and a “SqlQuery”
statement that will let join both lists using SQL SELECT syntax.
In the Visual Query Designer, you can add a statement by right-clicking in the Query
Statements panel as shown in the following screen shot:
Adding a statement
Of course, each statement has its own options and properties available through query
designer interface. For instance, for a “list” statement, you may specify the desired
columns or you may decide to filter on a specific folder. We won’t go into the details
here but you can freely download the User Guide from our site for detailed
information. The following table provides a short description of the purpose of each
Table: Description of Enesys RS Data Extension statements.
Lets you retrieve data from a SharePoint list.
| Merge multiple SharePoint lists at once.
Lets you retrieve list’s items in an entire site collection.
Lets you use SQL Select syntax to merge, join, aggregate the data resulting from
any other statement.
Lets you retrieve XML data from any URL.
Various administrative statements lets you retrieve back end information about
As you can see, the sqlQuery statement is extremely important in the sense that is will
let you apply sophisticated manipulations to SharePoint data from multiple lists and
sites. Except for simple needs (or when you just need to roll up list items in an entire
site collection using rollUpLists statement) , your query will often be composed of
multiple statements with at least on “sqlQuery” statement that will let you
join/merge/aggregate the data resulting from other statements.
The following screen shot shows the “sqlQuery” statement that is used to join both Orders
and Customers SharePoint lists:
SqlQuery statement for joining Orders and Customers lists
Note that you can use “Orders” and “Customers” as if they were simple tables.
The approach we have taken with Enesys RS Data Extension makes it
possible to come up with sophisticated queries. You are not limited to a single “sqlQuery”
statement per query. You can join the result of a “sqlQuery” statement with the data
resulting from any other statement by using another “sqlQuery” statement. This specific
approach will let you solve most of business needs you are facing regarding SharePoint
data stored in multiple lists and sites.
In fact, Enesys RS Data Extension lets you build “kind of” small applications and deploy
them the easy way for immediate availability to the users.