Archive for month: November, 2009

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:

1. The extension supports querying a single list only.
2. 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.

1. Set up a data source that uses the Microsoft SharePoint List provider.
2. In the Connection String field, enter the URL of the SharePoint site that has the list you want to query.
3. 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.

1. 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.

1. 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.

<RSSharePointList xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>

<ListName>Issues</ListName>

<ViewFields>

<FieldRef Name=”ID” />

<FieldRef Name=”ContentType” />

. . .

</ViewFields>

<Query>

<Where>

<And>

<Contains>

<FieldRef Name=”Title” />

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

</Contains>

<Gt>

<FieldRef Name=”Modified” />

<Value Type=”DateTime”>2009-10-01T00:00:00</Value>

</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.

Often, reports need to show images that represent discrete values, such as an image that shows a KPI status. Previously, you didn’t have another option but to use images that are dynamically changed based on the field value. Indicators, a new R2 feature, make this much simpler. In Reporting Services, an indicator is a simplified gauge region that lets you associate images with states. Although you can use indicators as stand-alone regions, you would typically nest them in a tablix, as the following report [View:https://prologika.com/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/8712.IndicatorDemo.rdl] demonstrates. The right-most column of this report shows the Gross Margin KPI status as an indicator.

Authoring this report takes a few simple steps:

1. Add a dataset that requests the Status property of the Financial Gross Profit Margin KPI from the Adventure Works 2008 Analysis Services cube grouped by Year and Quarter.
2. Use a tablix region to show the KPI status with Year and Quarter row groups.
3. Right-click the cell in an empty column and click Insert ð Indicator.
4. In the Select Indicator Type dialog that follows, select the indicator type. In this case, I chose the default directional type.

1. Once the indicator region is added to tablix, right-click on it and click Indicator Properties. In the Indicator Properties dialog box, select the Value and States tab.
2. Since in this case I display numeric values and not percentages, change the States Measurement Unit drop-down list to Numeric.

    

   By default, the default directional indicator lets you defined three states, as shown below. However, you can add or delete states and use custom images if needed. In my case, I defined a range from -1 to 0.-99 for underperformance, 0 – 0.99 as acceptable performance, and 1 as good performance.

    

   

    

   Indicators are simple but very useful enhancement to Reporting Services. They lets you visualize discrete values on reports.

When the Reporting Service team asked my opinion about shared datasets, a new feature in the forthcoming SQL Server Reporting Services R2, I was somewhat skeptical. I preferred them to focus on more important in my mind features, such as the ability to join datasets at report level. But the more I look at the way shared datasets got implemented, the more real-life scenarios I think may benefit from this enhancement.

Think of a shared dataset a hybrid between a shared data source and report execution. Similar to a shared data source, a shared dataset is a report dataset that can be managed independently and shared among reports. A shared dataset must use a shared data source. The shared datasets can be parameterized and reports that use it can pass parameters to it. Similar to report executions, a shared dataset can also be cached and refreshed on a schedule. In my opinion, there are two main scenarios where shared datasets can be useful:

• Easier maintenance – Suppose you have a dataset that you can re-use across reports, such as a dataset that populates the parameter available values. You want all reports to reuse the dataset and pick up the dataset query changes automatically.
• Improved report performance – You may have query that takes very long to execute. You want to execute the query in an unattended mode, such as outside working hours, perhaps for different set of parameters, and cache the dataset for a specific duration.

Creating Shared Datasets

Creating a shared dataset is easy. Both BIDS Report Designer and Report Builder 3.0 are capable of creating shared datasets. In Report Designer, you right-click on the new Shared Datasets folder in Solution Explorer, and click Add New Dataset. In Report Builder 3.0, you click the Create Dataset option on the main Create Report or Dataset popup screen.

You can easily convert a report-specific dataset to a shared dataset. Just right-click any dataset in Report Data Window and click Convert to Shared Dataset. Consequently, Report Designer will re-factor the dataset as a shared and it will add its definition to as an *.rsd file to the Shared Datasets folder in Solution Explorer. In the screenshot below, I have converted the EmployeeSalesDetails dataset in the Employee Sales Summary 2008 report to a shared dataset. Notice that the EmployeeSalesDetails dataset reference inside the report has a special icon.

As I mentioned in a previous post, the project properties dialog get enhanced to support new deployment settings that are specific to shared datasets. Specifically, the OverwriteDatasets setting specifies if the shared dataset definition will overwritten on deployment if it exists and the TargetDatasetFolder specified in which folder the dataset definitions will be deployed to.

Once the dataset is configured as shared, you can set up its properties, which include the dataset query, fields, and filters. Then, you can configure the dataset reference inside the report to pass parameters to the shared dataset or to have its own filters. This is similar to how to you configure a subreport. What if you want to use row-level security and you need to pass the user identify to the dataset query (User!UserID)? Just add a query parameter to the shared dataset, such as LoginID, and configure the dataset reference inside the report to pass User!UserID as a parameter to the shared dataset.

Managing Shared Datasets

Once the shared dataset is deployed, it can be managed just like any other published item using Report Manager or SharePoint. Common management tasks include setting the data source reference, caching, and security. The most interesting of these is caching. Just like report executions, you can cache a shared dataset. When a shared dataset is configured for caching, the report server will cache a dataset copy for each parameter combination. The same restrictions apply as with caching report executions. Specifically, the data source cannot use Windows Security or Prompt for Credentials authentication options. You can configure cache expiration options. For example, if data latency of 30 minutes is acceptable, you can configure the dataset cache to expire in 30 minutes.

Let’s say you a dataset query that takes very long to execute. You can set up a cache refresh plan to warm up the dataset cache on a set schedule. Cache refresh plans are also a new R2 feature. Previously, you pre-execute reports by creating a subscription using the NULL delivery provider. Moving to R2, you don’t need to use the NULL provider anymore and you can refresh shared datasets independently from reports. In the process of setting up a cache refresh plan, you need to specify an item-specific or shared schedule and default values for each parameter, just like you would do with snapshot caching or subscriptions.

As you can see, shared datasets can help you implement some interesting scenarios when you need to reduce the management effort or improve the report performance.

Aggregates of aggregates is a new feature in SQL Server R2 Reporting Services. It can be better understood by the sample Aggregates.rdl report ([View:https://prologika.com/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/2474.aggregates.rdl]). It shows sales grouped by year and quarter. The Year group subtotals use the Sum aggregation function to sum the quarter sales. The report footer total averages the year subtotals.

Prior to R2, authoring such a report would require dividing the total sales by the number of quarters, which you can obtain by using the CountRows function. You cannot just use the Avg function in the footer as the Bad Sales measure shows:

=Avg(Fields!Internet_Sales_Amount.Value)

If you do so, the Avg function would operate on the detail rows (all quarter rows) and it would produce 2,258,360. R2 lets you nest aggregate functions. The Sales footer total uses the following expression:

=Avg(Sum(Fields!Internet_Sales_Amount.Value, “Year”))

In this case, the Average function operates over the aggregated year subtotals. Note that not all aggregate functions can be nested. For example, you cannot define an aggregate over the Aggregate, RunningValue and Previous functions.