Continuing my intrepid journey in the SQL Server 2008 R2 land, I will discuss the new features of the recently-released CTP3. In this blog, I’ll talk about the first new feature that caught my attention – BIDS support for 2008 and R2 projects.

After hearing the outcry from many developers about project compatibility, in R2 Microsoft has decided to support both SQL Server 2008 and R2 report server projects. Similar to Analysis Services, Reporting Services now lets you target a server version in the project deployment settings. There is even a cool Detect Version link that would detect the SQL Server version based on the TagetServerURL setting.

So, you can have a project that targets either edition. What happens when you use R2-specific features, such as the map control, but set the TargetServerVersion to SQL Server 2008?

The map, Map1, was removed from the report. SQL Server 2008 Reporting Services does not support map report items.

I hope this message got improved before R2 ships because BIDS doesn’t actually remove the map control from the report; you just get a build error.

You should know that as with previous versions, you can deploy older RDLs to the server so you don’t have to upgrade your report definitions to R2. For instance, you can deploy RDL 2005 and the server will render the report via the compatibility interfaces without upgrading it to R2 RDL. Notice also that now we have more deployment settings, such as TargetDatasetFolder and TargetReportFolder but that’s a subject for another blog.

Continuing my intrepid journey through the new Reporting Services R2 enhancements, in this blog I’ll demonstrate some of the cool map features. As you’ve probably heard, R2 brings a brand new map control that lets you visualize spatial data on your reports. Since mapping is the one of the major enhancements in R2, there will be plenty of resources to cover it in details. For example, Robert Bruckner has written a great blog to get you started with mapping. The SSRS R2 forum adds more resources.

But what if you don’t need to visualize geospatial data, such as restaurants in the Seattle area? You shouldn’t bother with the map, right? Not so fast. What’s interesting is that the map supports the two spatial data types in SQL Server: geography and geometry. The latter lets you visualize everything that can be plotted on the planar coordinate system. That’s pretty powerful when you get to think about it. If you have the item coordinates, you can map pretty much everything. A few weeks ago, for example, I saw a Microsoft sample map report that plotted floor layout. Today, I’ll show you a report that I whipped out during the weekend that demonstrates how to visualize a heat map.

The Heat Map Report

A heat map is a graphical representation of data where the values taken by a variable in a two-dimensional map are represented as colors. The most basic variation of a heat map is a tree map that lets you visualize data trends presented as nested rectangles. In our case, the sample report shows the Adventure Works reseller sales. As shown in the screenshot, the larger the rectangle is the more sales were contributed by that reseller. The same can be observed by analyzing the rectangle color which ranges from green (most sales) to red (less sales). The greener the color is, the more sales that reseller has. I chose to use the sales as a color variable although I could have used another measure, such as number of employees, profit, etc. You can download the source code from here.

Limitations

I’ll be quick to point out the following limitations of this reporting solution:

1. As it stands, producing the report requires a two-step approach. First, you need to prepare the dataset and save it in a SQL Server table. Second, you run the report from the saved data. Unfortunately, the ReportViewer control doesn’t support RDL R2 which means it doesn’t support maps, so you cannot bind the dataset and run the report in one shot. While we are waiting for Microsoft to update ReportViewer, you can use a server-side custom data extension that exposes the ADO.NET dataset with the spatial data as a report dataset. This approach will et you bind the ADO.NET dataset to a server report. Chapter 18 in my book source code includes such an extension.
2. The performance of the algorithm that I use to calculate the coordinates of the nested rectangles degrades after a few hundred rows in the dataset. If you need to map more results, you may need to revisit the algorithm to see if you can optimize it.

Understanding Rectangle Geometry

SQL Server describes polygons in Well-Known Text (WKT) standard sponsored by Open Geospatial Consortium. For instance, a polygon is described as five (surprise) points on the coordinate system, as shown below. The fifth point is the same as the first point.

You can convert WKT to the SQL Server geometry data type by using the STPolyFromText method, such as:

geometry::STPolyFromText(‘POLYGON ((0 0, 0 125.6331, 115.0095 125.6331, 115.0095 0, 0 0))’, 0)

Calculating the Coordinates

To calculate the nested rectangle coordinates, I used the excellent Squarified Treemaps algorithm by Jonathan Hodgson. Jonathan explains in details how the algorithm works. My humble contribution added the following changes:

