Sitting down to do a write up for twitter contest, I spent quite a bit of time coming up with what I see as some of the best features of with Reporting Services 2008 R2. I realized I could easily write the next Reporting Services book just on the new features alone. But then this post is part of T-SQL Tuesday (#tsql2sday) on and time just wouldn’t permit. Instead I thought I could start by focusing on some of the best Business Intelligence features of R2. Still a long list, I mean come on isn’t Business Intelligence all about reporting in one respect or another.

To start let’s have a quick discussion on what is not in Reporting Services 2008 R2. SQL Server 2008 R2 and the Office 2010 Suite introduce a host of new BI features not built as a part of Reporting Services but they do enhance or consume from it these include the SQL Azure, Parallel Data Warehouse and, though not a SQL Server tool but worth the mention Office2010, SharePoint 2010 and PowerPivot. All of these tools as part of the Microsoft BI Suite and offer compelling ways to store data and to access and render in ways that would have required countless man hours in the past. With that out of the way we can start to focus on what is included in R2.

Data Sources

Reporting Services has done well being open with its data source availability. From the start Reporting Services has been able to connect to most OLE DB and ODBC data sources. Now in R2 the addition of Parallel Data Warehouse and PowerPivot for SharePoint has been added. Along with that, much needed support for accessing SharePoint 2007and 2010 Lists is now possible.

Shared Data Sets

One area reporting often struggles is with consistent data across reports. With the introduction of Shared Datasets it is now possible for many reports to refer to the same Shared Dataset in much the same way that Reporting Services has always allowed with the Shared Data Source. Shared Datasets can be used throughout a report project and are accessible to anyone with security to the Shared Dataset on the Report Server.

Multiple Dataset access in a Data Region

In the past, Reporting Service limited access to one dataset per data region (table, tablix, chart, etc…) and in most cases this was acceptable. However, with the number of data sources supported and the need for quick turnaround, limited space and limited resources the need to combine datasets was a real hindrance. In R2 this has all changed and it is now possible to perform “Lookups” on other datasets. Report Developers can now access data from a SQL Server Query and reference a SharePoint List where reference data or contact information might be contained. Three new functions make this possible to lookup one from a dataset (Lookup), return and array of matching values from dataset (MultiLookup ), return and array of matching values from an expression (LookupSet).

Nested Aggregates

Anyone who has spent anytime reporting can understand why this feature may have been left out. The idea of averaging an average is a classic example of data gone wild. But this function does have its place. Nested Aggregates allow scenarios like the Average Monthly Sales to be calculated over individual days of data back the past three months. In the past this same report would have required a separate query for the detail day’s data and the summary level average by month.

Data Level Visualization

Scorecards and Dashboards are often about the flash. No I am not talking about the kind that a certain fruit slinger despises. I am talking about the bars, lines and stop signs that many executives like to stare at with the morning cup of coffee or during a board meeting. R2 makes it light-years simpler to add three new visualization components. The Sparkline, an in row graph that shows a simple high-level trend line and looks a bit like a fuse. The Data Bar, a single in cell bar graph comparable to all other rows of data creating a horizontal bar graph of sorts in the Tablix region. Finally, the Indicator, made up of various sets of stoplights, stars, colored arrows and other visualizations that are then set based on ranges in the row of data.

Maps

This is by far one of my favorite features in Reporting Services 2008 R2. The Map report object is a multi-layered report control that allows for multiple datasets to be place atop a map. When first designing the map a wizard prompts to design the first layer. Here you can select from built in maps of the USA and the 50 States, you can import you own ESRI compliant Shapefiles (*.shp), or you can go right to the database to query your own spatial data from SQL Server. Once you have finished you selections in the wizard you can begin to add additional layers through the Map’s Map Layer Dialog Box and add other Map Gallery Items, Shapefiles, or spatial queries. You can create thematic maps to show colors based on polygons like states of counties. You can use spatial points to create bubble maps and various other map visualizations. Want an even more rich experience? You can even add a Tile Layer so that you can overlay data on a Bing Map. SQL Server includes access to Bing Maps for no additional cost in R2 making for a very nice addition.

Report Parts

Say you built a sweet performance dashboard and now you have other report writers asking for a copy of the RDL. Now you can break your report down to the next level above Shared Datasets, Report Parts. Report Parts can be deployed from Report Designer/Report Builder 3.0 to the Reporting Services Server and can then be consumed by anyone using Report Builder 3.0 in their own report. There is even a bit of versioning that can be done so that you don’t necessarily overwrite but instead allow others to choose when they want to use the latest version.

Report Builder 3.0

To truly empower the business with Business Intelligence much more than a bunch of charts and maps are needed. This is where Report Builder 3.0 comes in. As the name says this is the third release of the Report Builder application, an end user report design tool. Report Builder 1.0 was introduced as a Click-Once tool that allowed for some basic report authoring using a Report Model data source. Then in 2008 we saw a beefed up much more mature Report Builder 2.0. This tool allowed for almost all of the development features available in designer in a slimmed down Click-Once or Standalone version. R2 introduces the next iteration and when run from the Report Server actually has a few features that even make it more desirable to work with than Report Designer (BIDS). First is the ability to consume report parts. You can search for these through a number of methods including name and creator. By far the nicest separation from Report Designer is the Edit Session feature. This feature caches the report data on the server allowing changes to the design of the report not to require re-querying of the data (limited to 7200 seconds and 5 sessions).

All together all of these features bring Reporting Services to the next level of Business Intelligence. Of all of the features the biggest take away is that as much effort that is being put in to continue to improve and enhance even more is being put in to help the Self-Service Business Intelligence quest. Developers, DBA’s and Analysts the like can all benefit from the features and enhancements.