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:
- How long did the report take to generate
- Who requested the report
- When was the report generated
- Report Parameters used
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:
- Figure: How to migrate SSRS reports from an old server to another
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?
There are 3 steps:
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.
- Do you know how to get maximum logging in Report Server?
- Do you know which reports are being used?
- Do you know when to use Reporting Services?
- Do you know how to migrate Reporting Services reports?
- Do you use Report Server Project?
- Do you verify that Report Server authentication settings allow a wide range of web browsers?
- Do you know how to subscribe a report?
- Do you check out the built-in samples?
- Layout - Do you print and display your report on the web correctly?
- Layout - Do you include useful information on the footer of reports?
- Layout - Do you avoid using word 'Report' in your reports?
- Layout - Do you underline items with Hyperlink Action?
- Layout - Do you show errors in red?
- Layout - Do you have consistent report name?
- Layout - Do you include feedback information in the report?
- Layout - Do you show which version of Reporting Services you are running?
- Layout - Do you put as little content as possible in a report page header?
- Data Layout - Do you show the past 6 months of totals in a chart?
- Data Layout - Do you show data and chart in one?
- Data Layout - Do you avoid using a single chart when you need it to be scaled?
- Data Layout - Do you use expressions to show the correct scale on charts?
- Data Layout - Do you show change in your reports?
- Data Layout - Do you avoid showing change as a percentage?
- Data Layout - Do you use alternating row colors?
- Data Layout - Do you have nodes count like Outlook?
- Data Layout - Do you avoid using too many decimal places?
- Data Layout - Do you have consistent height of table row across all your reports?
- Data Layout - Do you display zero number as blank in reports?
- Data Layout - Do you show time format clearly?
- Data Layout - Do you show all of the report parameters in body?
- Data Layout - Do you know how to use logical page breaks?
- Data Layout - Do you know how to prevent charts growing with rows?
- Data Layout - Do you take advantage of vertical text to avoid lots of thin columns?
- Data Layout - Do you use gray color for past data?
- Data Logic - Do you use de-normalized database fields for calculated values?
- Data Logic - Do you use SQL Ranking functions to rank your data?
- Parameters - Do you avoid unnecessary words in the parameter?
- Parameters - Do you avoid showing empty reports?
- Parameters/Internationalization - Do you use the DateTime data type for date parameters?
- Parameters - Do you have consistent parameter names?
- Performance - Do you cache popular reports for better performance?
- Performance - Do you schedule snapshots of slow reports for quicker access?
- Internationalization - Do you use regional friendly formatting?
- Internationalization - Do you make sure your language follows the user's regional settings?
- Internationalization - Do you pay attention to the date format of your parameters?
- Internationalization - Do you make sure your language rule has an exception for Currency Fields?
- Admin - Do you validate all your reports?
- Admin - Do you create a separate virtual directory for Admin access?
- Admin - Do you take advantage of 'Integrated Security' to do Payroll reports?
- Admin - Do you remove @ExecutionTime in subject of subscription email?
- Do you know how to get email list of report subscription?
- Do you add report owner in your report?
- Do you use single line box instead of double line box?
- Do you use the correct authentication for reports?
- Do you have URL Access link for your report?
- Do you have a clear labelling for including / excluding GST?
- Do you have a summary and a detailed version of your report?
- Analysing with Excel - Do you use a live data feed?
- Do you follow the naming convention standards in Reporting Service?
- Do you use SharePoint Integration Reporting Mode over Normal(Native) Reporting Mode?
- Do you have a report which refreshes your data source?