Enesys RS Data Extension will be released by the end of September. Amongst other things, you will certainly appreciate the significant performance improvement when rolling up list items in a site collection.
Enesys RS Data Extension V3.2 will include the following new features:
Data Source variables
Note that the following explanation will certainly only make sense for those having already use our product.
It is now possible to define variables at the Data Source level in the connection string.
Datasource level variable can be used to specify a site’s relative URL (relativeSiteUrl attribute) and list’s title (Title attribute) within a query.
What problem does it solve?
The idea behind this feature is that you can have a complete set of reports that rely on a variable rather than on the literal relative site URL. If you need to run your reports on another server that would not use the same site hierarchy, you just have to use another data source with the variable properly configured for your different server without having to change the various URL in each statement and in each report.
Until now, the only approaching solution was to use a report’s parameter for the relative site URL. This approach has however several drawbacks:
- A report’s parameter is only valid at the report level. It is therefore necessary to create such a parameter for each report.
- A report’s parameter value is only know at run time. However, in order to discover the schema of your query when designing a report, it is necessary to know the relative site URL of the lists you are using. The solution is to specify the value of the URL when the query is executed the first time. It has however to be done each time you reload BIDS to re work your report and moreover, when you have some error on your report while working on it, it can invalidate the parameters and necessitate that you click refresh in order to parse parameters again. This can be more confusing than necessary when in the end a report’s parameter is only used has a mean to move reports more easily.
To give an example, we are providing on our site a complete reporting solution containing 9 fully working reports that run over Microsoft’s “Sales Lead Pipeline” sample site application template. In order to minimize the configuration effort necessary to run the reports over your own Sales Lead Pipeline site that is certainly not installed using the same URL as ours, we are using report’s parameters to specify the relative site URL of the lists used in the report’s queries. When installing our reporting solution, it is necessary to go through each report and to modify the value of the parameter for each of the nine reports.
With the use of a data source variable, a simple modification of the value of this variable within the shared data source will be the only modification necessary to run all reports over your own site.
Enesys Query Builder has been updated to be compatible with data source level variable and let you create/use them in an easy way:
Data Source variables are not a complete replacement for report’s parameters used in relative site URL. Using report’s parameter in relative site URL is still a legitimate approach if the site’s need to be specified at run time.
Site filtering when using RollUpLists statement:
It is now possible to filter the sites that will be used to roll up list’s data within a site collection.
You can specify the sites using literal values or a report’s parameter.
This new feature will come in handy for those who have one site per project and that only want to rollup data for specific projects.
Until now the only solution to filter out specific sites was to use a SqlQuery statement afterward. By using the this new feature you will experience a huge performance improvement when only a few sites need to be roll up.
Our tests show a 500% performance improvement when rolling up items from 5 sites out of 100 using the new filtering approach.
Performance improvement when Rolling up lists
In order to improve performance when rolling up numerous sites, we are using a new approach for enumerating sites in a site collection. This new approach will take only one call to get all sites in the site collection instead of one call per site for enumerating sub-sites. In order to benefit from this approach, you will need to specify a SPLogin account that has admin permissions over the entire site collection. This approach will not make much difference with a few sites. However, for 50 sites and more, you should notice an interesting performance improvement.
Our tests show an average 30% improvement when rolling up items within a site collection having 100 sites.
We have also re-factored the whole approach for merging lists that will increase performance by an average or 15% when rolling up large lists.
Support for custom Lookup fields
We have added complete support for custom Lookup fields that extend SharePoint SPFieldLookup field. Both single and multiple values Lookup fields are supported.
Query Builder
Query builder is now available in English and French (more languages will come soon). You can easily change the User Interface language by selecting “Language” in the “Help” menu.
A new Grid view is available for previewing the result of your query. This Grid view provides advanced features like sorting, grouping, filtering, …
The data resulting from your query can now be exported to Excel directly. Combined with the advanced features of the new Grid, this can make Enesys SharePoint Query Builder a stand alone working solution in some scenarios.
When specifying a query condition for a list, it is now possible to select between hidden and non hidden SharePoint columns. Considering you will filter on non-hidden SharePoint columns, this new approach should make the selection more comfortable. In previous version, all columns were displayed in the same window.
Others:
Any columns can be selected for sorting in the Columns/Filtering/Sorting tab. In previous versions, only selected columns could be used for sorting.
Support for parameters in relativeSiteUrl attribute.
Support for Data Source variables.