Upendra's Blog: SQL Server 2008 R2 Reporting Services

Friday, June 3, 2011

Top 10 Reasons to Migrate to Microsoft SQL Server Reporting Services 2008 R2

My key learning from the above Teched’11 Session

Reason #1: Tablix (2008)

Brings Best of Table (tabular) and Matrix (crosstab). Tablix = Table + List + Matrix.

Parallel Dynamic Groups

More Control over Sub Totals

Reason #2: Report Builder (2008/R2)

Report Builder is a desktop application which addresses the adhoc report building requirements of end users. Supports various data sources: Oracle, SQL Server, Analysis Services, Report Builder Models etc.

Ability to add expressions in the report:

Connects with Analysis Services:

Reason #3: Data Visualizations (2008/R2)

With 2008 SQL Server Reporting Services included enhanced charting capabilities and introduced new gauge region. In SQL Server 2008 R2, visualizations were enhanced with the introduction of Sparklines, Indicators and Data Bars.

Sparklines are simple inline graphics that help you visualize the data. Indicators are used to visualize the Key Performance Indicators (KPIs). Data Bars are similar to Excel data bars and helps you visualize the trends.

Example of a Sparkline: Shows trend of the Sales Amount over 4 years. Help end users visualize in which year was the sales amount for a Product Sub Category the largest.

Reason #4: Mapping (2008/R2)

Support for SQL Spatial types – Geography (earth co-ordinates) and Geometry (planar co-ordinates).

Spatial Sources include: ESRI shape files, SQL Server, SSRS Map Gallery, Bing.

Use the Map Wizard

Query below returns customers in Georgia in a SQL Server Database. Note the Spatial Location contains the latitude and longitude of the city. You can use Bing or Google services to query a city’s latitude and longitude.

Below example shows a heat map:

Reason #5: Report Parts (2008/R2)

Reuse common report elements for accelerated report creation, sharing and collaboration. Use the Report Publish option in Report Builder to publish/share parts of the report. Once the report is deployed to Reporting Services, the report part will also be published and be available to other users to be shared.

To reuse an existing Report Part, go to Insert>Report Parts. In the example, standard company page header is being reused.

If report parts are changed by the creator of the part, then the users of the report part can choose to update the report part if needed:

Reason #6: Data Enhancements (2008/R2)

Reports can be setup as data feeds. New Data Providers include: SharePoint List, SQL Azure, Parallel Data Warehouse.

Data Sets within Reports can now be shared for reuse the query of that data set. Changes to Shared Data Sets, all reports using that Data Set automatically pick up changes to the Data Set.

You can configure where the Shared Data Sets reside on Reporting Services:

Shared Data Sets can be cached on Reporting Services to yield better performance:

Export Data from Reporting Services in various formats: csv, excel, data feed

If the user has PowerPivot installed on his/her desktop, then the export function automatically recognizes it and

Data Exported into Excel PowerPivot:

Data can also be imported from a SharePoint List in Reporting Services:

Query Designer will show all the List in the SharePoint:

Reason #7: Robust Report Platform (2008)

Reporting Services is no longer dependent on IIS and thereby makes it autonomous and easier to configure. Reporting Services engine has been redesigned with scalability and performance in mind. Memory Utilization can be configured in rsreportserver.config.

Reason #8: Robust Report Platform (2008)

Support for Rich Formatting – HTML and CSS.

You can configure Text Properties to interpret text as HTML.

Note the above report is now HTML formatted.

Reason #9: Enhanced User Experience (2008 R2)

Report Viewer is now AJAX based (no more visible page refreshes) and offers a smooth, fluid user experience. Report Manager now has a SharePoint look and feel.