Best Practice for SSRS report development

I have put together a list of best practices for SSRS development based on my experience. Please feel free to add your thoughts
1. Always use the common template to promote a uniform reporting Experience
2. Always convert the complex queries into views and select from the view to produce a result in the report. This will help with your maintenance
· E.g. Any query with more than two Joins will be considered as complex query
3. Don’t use “Select *” in the report query
4. If you require multi stage result building or if you are using T-SQL then convert them into Procedures and call the procedure from SSRS
5. Optimise the report Query: Always follow the best practice whilst writing the SQL code. The performance of the query will directly impact the SSRS report performance so tune the query before using it in the reporting
6. Introduce only the necessary parameters as more than 6 parameters will give a poor user experience
7. Assign the default value to most of the parameters by discussing with users
· Remember if you are assign default parameters to all the parameters then the report will get auto loaded when the user select the report. If the execution time is greater than 1 minute then please go for “null” destination snapshot to cache the report.
8. If the user chooses “All” for multi select parameters then don’t send the parameter list, instead send “All” to the SQL and handle it. This will give a great boost to the performance
i. E.g if we have parameter called Branch and if the user chooses “ALL” then in the SQL use where (Branch in (@branch) or 'All' in (@branch))
9. Don’t retrieve more than what is needed. If the report delivers more than 5-6 pages (or more than 300-400 rows) of data then check with user to deliver them via E-Mail during the offline hours.



10. Use all the space
· Profile the data to understand the maximum length required. As a rule of thumb wrap the text and don’t wrap the numbers and dates
· Don’t assign more space than is required for columns
11. Before starting the report set the report width property to one of the standard paper sizes. This will give you an idea of the maximum size you can get (E.g. The width of A3 is 42 cm in Landscape mode)

12. Once developed export the report into required formats and make sure the users are happy with the extract format
· If the users want to export the report into PDF then make sure the report width fits in a page
· Use the Logical page breaks if needed for rending into different pages (Ex. Pushing each year of account into separate page)
13. If the report looks very lengthy in the report viewer then use soft page breaks via theInteractiveHeight and InteractiveWidth properties.
14. Move the Calculated fields into views. If there are calculated fields in the report then it is better to create them in a view as this will promote two things:-
· Ease of Maintenance
· Less processing load on the report server



15. Use the right data type for the parameters
E.g. Use the DateTime data type for date parameters instead of using strings. There are 3 reasons to do this:
1) Stop the bug "Cannot read the next data row for the data set"
Although a hardcoded string will work, it will not work for all users regional date/time settings.
E.g. a string data type parameter with a value of "26/01/2006" is correct for "dd/mm/yyyy", but it is wrong for "mm/dd/yyyy"
2) When SQL Reporting Services is using the DateTime data type parameter, it will get the datetime value on the user’s setting (aka the Culture DateTime format).
3) The users also get the advantage of a date/time picker control, which automatically works out the correct regional date format. This solves the US/Australian date problem. (i.e. DD and MM are reversed).
16. If your data is getting refreshed via ELT/overnight jobs then please include an additional textbox and label in the header to show the last refreshed date/time.