SQL Server Reporting Services is a comprehensive, server-based reporting solution that can author, manage, and deliver both paper-oriented and interactive, Web-based reports. Do you agree with all these rules? Are we missing some? Let us know what you think.
🔊 Historical (2006) Listen to Adam's podcast on these rules with Greg Low (42 min)
⚠️ While SQL Server Reporting Services (SSRS) is still in use, it is considered legacy technology. For modern data reporting and analytics, Power BI is the recommended solution. Check out our Rules to better Power BI.
If you need consulting help, visit Enterprise Reporting and BI and book in a consultant.
By default SSRS will track reporting execution for the last 60 days. This might be OK in most cases, but you may want to adjust the retention days if you want better report usage statistics.
SSRS keeps track of each report that gets executed and records useful information like:
So it's quite simply a matter of querying the ReportServer database for information in the ExecutionLog table.
Like any solution, Reporting Services has its pros and cons. From our experience, we have discovered these things about Reporting Services:
Let's say you want to migrate SSRS reports from an old reporting service server (e.g. SQL Server 2008 R2) to a new one (e.g. SQL Server 2016). What involves?
When working with SSRS reports, you need to have the right type of project otherwise it will be difficult for a developer, to create new reports or update existing ones.
If you have some reports and want to check them into source control, if you add them to project that is not a report project, your reports will not open in the design/preview view (allowing to see the DataSource and DataSets). They will open in the XML view (which is not pretty to work with).
The default configuration for Report Server isn't accessible by most mobile browsers and some desktop browsers too. You can adjust the authentication types allowed to increase the range.
Here are the steps to subscribe a report:
SQL Server 2008 R2 Reporting Services comes with some great samples that will help get you started. Unfortunately, they aren't installed by default.
A lot of the time, you will want a hard copy of your reports. Obviously reports are different sizes on screen and on paper, so you need to format your report so it exports to PDF and prints properly. Here's how.
When designing custom applications you want to include branding on reports. You should always include a useful and informative footer at the bottom of your reports.
We believe the word 'Report' is redundant in a report, so we avoid using this word in our reports.
The Hyperlink Action allows users to navigate between reports smoothly, but users may ignore the navigation functionality in your reports.
Errors on reports should not occur but when they do it is best to make it clear to the reader that they have just experienced an error.
A professional report should have consistent name.
A professional report should have the feedback information, then users can give suggestions directly to the designers.
You can add the version number to the name of the reporting server by:
Things in a report page header repeat on every page. To avoid duplicate and save paper when printing, we put content as less as possible in the header.
When you are working with reports that use time-based data (sales figures, employee productivity etc.), it is handy to see how you went this month compared to the past 6 months. The best way to show this is on a bar chart.
Users of the report service will find the data easier to understand and compare. However, in order for the report to be understandable, the data and chart must be clear and uncluttered.
Avoid using a single chart when you need it to be scaled, instead use an embedded chart within the table for dynamic chart lists.