1. New development environment: (Shell VS 2010)
The upgrade is – SSRS renders a report to the native format of Microsoft Excel 2007-2010. The format is Office Open XML. The content type of files generated by this renderer is application/vnd.openxmlformats-officedocument.spreadsheetml.sheet and the file extension of file is .xlsx. The benefits of this over the previous .xls format (Excel 2003) are listed below:-
2. Max columns per worksheet increased from 256 to 16,384
If you are exporting a report with more than 256 columns in a worksheet to the normal .xls format in Denali or in SSRS versions prior to Denali, you will get the error message.
You will no longer see this error message if you are exporting to the .xlsx format.
3. Max rows per worksheet increased from 65,536 to 1,048,576
Similarly, if you are trying to export a report with more than 65,536 rows in a worksheet to the normal .xls format, you would be greeted with the error message.
However, this will no longer be an issue when you export to the new .xlsx format.
4. No of colours allowed in a worksheet increased from 56 to approx. 16 million
Prior to Denali, you couldn’t get more than 56 colours in a worksheet but with the new .xlsx format, this is very much possible. For e.g. consider the following colour matrix that I made in BIDS.
Now when I export it to .xls (Excel 2003) format, I get the following output in excel
We can see that the entire last row is blacked out due to the colour limit. However, if we export it to the .xlsx format, we get the proper output.
5. ZIP compression
The .xlsx format has ZIP compression; hence the size of the files generated would be lesser. For e.g. I generated a report with 250 rows and 250 columns in both .xlsx and .xls formats. The .xlsx format was just 25KB while the .xls format was 35KB in size. This difference may become more evident and important in the case of large reports.
6. Self Service Alerting
Alerting is a new capability that we are adding to Reporting Services as well. It enables an end-user to setup alert rules and be alerted when report data changes occur that match a set of rules. No changes are required to the existing reports – you can create alerts for any reports created in previous versions of Reporting Services, as soon as upgrade/move to SQL Server Denali in SharePoint integrated mode.
7. SharePoint Shared Service
Reporting Services in SharePoint now runs as a SharePoint shared service. Among other benefits, this provides integration with claims authentication and scaling and load-balancing across the SharePoint farm. Furthermore, report viewing performance in SharePoint has been enhanced significantly.
8. Excel rendering as XLSX, Word rendering as DOCX
These new renderers produce now Open XML Office format and take advantage of some of the new capabilities in Office 2007 and 2010, such as export up to a 1 million rows in Excel. The original renderers for DOC and XLS, which produce binary formats for older Office versions are still available but hidden by default.
9. BI capabilities delivered with PowerPivot for Excel and PowerPivot for SharePoint.
10. BI Development Studio integrated in Visual Studio 2010
Not only did we adopt the Visual Studio 2010 shell for BI Development Studio for report design, but we also rearchitected our integration with Visual Studio in general for RDLC designer and report viewer controls. This will be available with the upcoming next version of Visual Studio and enable much more simultaneous availability of SQL Server Reporting Services functionality in Visual Studio as well.
11. Filtering, Highlighting, and Slicers
a. Interactive cross-filtering and highlighting with charts
Charts can act as filters, thanks to those relationships in the underlying semantic model. This is interactive filtering, meaning that you can select values directly on the chart and have that filter other data regions. If you select one column in a column chart, this automatically:
• Filters the values in all the tables and tiles, and bubble charts in the report.
• Adds a special kind of filtering, called highlighting, to bar and column charts. It highlights the parts of other bar and column charts that pertain to that value, showing the contribution of the filtered values to the original values.
You use Ctrl + click to select multiple values. To clear the filter, click inside the filter, but not on a value.
Interactive filtering also works with charts with multiple series. Clicking one section of a bar in a bar chart filters to that specific value
b. Slicers
Slicers are another kind of filter. They filter everything on the page. Crescent slicers look very much like slicers in PowerPivot for Excel. You create a single-column table from any field that is an attribute, and then convert the table into a slicer. Each value is a button, and a button in the top corner clears or resets the filter. To select the value just click that button. The data is filtered in the report immediately.
You can select multiple values by holding the Ctrl key when you click.
You can also create an exclusive filter, where all except a certain set of values are selected. You do this by resetting the filter with the button in the top corner, and then using Ctrl + click to unselect specific values. This will show overall values excluding the unselected values.
You can add more than one slicer to your report. The filtering effects of all the slicers are combined. Note that the slicers do not filter each other; they only filter the other data regions in the report.
Unlike chart filters, slicer filters:
• Filter charts. They do not highlight charts.
• Are saved with the report. When you save, close, and reopen a report, the same values will be selected in the slicer.
In the following image, the beverages category, selected in the slicer, is filtering the bar chart and table. Note that the bar chart has only three bars. When unfiltered, that same bar chart has six bars, for the six colors in the model.
c. Filters area
Crescent also has a filters area. Like slicers, filters in this area filter all the visualizations on the page, and are saved with the report. Unlike slicers, these filters are not on the canvas itself, so they do not take up any space in the report.
There are two kinds of page-level filters:
• Basic filters with a set of checkboxes of values in a field, similar to AutoFilters in Microsoft Excel.
• Advanced filters in which you can enter freeform values, or a range of values.
With the advanced date filter, for example, you can filter for items whose date:
• Equals
• Does not equal
• Is after
• Is on or after
• Is before
• Is on or before
12. Report Performance Improvements
o For reports in AJAX Viewer
o Parity with Denali Native mode performance
Top SharePoint mode pain point
Small reports used to be 2-3 times slower: Fixed
o Faster than 2008 R2 SharePoint mode ~ 30-60%
Some Video URLS:
1. http://social.technet.microsoft.com/wiki/contents/articles/default.aspx (Featured Articles)
2. http://newtech.about.com/od/tutorials/ss/How-To-Configure-Excel-2010-Pivot-Table-For-Business-Intelligence.htm (PowerPivot)
3. http://social.technet.microsoft.com/wiki/contents/articles/sql-server-samples-readme.aspx
4. http://blogs.msdn.com/b/analysisservices/archive/2011/05/16/analysis-services-vision-amp-roadmap-update.aspx
5. http://blogs.technet.com/b/dataplatforminsider/archive/2010/11/12/analysis-services-roadmap-for-sql-server-denali-and-beyond.aspx
6. http://www.gartner.com/technology/media-products/reprints/microsoft/vol2/article15/article15.html
7. http://www.sqlserverlaunch.com/Home.aspx?Day=127
