Tag Archive for: SQL Server 2008 R2

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:

1. I signed up for SQL Azure. If you MSDN Universal subscriber, you can sign up to test SQL Azure for eight months for free.
2. 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.
3. Installed SQL Server 2008 R2 Release Candidate which is not publicly available.
4. Created a new report in BIDS. As usual, I added a data source and selected the Microsoft SQL Azure provider.
5. 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.

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

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.

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.

Besides indicators, R2 adds sparklines and data bars to the arsenal of data visualization features. Sparklines are small graphics embedded in a context of words, numbers, and images. They help end users visualize simple trends. You could implement sparklines and simple data bars in the previous versions of Reporting Services but you had to go through many steps to strip down the chart or gauge regions. R2 makes the process much simpler. Let’s see what it takes to author a sparkline report with R2 that is similar to the Sparklines report included in my book source code. [View:https://prologika.com/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/6175.Sparklines-R2.rdl]

This report helps the user visualize the performance of the Adventure Works sales persons. For each person, the report shows the employee’s trend based on the reseller sales made over four quarters and his performance ratio which is defined as the actual sales made by the employee divided by his sales quota. The report gets the data from the Adventure Works 2008 Analysis Services cube.

Configuring Sparklines

In Reporting Services, a sparkline is a scaled-down chart region. Configuring a sparkline takes a few simple steps.

1. Right-click on a tablix cell and click Insert ð Sparkline.

2. In the Select Sparkline Type dialog box that follows, select the Line sparkline type.

3.  Double-click the sparkline region to open the Chart Data panel which is another new design feature in R2.

4.  Click the plus sign in the Values pane and select the Reseller_Sales_Amount to configure the sparkline series.

5.  Click the plus sign in the Category Groups pane to add the Category_Quarter_of_Year field to configure the category groups.

That’s it! I’ve made a few more minor adjustments to finalize the sparkline, such as removing the chart border, setting the chart background for the “green bar” effect and increasing the line width.

Configuring Data Bars

To demonstrate data bars, I implemented the employee’s performance ratio using a data bar region. Similar to a sparkline, a data bar is a simplified chart.

1. Right-click on a cell and click Insert ð Data Bar. I actually, configured the data bar outside tablix first by embedding it inside the rectangle and copying and pasting it inside the tablix cell. I did that to make the bar narrower so it doesn’t occupy the entire cell height.
2. On the Select Data Bar Type, leave the default Bar type selected.

3.  In the Chart Data panel, click the plus sign in the Value pane to add the PerformanceRatio field and set its aggregation function to Avg.

That’s pretty much it to set up the initial data bar although I had to go through a few more steps to configure the bar labels and appearance.

Although not revolutionary features, Indicators, sparklines and data bars will save you time when you need to enhance your report with simple graphics. Sean Boon from the Reporting Services team has written an interesting blog post that demonstrates more sparkline features to implement a win-loss sparkline.

R2 brings more control over pagination and rendering. A question that pops every now and then on the discussion list is how to name Excel worksheets for each instance of a row group. This scenario wasn’t supported before R2. In R2, there are addition page break options, as shown in the screenshot below. One of them is PageName.

In this case, I set the PageName property of the ProductCategory row group to the Category field. Then, I set up the BreakLocation of the ProductCategory row group to break when the product category changes. The screenshot below shows what the exported to Excel report looks like. As you can see, each worksheet is now named. If the row group spills on the next page, a new worksheet is added with the same name.

Another welcome enhancement is the ability to reset page numbering. Suppose you have a master report that includes a subreport and you want to reset the page numbering before the subreport is rendered. This is another commonly requested scenario which wasn’t supported before R2. In the screenshot below, I have a report package that includes two subreports. I want to reset the page number after the first subreport renders. Since the subreport region doesn’t have page break properties, I enclosed the first subreport in a rectangle and set the rectangle PageBreak ð BreakLocation to End and PageBreak ð ResetPageNumber to True. In the page footer, I added Globals!PageNumber of Globals!OverallTotalPages. Since you can reset the page number, the RS team has added also OverallPageNumber and OverallTotalPages built-in fields to the Globals collection. Given this setup, the report will increase the page number for each page in the Company Sales 2008 report. Once Company Sales 2008 has rendered, the server will reset the page number but it will show the overall number of pages.

Notice that there is also a Disabled page break property. Suppose that when exported to other formats you want your report to break on each row group instance but when exported to Excel you want the entire report to export to a single worksheet. R2 has added two additional built-in fields to the Globals collection, RenderFormat.Name and RenderFormat.IsInteractive, that lets you support such scenarios. In this case, I had to define the following expression for the PageBreak ð Disabled property:

=Iif(Globals!RenderFormat.Name=“EXCEL”, True, False)

If the user exports the report to Excel, the expression turns on the Disabled property and no page breaks are generated. However, if the user exports the report to another format, the report breaks on each instance of the row group.

Finally, to round up the rendering enhancements, you have now the option to rotate text 270 degrees as shown in the report below. This can be easily achieved by setting the textbox WritingMode property to Rotate270. You probably would want to set also TextAlign to Center and VerticalAlign to Middle.