1
1
The perfect solution for creating powerful yet inexpensive business solutions by integrating data from multiple SharePoint lists. Enesys RS Data Extension lets you leverage all the power of Microsoft SQL Server Reporting Services with your SharePoint Data.

Knowledge Base and Samples

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.

Introduction

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 Designer.

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 screen shot:

QueryConceptsMain

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 statement.

Table: Description of Enesys RS Data Extension statements.
Statement Description

list

Lets you retrieve data from a SharePoint list.

mergeLists

Merge multiple SharePoint lists at once.

rollUpLists

Lets you retrieve list’s items in an entire site collection.

sqlQuery

Lets you use SQL Select syntax to merge, join, aggregate the data resulting from any other statement.

xmlQuery

Lets you retrieve XML data from any URL.

Administrative statements

Various administrative statements lets you retrieve back end information about SharePoint.

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.

Conclusion

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.