We would like to share with you some options for future versions of Enesys RS Data Extension and get your feedback before deciding to include them in the coming versions.
SQL Query language
Enesys RS Data Extension query syntax currently provides some SQL-like statements that make it possible to apply operations to SharePoint lists data that have been retrieved using list or multiList statements. It's no secret that the operations are rather limited in comparison of SQL language features available when dealing with data tables.
Rather than spending a lot of development effort trying to mimic more sophisticated sql-like operations, our idea is to include some SQL engine into our product and to interface it to SharePoint lists retrieved using list or multiList statements.
For those who are already familiar with ERSDE Queries, here is how an ERSDE Query could look like with SQL Query language added feature:
<?xml version="1.0" encoding="utf-8" ?>
<root xmlns="http://enesyssoftware.com/schemas">
<list title="Products" relativeSiteUrl="/sites/demo/" tableName="Products">
<fields>Title, Category</fields>
<query></query>
</list>
<list title="Product categories" relativeSiteUrl="/sites/demo/" tableName="ProductCategories">
<fields>Title, Description</fields>
<query></query>
</list>
<list title="Orders" relativeSiteUrl="/sites/demo/" tableName="Orders">
<fields>OrderID,OrderDate,Employee</fields>
<query></query>
</list>
<list title="Order Details" relativeSiteUrl="/sites/demo/" tableName="OrderDetails">
<fields>Product, Order_x0020_ID, UnitPrice, Quantity, Discount</fields>
<query></query>
</list>
<sqlQuery dstTableName="testQuery">
SELECT OrderDetails.Product, Products.Category, ProductCategories.Description,
Orders.OrderID, OrderDate, UnitPrice, Quantity, UnitPrice*Quantity As Total
FROM Products INNER JOIN ProductCategories ON Products.Category = ProductCategories.Title
INNER JOIN OrderDetails ON Products.Title=OrderDetails.Product
INNER JOIN Orders ON OrderDetails.Order_x0020_ID=Orders.OrderID
ORDER BY Product
</sqlQuery>
<resultSet>testQuery</resultSet>
</root>
As you can notice, list statements are still used to retrieve data from SharePoint using all the available options provided by Enesys RS Data Extension. A new sqlQuery statement would let you apply a SQL SELECT statement using the SharePoint lists retrieved using list statements as if they were SQL tables. This would obviously open quite some possibilities and make it easier as most people dealing with reports are fluent in SQL.
We have already made some basic tests with SQLite which have been promising.
If we were to provide this feature within Enesys RS Data Extension, we would preferably completely remove support for sqlOp statements. Of course, because many reports have already been built using sqlOp statements (and we have lots of them as well), we would give a new name to the new data extension so that it can be run side by side with the current one (that's what we did when we released the 2.0 version).
We could also decide to keep sqlOp statements for compatibility purpose though it would not be possible to build such statement with our coming query builder.
We are very interested in getting your feedback regarding this possible major change.
Internal and display name
SharePoint list columns have both internal names and display names. When creating a column in a SharePoint list, one will enter some column name. Under the hood, the column internal name is assigned the column display name except for spaces and accentuated characters which are replaced by an hexa value surrounded by underline chars (because of this, we took the habit to first specify a column name without any spaces or special chars on column creation and to change it as soon). From that point, the column internal name will not change though a user with the necessary permissions may change the column (display) name.
Enesys RS Data Extension let's you use either SharePoint columns internal names or display names when building a query by setting useDisplayName attribute to true or false. Using internal names has the advantage of not breaking your report if a column is being renamed. On the other hand, manipulating internal names within the report layout is not particularly great if the internal name has lots of hexa values or even worse if the internal name meaning has nothing to do anymore with the column value which may happen if you reuse some column for holding completely different values than what it was initially intended for.
In order to overcome this limitation we are proposing to extend the syntax of the field element in order to specify your own display name. Here is a sample query using this possible approach:
<?xml version="1.0" encoding="utf-8" ?>
<root xmlns="http://enesyssoftware.com/schemas">
<list title="Products" relativeSiteUrl="/sites/demo/" tableName="Products">
<fields>Product=Product_x0020_Name, Category=Cat_x0020_ID, Supplier</fields>
<query></query>
</list>
<resultSet>Products</resultSet>
</root>
Each field within the fields element is specified using the following syntax: <YourOwnDisplayName>=<SharePoint column name> where <SharePoint column name> could be the internal or display name depending on useDisplayName attribute. Specifying your own display name and setting useDisplayName attribute to true doesn’t make a lot of sense but would still be a possible option.