We are pleased to announce the release of Enesys RS Data Extension 3.8. This new version includes major new features like SQL Server 2012 compatibility, support for Choice Columns, Merge a set of lists, Visual Query designer improvements, and much more …
Simpler Approach for Distinct Field Values
Retrieveing distinct field values is extremely useful when using report parameters. With Enesys RS Data Extension version 3.7 and earlier, you could use an additional "SqlQuery" statement to get distinct values from a SharePoint list.
Because this approach is so common, Enesys RS Data Extension 3.8 makes it possible to get distinct values from a list without having to use an additional SqlQuery statement. You can specify that you want distinct values directly within the “List” properties as shown in the following screen shot:
You can specify that you want distinct field values at the list level.
Retrieving Choice Values from a Choice Column
When using a report parameter to filter a list based on the values of a choice column (ie: status, priority,…), you may want to consider two approaches:
- You will only display values that are already in use. Say you are filtering issues based on a specific “Priority” that the user can select when running the report. Even though the “Priority” is a SharePoint choice column that contains the following 3 values (High, Normal and low), if you don’t have any issues with a “low” priority value, you may not want to display “Low” as a possible choice for filtering out issues.
With that approach, you consider that it is not necessary to bother the user with some value that does not return any items.
- You will display all possible values regardless of its selection will return any values. If we take the previous example, you want to display “low” as a possible choice for filtering issues even if you don’t have any issues with low priority.
With that approach, you are considering that displaying all the possible values and not returning any items for a specific value brings some meaningful information to the user.
Both approaches are perfectly legitimate. It all depend on the business case.
In version 3.7 and earlier of Enesys RS Data Extension, only the first scenario can be implemented. To implement this approach, you will select distinct values from the specific field you want to filter and use them for the report parameter values.
Enesys RS Data Extension 3.8 offers the possibility to retrieve the choice values from a SharePoint choice columns. With this new feature, you can therefore implement the second scenario described earlier and present all the possible values for the column you are filtering as part of a report parameter.
Retrieving choices from a column of type "Choice”
Enesys RS Data Extension 3.8 provides a new statement for retrieving “choices” from list or site columns of type choice.
When adding “Column Choices” statement to your query, you are proposed to either retrieve choices from a specific list column or from a column defined at the site level:
Adding Column Choices statement.
When selecting the option, “Retrieve choices from a list column”, you get the usual dialog box where you can select the desired list:
When selecting the option, “Retrieve choices from a list column”, you get the usual dialog box where you can select the desired list.
When selecting a list or a site, you will only be proposed the choice columns available in the list or the site. As part of the statement, you can only select a single column to retrieve choices values from (it would not make many sense to return value from multiple choices columns in a single result set). Executing the statement will return choices associated with the column.
Selecting Choice Column.
SQL Server Reporting Services 2012 compatibility
SQL Server Reporting Services 2012 brings quite a few changes:
- Business Intelligence Development Studio (BIDS) is now called SQL Server Data Tools (SSDT) and runs under visual studio 2010 shell. It doesn’t mean you need visual studio. The visual studio 2010 core interface will be automatically installed when installing SSDT.
- SharePoint integration in now implemented as a SharePoint 2010 Shared Service.
Enesys RS Data Extension 3.8 addresses the various configuration changes to work with Reporting Services 2012.
When installed in SharePoint Integration mode, The “Enesys RS Server Configuration” tool reflects the new “SharePoint Service Application” as shown in the following screen-shot:
SSRS as a SharePoint Service Application within Enesys RS Server Configuration.
“Enesys RS Client Configuration” tool reflects the new Microsoft terminology when Enesys RS Data Extension is installed along SQL Server Data Tools:
SQL Server Data Tool.
Within SSDT 2012 (Visual Studio 2010 shell), you get the familiar tools for building reports and sophisticated queries:
Building reports with SQL Server Data Tools 2012.
Merging Multiple Lists
Enesys RS Data Extension 3.8 provides additional flexibility and power for merging multiple lists with the new “ListsMerge” statement.
The new “ListsMerge” statement lets you merge a set of lists specified by another statement. You will configure “ListsMerge” statement so that the lists to be merged are specified in another statement which means that you can store the lists in whatever way you want as long as you can use one of the statement provided by Enesys RS Data Extension to retrieve them.
For instance, you may want to use a SharePoint list to maintain the lists/libraries that you would like to merge. Using this approach, adding or removing a list from your report is just a matter of adding or deleting an item in your SharePoint list without having to change your report.
In order to merge a set of lists, you need to specify a statement that returns a set of lists. You will also need to specify an existing list as a reference so that the schema can be inferred at design time to let you choose the necessary fields and apply the desired filters:
Merging a set of Lists with the ListsMerge statement.
The source statement needs to have two columns that provides list name and site URL. The designer will ensure those columns are available before you can create the statement:
Merging a set of Lists - source statement validation.
Two additional columns are provided to let you know to which site and list belongs an item. This can be useful for grouping the resulting data:
Merging a set of Lists - source site and list.
Here is an example SharePoint list that can be used as a source for merging multiple lists from multiple sites. Once your report is built upon this list for merging data, you don't need to change your report to add or remove a list:
Merging a set of Lists - sample SharePoint list that can be used as a source for merging a set of lists.
Visual Query Designer Improvements
As with most releases, Enesys RS Data Extension 3.8 brings various user interface improvements to make your life easier when designing queries.
Filtering using parameters
In many cases you will want to use report parameters when filtering SharePoint lists data. Until now, it was necessary to use a very specific syntax (the name of a parameter surrounded by @ and !), to indicate that you are referring to a report parameter.
That’s not really difficult but not always intuitive especially when discovering the product.
Therefore, with Enesys RS Data Extension 3.8, a "Parameter…" option is available as shown in the following screen shot:
Filtering using parameters.
When selecting the “Parameter…” option, a dialog box lets you either select an existing parameter or enter a new parameter name:
Selecting or creating a parameter.
Filtering on choice columns
When filtering on SharePoint columns of type Choice, choices values are displayed and available for selection as shown in the following screen shot:
Filtering on Choice Columns.
This is also available for some specific columns like the workflow status:
Filtering on Workflow status.
Note that the workflow status value is of type integer. The label displayed next to the integer value is for visual help only.
In previous versions of Enesys RS Data Extension, renaming a statement could lead to an overall invalid query when the statement was used, for instance, in a SqlQuery statement for joining multiple lists. Indeed, in that case, the SQL Select statement is not updated to reflect the new name of the statement.
If you look at the screen shot below, the query includes a “SqlQuery” statement that joins both “Issues1” and “Issues” statements. With versions prior to 3.8, renaming “Issues1” will not update the “SELECT” clause. It will be necessary to make the modification manually.
With Enesys RS Data Extension 3.8, renaming a statement will automatically update the overall query to reflect the change. No more manual update is necessary.
Enesys RS Data Extension offers various specialized Statements for retrieving data from SharePoint lists, InfoPath libraries, SQL Server, Oracle, and much more.
You can mix and match the data in those statements by using a “SqlQuery” statement that lets you use SQL Select syntax.
At time, when creating sophisticated queries, things can get complex. Intellisense features made available a few versions ago were a great step in the right direction. In version 3.7, we added visual information by displaying - in the query statements panel (the yellow area in the image below) - the fields associated with a specific statement. This was however limited to a few set of statements.
With Enesys RS Data Extension 3.8 we have generalized this approach to all statements. You can therefore have an overall view of your statements and fields which greatly helps when building complex queries:
Statement fields displayed in the statement panel.
Improved XmlQuery Statement
The XmlQuery statement lets you retrieve and manipulate XML data from an URL you specify. With previous versions of Enesys RS Data Extension, you were bound to use an XPath expression to specify the data you wanted to retrieve from your XML data. Though this may be satisfying in many cases, you may need to retrieve data from a more complex XML structure that would not translate using a single XPath expression.
Capitalizing upon the recent developments on the InfoPath statement, the XmlQuery statement now offers a similar approach for selecting data (ElementPath) in addition to the XPath expression that is still supported.
When creating a new XmlQuery statement, both options are proposed as shown in the following screen shot:
Support for XPath and ElementPath queries.
When using the new ElementPath approach, you will be able to specify the data you need with more precision than using the XPath approach.
Moreover you get you get the same designer control that is available with InfoPath statement:
XML ElementPath Query Designer.
The XPath approach is the only available method with version 3.7 and earlier. It lets you write an XPath expression to specify the data you would like to retrieve from your XML data. Though it has some limitations when you want to deal with retrieving data at various level of your XML hierarchy, it can also be quite helpful when you want to retrieve nodes whatever their position in your XML schema.
Enesys RS Data Extension 3.8 brings two improvements to the XPath approach.
XML Schema viewer
You can view the XML schema while writing your XPath expression:
XML Schema is displayed as an helper for building XPath expression.
When using an XPath expression, you don’t have much control on the elements/attributes returned by the expression. Say you use an expression like “//Item”, you would end up having all the child element of “Item” being converted to fields in your query.
You can now select the fields to include:
Select Specific fields to further refine your Xpath query expression.
Run Queries up to 50 times faster with Statement Filtering
Th new statement filtering feature let's you filter a SharePoint list using data from another statement, be it another SharePoint list or any other statement that gets date from SQL Server, Oracle...
When joining multiple SharePoint lists using an Enesys RS Data Extension SqlQuery statement, the "join" operation takes place after lists items are retrieved. Therefore you may retrieve from your SharePoint server unecessary items that will be discarderd once doing the "join" operation. Statement Filtering will allow you to use the resulting data of another statement in order to filter at the SharePoint list level (as opposed to filtering once the data is retrieved). This approach can make a huge difference in scenarios where you are manipulating large lists and your join operations will only return a few items.
This feature is better explained by using a concrete sample. Please read the following article for a detailed scenario that explains why and how to use this feature.
- Client and Server Configuration tool now adds links to Log folder for easier access.
- Server Configuration tool now supports specifying a default value for the enableObjectModel data source property.
- The Server Configuration tool doesn’t reset IIS when Enesys RS Data Extension is configured on Reporting Services 2005. A restart of the Report Server service is done instead.
- Improved "In" performances when using SharePoint 2010 by converting the pseudo-CAML element into the new SharePoint 2010 "In" CAML element.
- A new IncludesAll pseudo-CAML element allows filtering a multi-values column to ensure that returned items contain all the given values.