1. I decoupled the algorithm from his Silverlight solution to a C# console application.
2. Instead of loading the sample dataset from a XML file, I load it from the Analysis Services Adventure Works cube. Specifically, the MDX query returns the top fifty resellers order by Reseller Sales.
3. I added Console.WriteLine statements to output insert queries which you can execute to populate the Test table.

CREATE TABLE [dbo].[Test](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [Name] [varchar](50) NULL,

    [Shape] [geometry] NOT NULL,

    [Size] [decimal](18, 5) NULL,

    [Area] AS ([Shape].[STArea]()),

CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED

(

    [ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

insert into test(Shape, Size, Name) values (geometry::STPolyFromText(‘POLYGON ((0 0, 0 125.6331, 115.0095 125.6331, 115.0095 0, 0 0))’, 0), 877107.2, ‘Brakes and Gears’)

insert into test(Shape, Size, Name) values (geometry::STPolyFromText(‘POLYGON ((0 125.6331, 0 247.9348, 115.0095 247.9348, 115.0095 125.6331, 0 125.6331))’, 0), 853849.2, ‘Excellent Riding Supplies’)

…

Once you execute the INSERT statements to populate the Test table, you can use the SQL Server spatial data visualizer to view the results:

Authoring the Report

Once you have the data, authoring the report is easy.

1. Create a new report in Report Builder 3.0 Add a dataset that uses the following query:
   SELECT * FROM Test
2. Click the Map icon to start the Map wizard.
3. On the Choose a Source for Spatial Data step, choose the SQL Server Spatial Query option and click Next.
4. On the Choose the Dataset step, select the Dataset from step 1.
5. On the Choose Spatial Type, the map wizard should identify the Shape column as a geometry data type. At this point, the pre-release Report Builder 3.0 shows an empty map because it doesn’t recognize the coordinate system as Planar. Don’t worry, we’ll fix this later.
6. On the Choose Map Visualization, select the Color Analytical Map option.
7. On the Choose the Analytical Dataset step, select the same dataset because it has a Size column (reseller sales) that we will use for the color visualization.
8. On the Choose Color Theme step, choose =Sum(Fields!Size.Value) for a field to visualize and Red-Green as a color rule because the resellers with less sales will be coded red. Click Finish to exit the wizard.
9. In design mode, select the map and change the Viewport CoordinateSystem property to Planar.

Once you’ve changed the coordinate system, the polygons should magically appear. I’ve made a few more changes but you should be able to understand them by examining the map properties.

During my Reporting Services Tips and Tricks TechEd 2009 presentation, one of my most important tips was to encourage you to write more reports and less custom code. I showed a cool SharePointb-ased dashboard that we built by assembling report views. Following this line of thought, consider report maps instead of writing custom code when you need to visualize spatial data of any kind.

In this and future blogs I’ll cover the most important new features in the forthcoming release of Reporting Services which will be included in SQL Server 2008 R2 (code-named Kilimanjaro or KJ for short). Let’s start with the most obvious new feature: the all new Report Manager UI.

SharePoint-like UI

The moment you open Report Manager, you will notice that significant work was done to make the UI more intuitive, a la SharePoint.

For example, similar to SharePoint, a drop-down context menu pops up when you hover on a report item that lets you quickly access relevant tasks.

Simplified UI

The management pages also received facelift. Microsoft has reduced the number of buttons and changed the tab styling so you are sure which tab is selected.

Although seemingly minor, I am sure you’d love the new look and feel. For example, I like the fact that I don’t have run a report or switch to Details mode just to get to the report properties.

Report Builder 3.0 ClickOnce

Another welcome enhancement is that opening a report in Report Builder, launches Report Builder 3.0 (more on it in another post) by default. Report Builder 1.0 is still installed but it’s not the default reporting client. You see the writing on the wall. Report Builder 1.0 days are counted and you better embrace the future.

AJAX Report Viewer

Another major enhancement is the debut of an AJAX-enabled ReportViewer control (developers will get the Visual Studio counterpart in Beta 2 of Visual Studio 2010). The control eliminates visible page reposts when interactive actions, such as drilldown, are initiated. The page fades out but it stays in place and a spinny lets the user know that an interactive action is taking place.

All in all, end users and administrators will enjoy the new Report Manager. You won’t have to switch to SharePoint integration mode just to get a SharePoint-like UI.