Build powerful and complex queries using SQL syntax
The major new feature of Enesys RS Data Extension Version 3 is the inclusion of an SQL Engine for making it possible to manipulate SharePoint lists using SQL Select statements.
In version 2, operations over SharePoint lists were limited to some operations that were not very flexible.
In version 3, the ability to issue SQL Select statements opens up "Enesys RS Data Extension" for limitless manipulation of SharePoint lists as shown in the following query that will return the 5 best selling products for a specific year:
Recurring Events
Getting recurring events in version 2 involved writing some complicate CAML query.
In version 3, the availability of a new automatic mode makes retrieving recurring events for a given date range as easy as retrieving regular items.
Rolling up list's items over multiple lists
Due to the fact that SharePoint Web Services need credentials that have the specific SharePoint "Browse Directories" permission, Enesys RS Data Extension version 2 was not the perfect solution for rolling up items only available to the user running the report. For instance, users belonging to SharePoint "Reader" group do not have "Browse Directories" permission by default. Therefore, it would be necessary to add this permission to the "Reader" group for being able to roll up items for those users.
To overcome this limitation, ERSDE version 3 makes it possible to globally configure a special account with elevated privileges. Enesys RS Data Extension will only use this account for enumerating web sites and will retrieve lists items using user's credentials. For security reason, the global account information is stored in an encrypted form.
Support for multi-value parameters
Enesys RS Data Extension version 2 has no direct support for multi-value parameters even though a work around was available for using them indirectly.
Reporting Services Multi-value parameters are now directly supported by Enesys RS Data Extension. More specifically, you can use a multi-value parameter in the following context:
· SqlQuery statement IN clause:
· Pseudo Caml Query In operator (new in this version):
Enhanced CAML Query
In and NotIn operators
Enhancing direct support for multi-value parameters, we have extended CAML Query syntax in order to support "In" and "NotIn" operators.
Using “In” at the CAML Query level rather than relying on the equivalent SqlQuery statement improves performances considerably when using medium to large lists (by a factor of 2 to 10).
IfNot modifier
The new IfNot element acts as a modifier for the Eq operator in order to allow you to retrieve either all items or items filtered on a specific value.
The idea is to permit the “ALL” or “Some value” filtering scenario without relying on an additional SqlQuery statement and mostly, with much improved performance than the equivalent SqlQuery statement.
Considering the following report's parameter:
The following query will return only those items whose "Category" is equal to the parameter's value OR all items if the parameter's value is equal to "(All)".
Querying over multiple SharePoint Web Applications
Specifying a SharePoint server URL at the data source level limits an ERSDE query to a specific SharePoint Web application. It was already possible to specify an empty server URL in a data source and specify an absolute SharePoint site's URL instead of a relative site URL within a "list" statement. However, this approach was not working properly with multiList statements.
Enesys RS Data Extension version 3 officially supports this approach which works with "MultiList" statements as well.
Logging Framework
ERSDE version 2 does not implement any logging features. It completely relies on Reporting Services for displaying and possibly logging errors when they occur. Therefore, errors occurring on a report server would be mixed within all data logged by Reporting Services which is obviously not very practical.
By implementing a complete logging framework (NLog), not only ERSDE errors (with more details) can be logged in a separate file, but debug information can be logged as well and help tracing difficult to reproduce problems.
Visually build queries using our new Query Builder
Enesys RS Data Extension version 3 includes a new graphical designer for building queries visually.
With "Enesys SharePoint Query Builder", you do not need to know CAML Query syntax details for building the XML-based query that will be understood by "Enesys RS Data Extension". Just add a SharePoint list, select the needed column and add some conditions the easy way.
You can run the query within the builder for testing purposes. In the end, you can just copy and paste the automatically built XML query into your report designer:
Take a look at the available presentation videos of "Enesys SharePoint Query Builder":
Other features
Easier Installation and upgrade
Until now, Enesys RS Data Extension was distributed as a zip package. Though the configuration was not extremely difficult, the necessity to modify some configuration files could always lead to some typo and problems difficult to sort out.
Enesys RS Data Extension is now provided as an MSI package. As part of the installation, it will automatically configure your Business Intelligence Development Studio (both 2005 and 2008 version).
Retrieve all non hidden fields
It is now possible to specify a ‘*’ (star) within a fields element in order to automatically retrieve all SharePoint non hidden columns. For performance reasons, It is recommended to use this approach when you do not want to specify each column rather than leaving empty the fields element.
Miscellaneous
- webCulture attribute not necessary anymore when retrieving item's versions from sites with different regional settings.
- Much improved syntax validation and more meaningful error messages.
- ResultSet statement is not mandatory anymore (data returned by the latest statement will be used).
Build powerful and complex queries using SQL syntax
The major new feature of Enesys RS Data Extension Version 3 is the inclusion of an SQL Engine for making it possible to manipulate SharePoint lists using SQL Select statements.
In version 2, operations over SharePoint lists were limited to some operations that were not very flexible.
In version 3, the ability to issue SQL Select statements opens up "Enesys RS Data Extension" for limitless manipulation of SharePoint lists as shown in the following query that will return the 5 best selling products for a specific year:
Recurring Events
Getting recurring events in version 2 involved writing some complicate CAML query.
In version 3, the availability of a new automatic mode makes retrieving recurring events for a given date range as easy as retrieving regular items.
Rolling up list's items over multiple lists
Due to the fact that SharePoint Web Services need credentials that have the specific SharePoint "Browse Directories" permission, Enesys RS Data Extension version 2 was not the perfect solution for rolling up items only available to the user running the report. For instance, users belonging to SharePoint "Reader" group do not have "Browse Directories" permission by default. Therefore, it would be necessary to add this permission to the "Reader" group for being able to roll up items for those users.
To overcome this limitation, ERSDE version 3 makes it possible to globally configure a special account with elevated privileges. Enesys RS Data Extension will only use this account for enumerating web sites and will retrieve lists items using user's credentials. For security reason, the global account information is stored in an encrypted form.
Support for multi-value parameters
Enesys RS Data Extension version 2 has no direct support for multi-value parameters even though a work around was available for using them indirectly.
Reporting Services Multi-value parameters are now directly supported by Enesys RS Data Extension. More specifically, you can use a multi-value parameter in the following context:
· SqlQuery statement IN clause:
· Pseudo Caml Query In operator (new in this version):
Enhanced CAML Query
In and NotIn operators
Enhancing direct support for multi-value parameters, we have extended CAML Query syntax in order to support "In" and "NotIn" operators.
Using “In” at the CAML Query level rather than relying on the equivalent SqlQuery statement improves performances considerably when using medium to large lists (by a factor of 2 to 10).
IfNot modifier
The new IfNot element acts as a modifier for the Eq operator in order to allow you to retrieve either all items or items filtered on a specific value.
The idea is to permit the “ALL” or “Some value” filtering scenario without relying on an additional SqlQuery statement and mostly, with much improved performance than the equivalent SqlQuery statement.
Considering the following report's parameter:
The following query will return only those items whose "Category" is equal to the parameter's value OR all items if the parameter's value is equal to "(All)".
Querying over multiple SharePoint Web Applications
Specifying a SharePoint server URL at the data source level limits an ERSDE query to a specific SharePoint Web application. It was already possible to specify an empty server URL in a data source and specify an absolute SharePoint site's URL instead of a relative site URL within a "list" statement. However, this approach was not working properly with multiList statements.
Enesys RS Data Extension version 3 officially supports this approach which works with "MultiList" statements as well.
Logging Framework
ERSDE version 2 does not implement any logging features. It completely relies on Reporting Services for displaying and possibly logging errors when they occur. Therefore, errors occurring on a report server would be mixed within all data logged by Reporting Services which is obviously not very practical.
By implementing a complete logging framework (NLog), not only ERSDE errors (with more details) can be logged in a separate file, but debug information can be logged as well and help tracing difficult to reproduce problems.
Visually build queries using our new Query Builder
Enesys RS Data Extension version 3 includes a new graphical designer for building queries visually.
With "Enesys SharePoint Query Builder", you do not need to know CAML Query syntax details for building the XML-based query that will be understood by "Enesys RS Data Extension". Just add a SharePoint list, select the needed column and add some conditions the easy way.
You can run the query within the builder for testing purposes. In the end, you can just copy and paste the automatically built XML query into your report designer:
Take a look at the available presentation videos of "Enesys SharePoint Query Builder":
Other features
Easier Installation and upgrade
Until now, Enesys RS Data Extension was distributed as a zip package. Though the configuration was not extremely difficult, the necessity to modify some configuration files could always lead to some typo and problems difficult to sort out.
Enesys RS Data Extension is now provided as an MSI package. As part of the installation, it will automatically configure your Business Intelligence Development Studio (both 2005 and 2008 version).
Retrieve all non hidden fields
It is now possible to specify a ‘*’ (star) within a fields element in order to automatically retrieve all SharePoint non hidden columns. For performance reasons, It is recommended to use this approach when you do not want to specify each column rather than leaving empty the fields element.
Miscellaneous
- webCulture attribute not necessary anymore when retrieving item's versions from sites with different regional settings.
- Much improved syntax validation and more meaningful error messages.
- ResultSet statement is not mandatory anymore (data returned by the latest statement will be used).