Prologika (Teo Lachev's Weblog) : SQL Server 2008 R2

Fixing Indicator Images

tlachev — Fri, 21 May 2010 22:20:00 GMT

Indicators, a new feature of Reporting Services 2008 R2, let you show images for state values on your reports and avoid using custom images and expressions. Sean Boon's recent blog shows you how to scale the indicator images. One annoying issue business users reported when working with indicators is that the indicator image stretches if the text grows and spills to the next row. For example, on the report below the second row spans two lines and its indicator images are stretched vertically.

Unfortunately, the indicator region doesn't have a property to "fix" the image size. The only workaround for now is to enclose the entire indicator region in a rectangle: a kludgy hack especially for business users.

Microsoft SQL Server 2008 R2 Feature Pack Available

tlachev — Wed, 12 May 2010 12:09:52 GMT

Microsoft released the Microsoft SQL Server 2008 R2 Feature Pack. Among other things, it includes Report Builder 3.0, Reporting Services SharePoint Add-in, and PowerPivot for Excel.

Atlanta.MDF Presentation

tlachev — Tue, 11 May 2010 12:28:41 GMT

My presentation "What's New in Reporting Services 2008 R2" for the local Atlanta.MDF group went well last night. We had some 80 people attending and I had some great questions. Too bad I couldn't beat my previous attendance record. I guess rain and traffic were deterrent factors. Thanks for everyone who attended! I posted the presentation materials on my website. It should be available shortly on the Atlanta.MDF site as well.

Synchronizing Reporting Services Groups

tlachev — Mon, 03 May 2010 23:41:00 GMT

One new R2 feature that I discovered while reading the Reporting Services Recipes book, is group data synchronization. Consider the following report (download the two sample reports here):

In this report, the matrix region is nested inside a list region. The list region pages (groups) on product while the nested matrx region displays the sales for the current product grouped by region and year. As you can see, Mountain-200 Sliver, 42 has data for years 2003 and 2004 but if you move to the next page, you will see that Montain-200 Silver, 46 has sales for 2002, 2003, and 2004.

What if you want to synchronize the instances of the matrix region to return the same number of columns, which in this case would it all available years in the dataset? In R2, this takes a few clicks.

In design mode, click anywhere inside the nested matrix region so the Groups pane shows its groups.
Select the CalendarYear column group on which the matrix region pivots.
Expand the Group section in the Properties window and enter the name of the outer region in the DomainScope property. In this case, the outer region is the list region whose name is List.

Now, when you run the report, all instances of the matrix region are synchronized and have the same number of columns irrespective of the fact that they might not have data for given years.

DomainScope is similar to expression scope but not the same. DomainScope can be set on a group leaf member only. So, if the matrix region was grouping on years and quarters, you can synchronize quarters only in which case each year would show four quarters. You cannot synchronize the Year level because it's a parent group. In addition, the domain scope can be an outer group or region. Unlike expression scopes, you cannot synchronize on a dataset.

Group synchronization comes also handy when you work with sparklines. For example, the following report includes an Over Time column that shows a bar sparkline for product category sales over years.

As you can see, the Caps category has sales for four years (The AventureWorksDW2008 database has data for years 2001-2004), while Bib-Shorts category has data for two years. However, the sparklines groups are not aligned. For instance, you can't tell which two years Bib-Shorts have sales for. To fix this:

Double-click the sparkline region to show the Chart Data window.
Click the CalendarYear group and set the DomainScope property to the name of the containing tablix region, Tablix1 in this case.

Now, when you run the report, you'll see that the sparkline group ordinal position reflects the year. Thus, knowing the range of available years (2001-2004) which for clarity you can display on the report, e.g. as a subtitle, Bib-Shorts have data for years 2002 and 2003, while Bike Racks have data for 2003 and 2004.

Reporting From OData Services

tlachev — Mon, 26 Apr 2010 02:57:00 GMT

Open Data Protocol (OData), previously known as ADO.NET Data Services, is quickly gaining popularity as a web protocol for querying and updating data. A few Microsoft products already expose their functionality as OData services, including SharePoint 2010, SQL Azure, and Reporting Services (see my blog about reports as data feeds). Therefore, chances are that you may need to report off OData services. As it stands, Reporting Services doesn't include a native support for OData but the XML Data Provider, which has been around since version 2005, may get the job done.

The OData report demonstrates how you can use the XML Data Provider to query OData services. It has two datasets. The Customers dataset retrieves data from the sample Northwind OData service. The CompanySales dataset queries the Adventure Works Company Sales report as a data feed.

Northwind OData Service

The Northwind datasets shows a list of Northwind customers. I implemented the report as follows.

Set up the Northwind data source that uses the XML Data Provider and whose connection string points to the Northwind Customers service.
http://services.odata.org/Northwind/Northwind.svc/Customers

As you've probably guessed it, you need to set up a data source for each OData service you want to query. If you open the above link in IE with the feed view turned off (Internet Options, Content tab, Settings, uncheck Turn On Feed Reading View), you will see the output of the service.

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>

- <feed xml:base="http://services.odata.org/Northwind/Northwind.svc/"
xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"
xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">

<title type="text">Customers</title>

<id>http://services.odata.org/Northwind/Northwind.svc/Customers</id>

- <entry>

<id>http://services.odata.org/Northwind/Northwind.svc/Customers('ALFKI')</id>

- <author>

</author>

- <content type="application/xml">

- <m:properties>

<d:CustomerID>ALFKI</d:CustomerID>

<d:CompanyName>Alfreds Futterkiste</d:CompanyName>

<d:ContactName>Maria Anders</d:ContactName>

<d:ContactTitle>Sales Representative</d:ContactTitle>

<d:Address>Obere Str. 57</d:Address>

<d:City>Berlin</d:City>

<d:Region m:null="true" />

<d:PostalCode>12209</d:PostalCode>

<d:Country>Germany</d:Country>

<d:Phone>030-0074321</d:Phone>

<d:Fax>030-0076545</d:Fax>

</m:properties>

</content>

Set up a dataset query.

<Query>

    <ElementPath IgnoreNamespaces="true">

        feed{}/entry{}/content{}/properties

    </ElementPath>

</Query>

The IgnoreNamespaces attribute instructs the provider to ignore the XML namespaces. The ElementPath text specifies a path to the element whose nodes you want to display. In this case, the element is properties which is the element that contains the interesting OData stuff. The empty curly braces for the preceding nodes are used to ignore information from the preceding nodes in the path.

CompanySales

Since starting with R2, reports are available as data feeds, you may also implement a quick and easy way to display data from one report in another. In this blog, I provided more details about the query syntax. In the case of the Company Sales, the data source connection string is as follows:

http://localhost/ReportServerR2?/Adventure Works Sample Reports/Company Sales 2008&ShowDescription=False&StartDate=01/01/2002 00:00:00&EndDate=12/31/2003 00:00:00&rs:ParameterLanguage=&rs:Command=Render&rs:Format=ATOM&rc:DataFeed=xAx0x2xCx0xMx0

The dataset query is the same because all OData services return data in the same format.

Moving forward, it will be nice if a future release includes a native OData provider that handles parameters and supports methods, as the XML Data Provider does for SOAP web methods, so you don't have to set up a data source for each OData service.

SQL Server 2008 R2 Released to Manufacturing

tlachev — Wed, 21 Apr 2010 15:45:00 GMT

Robert Bruckner announced that SQL Server 2008 R2 was released to manufacturing today. Robert summarized the most important most features. I covered them in my blog as well. I'll update this blog once I find out when R2 will be available on MSDN.

UPDATE

According to this Microsoft blog, R2 will be available for MSDN subscribers on May 3^rd.

Feeding Reports by Code

tlachev — Tue, 30 Mar 2010 19:01:00 GMT

In a previous blog, I introduced one of the new SSRS 2008 R2 feature: reports as data feeds. As I said, the R2 release will include a new Atom Data Feed renderer to produce an Atom service document (*.atomsvc) which defines a feed(s) per data region. I also said that the main scenario for report feeds was to let the PowerPivot add-in for Excel (previously known as Gemini) consume report data. Recently, I've watched the interesting Pablo Castro's "ADO.NET Data Services: What's new with the RESTful data services framework" presentation which got me inspired to find a way to consume a report data feed programmatically outside PowerPivot. Since the Atom feed format is pre-defined, a custom application could benefit from this scenario to retrieve and manipulate the report data. In this case, an atom feed may be preferable than other format, such as CSV, because data is exposed as properties instead of columns.

So, I embark on a journey to write a small Silverlight app, RSFeedReader, that reads and displays a report feed. You will need Visual Studio 2010 to open the RSFeedReader source code. Before running the application, you may need to deploy the clientaccesspolicy.xml file included in the source to the IIS root, such as c:\inetpub\wwwroot or C:\inetpub\wwwroot\wss\VirtualDirectories\80 if you have SharePoint installed on port 80, to grant the application rights to connect to Reporting Services. If you don't, Silverlight would treat the application URL, which runs under the local ASP.NET IIS server, and Report Server URL as two different domains and it will throw a security exception.

You can enter a report feed in the textbox and hit Fetch. The data feed results are shown in the grid. Similar to PowerPivot, each row represents a row in the report data region but the properties are stacked vertically instead of horizontally. The first column shows the entry identifier which identifies the row in the data region and the second column shows the values in that row. The Generating Data Feeds from Reports BOL topic covers the technical details of report feeds pretty well. Basically, the feed URL must include a unique identifier which you obtain from the Atom service document when you export the report to the Atom Data Feed format. The default feed URL points the matrix region in the Company Sales report although you can't tell this from the feed URL. Again, you need to get the DataFeed identifier for the region from service document after exporting to Atom. If you request the report data feed in the browser, you will see the resulting XML. The most interesting information is included in the m:properties element.

Unlike SharePoint, Reporting Services doesn't provide a WCF endpoint, such as ListData.svc, so you can't generate a Visual Studio proxy (unless you do it manually). Instead, RsFeedReader, uses WebClient to submit the URL request to Reporting Services to obtain the feed.

protected void LoadFeed(string uri)

{

WebClient wc = new WebClient();

wc.OpenReadCompleted += new OpenReadCompletedEventHandler(wc_OpenReadCompleted);

Uri feedUri = new Uri(uri, UriKind.Absolute);

wc.OpenReadAsync(feedUri);

}

private void wc_OpenReadCompleted(object sender, OpenReadCompletedEventArgs e)

{

if (e.Error != null)

{

txtFeedLoc.Text = "Error load report data feed. " + e.Error.Message;

return;

}

using (Stream s = e.Result)

{

SyndicationFeed feed;

using (XmlReader reader = XmlReader.Create(s))

{

// read the feed

feed = SyndicationFeed.Load(reader);

var posts = from item in feed.Items

select new FeedEntry(item.Id, (XmlSyndicationContent)item.Content);

// show feeds in grid

dGrid.ItemsSource = posts;

dGrid.Visibility = Visibility.Visible;

}

Once the asynchronous call completes, RsFeedReader loops through the feed entry elements and populates a collection of FeedEntry objects which I bind to the grid. The FeedEntry class parses the content element of the entry (where the properties are located) and exposes them as a ContentProperties object (not shown) which I bind to the nested grid in the LoadingRow event of the outer grid.

It probably won't be long before various custom applications start popping up that lets end users subscribe to feeds, such as to let the end user get notified when report content changes.

Product Release Galore

tlachev — Wed, 17 Mar 2010 16:17:08 GMT

Microsoft announced release dates for SQL Server R2, SharePoint 2010, Office 2010, and Visual Studio 2010.

According to the Microsoft SQL Server Team Blog: SQL Server 2008 R2 (aka SQL 2010) will be listed on Microsoft's May price list, and will be available by May 2010
According the Microsoft SharePoint Team Blog: SharePoint 2010 and Office 2010 will be officially launched on May 12, 2010 with an RTM date of April 2010
Visual Studio 2010's release date was pushed to April 12, 2010 due to some performance issues according to Developer Division's Marketing and Communications Manager Rob Caron's blog.

2010 is sure a busy year for BI!

BIDS Integration Story in R2 – The Good, the Bad, and the Ugly

tlachev — Wed, 17 Mar 2010 01:20:00 GMT

There was a huge discussion thread about the BIDS-Visual Studio integration story in the SQL Server 2008 timeframe where customers complained that BIDS got married with Visual Studio. This required switching both BI and code projects at the same if you want to have a solution with both project types. What has changed in R2?

The good – Report Server projects in BIDS R2 can target either SQL Server 2008 or R2, as explained in this blog. There is no change to Analysis Services projects. If you have a Report Server project with RDL 2008 files, the BIDS Report Designer won't upgrade the report definitions. However, the moment you add a R2-specific feature, such as the new map region, BIDS will upgrade the report definition to R2 RDL. Therefore, if you want to play with R2 on your machine while the rest of the team is still on 2008 and you are careful, you can install R2 and upgrade BIDS without affecting the rest.

The bad – The R2 Report Server project format has changed to accommodate the new project deployment settings which are explained in the above-mentioned blog. The first time you open a Report Server project in BIDS R2, you will be asked to upgrade the project (not the RDLs). But what if you are forging new frontiers in your company (aka not following corporate standards) and decide to move to R2 while your peers are still on SQL 2008 assuming you use source control and share project files? No worries. BIDS 2008 will automatically strip out the elements in the report project file it doesn't recognize. But when they save the project the project file will revert to the 2008 format, so you'll get prompted again to upgrade the next time you load the project in BIDS. To solve this problem, I use a local project file.

The ugly – R2 doesn't integrate with Visual Studio 2010 as it targets VS 2008 only. And since BI project types are part of BIDS and not Visual Studio, VS 2010 users won't get the BI project templates either. In other words, you must have BIDS R2 and Visual Studio 2010 SxS. Consequently, you won't be able to have a solution that spans both Visual Studio 2010 code projects and BI projects. This is like having one TV to watch US channels and a second TV to watch Euro.

It will be nice if one day the SQL Server setup program detects the Visual Studio version installed and ask you which version you want to integrate with. But given that Visual Studio project formats change between releases this may remain a wishful thinking.

Where is RDLOM in R2?

tlachev — Tue, 09 Mar 2010 02:17:00 GMT

After covering the cool new stuff in R2, it's time to talk about the uncool. One R2 change that really vexes me is RDLOM. RDLOM, short for Report Definition Language Object Model, lets developers program RDL in object-oriented way as opposed to using XML. RDLOM could save you a lot of effort when you have to meet more advanced requirements that involve pre-processing RDL. For example, in one of my projects we had to implement a wizard that would walk business users through steps to generate an ad-hoc report. Behind the scenes, the wizard would generate the report definition by using a custom RDLOM which we developed since back then Microsoft didn't have their own. I included a scaled-down version of our implementation in my TechEd 2007 code sample. Although it took us a while to get our RDLOM implemented, it turned out to be invaluable down the road. Since we didn't have to use XML API to manipulate RDL, it simplified programming RDL. For another project, we used RDLOM to let the customer specify which sections they want to see on the report. Once the selection was made, we would remove the unwanted sections from a report template. The list of real-life scenarios that could take advantage of RDLOM goes on …

Apparently, I wasn't the only one bugging Microsoft about RDLOM that ships with SSRS and they promised that this will happen at TechED 2007 USA. Sure enough, SSRS 2008 brought us an unsupported version which I blogged about and covered more extensively in my Applied SSRS 2008 book. Although unsupported, the SSRS 2008 RDLOM was very useful. It was implemented in a single assembly (Microsoft.ReportingServices.RdlObjectModel.dll), exposed all RDL elements as objects, and supported serializing and deserializing RDL.

Alas, this will come to pass with R2. Upgrading to R2, you'll find that the classes in Microsoft.ReportingServices.RdlObjectModel are marked as internal so you cannot use this assembly. Instead, RDLOM got moved into the Microsoft.ReportingServices.Designer.Controls.dll assembly. The good news is that there are rumors that RDLOM may get documented. Now the bad news:

Clearly, R2 RDLOM was not meant to be distributed. While you need to reference explicitly only Microsoft.ReportingServices.Designer.Controls and Microsoft.ReportingServices.RichText assemblies you'll find that they are dependent on other assemblies and you need to distribute a total of 25 assemblies resulting in a whopping 25 MB of referenced code. One way to avoid shipping so many assemblies to deploy your application in the BIDS or Report Builder 3.0 folders.

R2 RDLOM will be 32-bit only because BIDS and Report Builder are 32-bit only.
R2 RDLOM doesn't support serializing the object model. I found a hack that uses .NET reflection to invoke the internal Serialize method of the old RDLOM 2008.

The RDLOM R2 C# console application bundled in this code sample demonstrates how you can use the "new" RDLOM. Besides referencing the new assemblies, you need to use the Load method to deserialize RDL to the object model:

report = Report.Load(fs);

And the above-mentioned hack to serialize it back to disk or stream.

using (FileStream os = new FileStream(ReportPath, FileMode.Create)) {

Assembly assembly = Assembly.GetAssembly(typeof(Report));

object mc = assembly.CreateInstance("Microsoft.ReportingServices.RdlObjectModel.Serialization.RdlSerializer");

Type t = mc.GetType();

MethodInfo mi = t.GetMethod("Serialize", new Type[] { typeof(Stream), typeof(Report) });

report = (Report)mi.Invoke(mc, new object[] { os, report });

}

Other than that, RDLOM remains virtually unchanged from 2008 excluding that it now supports the R2 enhancements to RDL. What if you don't need this overhead but you need to make small changes to RDL that don't necessarily require an object-oriented layer. I've found that the easiest way to do this and avoid XML programming is to use LINQ to XML. The LINQ to XML project included in the same sample demonstrates this approach. For example, the following code changes the data source reference of the first data source.

static void Main(string[] args) {

XElement report = XElement.Load("report.rdl");

string dns = "{" + report.GetDefaultNamespace() + "}";

XElement ds = report.Element(dns + "DataSources").Element(dns + "DataSource").Element(dns + "DataSourceReference");

ds.Value = "/Data Sources/AdventureWorksAS2008";

report.Save("report1.rdl"); }

Not happy about the RDLOM story? Join the movement and vote for my suggestion on connect.microsoft.com to have a supported RDLOM.

New Reporting Services Data Providers

tlachev — Sun, 28 Feb 2010 15:39:00 GMT

Two new data providers will be included in the SQL Server 2008 R2 release of Reporting Services, Microsoft SQL Azure and Microsoft SQL Server Parallel Data Warehouse, that will let you report from SQL Azure and SQL Server 2008 R2 Parallel Data Warehouse (code named Madison) databases respectively. I decided to take the Microsoft SQL Azure data provider for a spin. As you probably know by now, SQL Azure is a part of the Microsoft Windows Azure SaaS cloud offering. SQL Azure lets you host your SQL Server relational databases in the Microsoft data centers that provide scalability and fault tolerance services. This is great for companies of all sizes which don't have resources or expertise to purchase and configure expensive hardware and software in order bo build scalable and highly available solutions. IMO, SQL Azure will be increasingly important, especially after Reporting Services and Analysis Services become available on SQL Azure although Microsoft doesn't say yet when this will happen. At this point, you can only use Reporting Services as a client to author reports from SQL Azure-hosted databases.

Strictly, speaking if your database is hosted in SQL Azure, you can use the SQL Server or OLE DB providers (already included in Reporting Services) to send queries to SQL Azure. Just like on-premise solutions, SQL Server Azure clients retrieve data using the native SQL Server Tabular Data Stream (TDS). However, if you decide to use the native SQL Server providers, you will run into some issues with retrieving SQL Azure metadata. Hence, Microsoft added the SQL Azure data provider. To test it out, I followed these steps:

I signed up for SQL Azure. If you MSDN Universal subscriber, you can sign up to test SQL Azure for eight months for free.
I deployed the AdventureWorksDW2008R2 and AdventureWorksLT2008R2 databases to my SQL Azure server. The Adventure Works database deployment scripts for SQL Azure are available on codeplex. They will create AdventureWorksDWAZ2008R2 and AdventureWorksLTAZ2008R2 database on your SQL Azure server.
Installed SQL Server 2008 R2 Release Candidate which is not publicly available.
Created a new report in BIDS. As usual, I added a data source and selected the Microsoft SQL Azure provider.
In the Connection Properties dialog box, I entered the name of my SQL Azure server and my standard security credentials (Windows security is not supported on SQL Azure). For some reason, the database dropdown is not automatically populated so I had to enter the name of the database AdventureWorksDWAZ2008R2 manually.
Once connectivity has been taken care of, life is as usual. The Graphical Query Designer (toggle the Edit as Text button if the generic query designer starts first) shows the database schema.

One thing that surprised me was the performance of data retrieval. Even when I opted to get all data from FactInternetSales, which has thousands of rows, the query executed pretty fast. One caveat though with this approach is that you have open port 1433 on your corporate network because this is the port SQL Server uses for client connections. This is not required if your application is installed on Windows Azure and connects to the SQL Azure database on the server.

Report Builder 3.0 Edit Sessions

tlachev — Mon, 18 Jan 2010 14:25:00 GMT

When Report Builder 2.0 shipped , it introduced local and server preview modes. If the report uses local references only, such an embedded data source(s), Report Builder 2.0 executes the query and renders the report locally just like the BIDS Report Designer. However, when the report uses a server reference, such as shared data source(s) or subreports that were saved on the server, Report Builder 2.0 would transparently upload and process the report on the server just like the Report Builder 1.0.

Report Builder 3.0 improves the performance of server preview mode by introducing edit sessions. An edit session is implicitly created when the user runs the report for the first time. Behind the scenes, Report Builder 3.0 calls the new CreateReportEditSession API to instruct the server to create an edit session. The server caches the report dataset(s) in the ReportServerTempDB database (not in memory) and uses the cached data for subsequent previews. In this respect, you can think of edit sessions as the *.data files that the BIDS Report Designer generates to cache the report data in order to speed up report preview. As long as you don't make changes to the report that affect the data, such as changes to the dataset itself, data source, or parameters, the cached copy of the data is used by the report. This means that the user may not see changes that might have taken place in the database for the duration of the edit session. To make sure that the latest data is used, click the Refresh button on the Report Builder 3.0 toolbar. Refreshing the report discards the edit session and executes the database queries.

The administrator can configure two new server properties to manage edit sessions:

EditSessonCacheLimit - By default, the data cache can hold up to five datasets per report and user. Note that if the dataset is parameterized, each parameter combination counts as one dataset. If you use many different combinations of parameter values, the report might need more cached dataset copies. The administrator can allow this by increasing the EditSessonCacheLimit setting.
EditSessionTimeout - By default, an edit session lasts 7200 seconds (2 hours). The servers resets the session every two hours when the user previews the report.

Report Builder 3.0 edit sessions are a performance-related enhancement that makes server-side report preview more efficient.

Report Builder 3.0 Enhancements in R2 and SharePoint 2010 Mode

tlachev — Wed, 13 Jan 2010 03:06:00 GMT

A couple of Report Builder enhancements in SharePoint 2010 integration mode caught my attention. First, Report Builder 3.0 now launches by default when the Open in Report Builder action is initiated inside SharePoint 2010. Previously, Report Builder 1.0 would be launched.

Second, if the report includes a drillthrough action to another report, the action now works when the action is initiated from Report Builder 3.0 preview mode. As it turns out, a drillthrough action is forwarded directly to SharePoint. Previously, a report drillthrough action will result in an error because the Report Builder would attempt to resolve the action locally.

ReportService2010 Endpoint

tlachev — Wed, 13 Jan 2010 02:29:57 GMT

Here is a little gem for developers programming Reporting Services. SQL Server 2008 R2 brings a new web service endpoint, ReportService2010, which merges the functionalities of the ReportService2005 and ReportService2006 endpoints. The new endpoint can manage objects on a report server that that are configured for either native or SharePoint integrated mode. Previously, the ReportService2005 endpoint was used for managing objects on a report server that is configured for native mode and the ReportService2006 endpoint was used for managing objects on a report server that is configured for SharePoint integrated mode. This required code changes and retesting if the report server was re-configured in a different mode. These endpoints are now deprecated but still supported. The ReportService2010 endpoint includes the functionalities of both endpoints. When unsupported APIs in ReportService2010 are invoked, such as invoking the CreateLinkedReport API in SharePoint mode which doesn't support linked reports, the endpoint will return an error that the operation is not supported.

SharePoint List Data Extension

tlachev — Tue, 24 Nov 2009 14:18:51 GMT

R2 brings several SharePoint enhancements, including:

Support for multiple SharePoint zones – Previously, SSRS supported access from SharePoint URLs in the default zone, as I explained before.
Support for the SharePoint Universal Logging service - Reporting Services introduced a new server interface called IRSSetUserToken. When this interface is implemented, the report server calls the UserToken Set property on this interface and passes the SharePoint user token to the data extension.
Scripting with the rs utility - The rs utility now supports servers configured in SharePoint integrated mode. This lets you create scripts that automate management tasks that target report servers in SharePoint mode.
New SharePoint List Data Extension – Lets you report from SharePoint lists

The last on the list, the new SharePoint List data extension, probably picked up your interest and deserves more explanation. Previously, if you had to use the XML Data Provider to call the SharePoint web service if you wanted to report off SharePoint lists. The new extension simplifies this by supporting SharePoint lists natively. But before you get too excited about it, I have to dampen your enthusiasm by mentioning two limitations:

The extension supports querying a single list only.
It doesn't support folders if the list organizes items in folders.

The SharePoint List data extension is supported with both modes – native and SharePoint integrated. It supports Windows SharePoint Services 3.0 and 2010, as well as MOSS. Next, I'll demonstrate the SharePoint List extension by setting up a dataset that retrieves items from the Issues list of a SharePoint site. The SharePoint list extension is available both in the BIDS Report Designer and Report Builder 3.0. For the purposes of this demo, I'll use the BIDS Report Designer.

Configuring the data source

Start by setting up the data source as you would normally do when you author a report.

Set up a data source that uses the Microsoft SharePoint List provider.
In the Connection String field, enter the URL of the SharePoint site that has the list you want to query.
Switch to the Credentials tab and select the Use Windows Authentication option. Click OK to close the Data Source Properties dialog.

Configuring the dataset

Once the data source is in place, the next steps will be to set up a dataset. The SharePoint List data extension supports a graphical query designer that lets you select and filter a list.

Add a new dataset that uses the data source you've just created. On the Dataset Properties, click the Query Designer button.

Report Designer loads the graphical query designer. The SharePoint Lists pane shows all lists in the library. You can check a list to get all fields of the list or select individual fields. You can hover on a field to see additional properties as tooltips, such as Name, Identifier, Field Type and Hidden properties.

You can also filter the list. In this case, the Issues list is associated with a My Issues view. Since I don't have any issues assigned to me, I had to overwrite the list filter by supplying a non-empty query filter. For demo purposes, I decided to set up a filter where the Title column contains the word "parameter" and the Modified date is after 10/1/2009.

Click the Run Query button to execute the query and see the data and the OK button to close the Query Designer and generate the dataset.

In case you are curious what the actual query looks like, click the Edit As Text query button. If you want to get all fields, the query can omit the ViewFields element.

<ListName>Issues</ListName>

. . .

</ViewFields>

<Query>

<Where>

<And>

<Value Type="Text">parameter</Value>

</Contains>

<Gt>

</Gt>

</And>

</Where>

</Query>

</RSSharePointList>

The SharePoint List data extension is a new R2 feature that simplifies the process of authoring reports from SharePoint lists. Although it doesn't support all features of third-party data extensions for SharePoint, it's a welcome enhancement that lets you retrieve and filter items from a single SharePoint list.