5. April 2011 04:02
We recently converted 100’s of Crystal Reports to SQL Server Reporting Services. I have noticed a few issues that Crystal developers frequently either run into or try to do incorrectly. Even if you are developing SSRS reports a few key principals must be kept in mind. Here they are:
- One inch is 1140 pixels in Crystal and 72 Points in SSRS
- Interactive Size and Print size are two different settings. A setting of InterActiveSize=0 renders the entire report in one long page. I strongly suggest having your interactive width match your print version. Having wider margins in interactive mode will print blank pages and guaranteed to make your users upset!
- You can change the default rendering format, which is html to Excel or PDF by simply adding a URL parameter to the end of your request (e.g. &rs:Format=PDF)
- Make sure to define global data sources and have all reports use the same data source reference. Doing so, will make the DBA’s job a lot simpler when moving reports from environment to environment. He can do this without any developer involvement and through SSRS backend administration.
- Try to use stored procedures to retrieve your data at all times. Make sure to use unique column names when returning data. SSRS will bark at you with a strange error message if you don’t do so.
- It is always best to create a core report template you frequently use and save it in
Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject.
For future use. Typical things saved in a template are print page size (e.g. 8.5 x 11), preferred margins, header and footer items such as logos, page numbers and copyright notice. Next time when you start a new Report, you can select this report as your start up.
- Exporting to Excel 2003 and prior versions has a limit of 65K rows. Attempting to do so will generate a runtime error. It is best to have a check-enable option to paginate the report based on a conditional group, which in turn exports each 65K chunk in individual Excel sheets. Make sure this is an optional grouping as adding groups increases your processing time that is really not necessary when viewing a report. A typical conditional grouping at the highest level could use a formula such as =Int((RowNumber(Nothing) - 1) / 65000).
- Clicking in a row selector of a Tablix does NOT display its properties! It simply shows the common properties of all the cells embedded on the row! To change the properties of a static row in a Tablix you should turn on the Advance Mode from the bottom right menu and click on the static row in the lower left pane.
- Never ever use global level Code in SSRS to calculate totals. The code and it associated values are reset every time a new page is rendered. Don’t bother tagging you variables or code as shared. This will cause you even more headaches. Use standard Tablix summary and total rows for any aggregation processing.
- The default rendering parameters for PDF and Excel rendering extensions can be changed in the RSReportServer.config file. A typical usage is not to have report headers and footers exported to Excel. Note that this setting doesn’t have any impact in Visual Studio environment. It can only be tested on the SSRS server. The config file is located at
\Program Files\Microsoft SQL Server\<instance name, e.g. MSRS10_50.MSSQLSERVER>\Reporting Services\ReportServer
10. December 2010 04:35
Here is the slide deck and code samples for my SSRS Tips & Tricks presentation. To run the demos you need the following environment:
- Visual Studio 2008 (VS2010 still doesn’t support BI Templates ? ) or BIDS 08 if you just have SQL server installed
- SQL Server 2008 (Preferably with R2 update)
- AdventureWorks 08 OLTP database (again R2 version if possible), which you can get from CodePlex (http://msftdbprodsamples.codeplex.com).
Once ready, add the following stored procedure to your database. It simply gets a dataset containing products and their associated categories and subcategories.
Enjoy. Don’t hesitate to drop me an email if you have any questions or comments.
SsrsStoredProc.zip (912.00 bytes)
SsrsTipsCode.zip (374.26 kb)
SsrsTipsSlideDeck.zip (111.66 kb)