Rules To Better Reporting Services
Since 1990, SSW has supported the developer community by publishing all our best practices and rules for everyone to see.
If you still need 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.
To update the value you can:
- Connect to the ReportServer database in SQL Management Studio
- Execute the following script and update the value to the number of days you want to track
EXEC SetConfigurationInfo @Name=N'ExecutionLogDaysKept',@Value=N'365'
After you have this, you can query the ExecutionLog table to find useful information about report execution like:
- Which users are actively using the reports
- How long reports are executing
- The last time a report was executed
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.
RANK() OVER (PARTITION BY ReportID ORDER BY TimeStart DESC) AS iRank
FROM dbo.ExecutionLog t1
ON t1.ReportID = t2.ItemID
SELECT t2.Name AS ReportName,
SUBSTRING(t2.Path, 0, CHARINDEX('/', t2.Path, 2)) ParentFolder,
FROM RankedReports t1
ON t1.ReportID = t2.ItemID
WHERE t1.iRank = 1
GROUP BY t2.Name, Path, ReportID
ORDER BY MAX(t1.TimeStart) DESC;
The query above gives you the last reports that were accessed (Credit to Eric Phan - SSRS - Find out which reports are being used (handy for migrating only the useful reports to a new server))
Here are the steps to subscribe a report:
1. Open IE, go to the folder view which contains the report you're going to subscribe.
Figure. Reports folder view
2. Click the report you're going to subscribe and select "Subscribe...".
Figure. Subscribe report
3. Configuring the subscriber's email address, report render type and schedule.
Figure. Configuring subscribe settings
The five user experiences of Reporting Services are:
- Vanilla (Report Manager)
Figure: Vanilla user experience
Figure: Website user experience
Figure: Email user experience
Figure: Windows user experience
Figure: SharePoint user experience
Like any solution, Reporting Services has its pros and cons. From our experience, we have discovered these things about Reporting Services:
- Parameters - you are forced to use built-in controls
- Query string - when you change the parameters and refresh a report, the values do not appear directly in the query string, making it hard to copy/paste URLs
- Can't separate SQL into a strongly-typed dataset or middle-tier object like in ASP.NET
- There are potential difficulties with the deployment of RS reports and the exposing of them. However, once we have the infrastructure...
- You can develop read only reports faster in Reporting Services than ASP.NET
- Maintenance with RS is easier than ASP .NET, as with most cases you don't have to write any code
- Flexibility with groupings and totals is easier. In ASP.NET you would need to iterate through the DataSet, keeping variables with the totals
- Parameters are built-in. In ASP.NET there is code
- Drilldown interactivity. In ASP.NET you need to code up a treeview
- Users can have reports automatically emailed to them on a schedule
- Users can export natively to PDF and XLS, plus a variety of other popular formats
So in conclusion, if you will only ever need 1 report, go with ASP.NET - it is easier to get up and running. If you plan to have more than one report, use Reporting Services - it's worth the time to configure.
For a more detailed comparison between reporting solutions, take a look at our Guidelines for Report Solutions - Web Clients
Figure: Reporting Services has built-in support for PDF/XLS export and can be embedded in your ASP.NET pages
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 three steps:
Step 1: Find the reports that don't need to be migrated
- You need to install SSW SQL Reporting Service Auditor both on the old and new servers. (You'll also need to run it on 3rd step)
- Find those reports are not-in-use, as per a rule:
Do you know which reports are being used?
- Find creators of those reports, by clicking “Detail Views” in reports folder
- Figure: Find reports creators by clicking "Details View" inside report folder
- Send an email to report creater ask for permission to delete
- Figure: Send an email to ask permission
- Figure: Email received with permission to delete from creator
2. Migrate those in-use reports from old server to new server
Tip: use the
ReportSync tool to save time
3. Check audit results
- Run SSW SQL Reporting Service Auditor on both sides
- Compare audit results. Note that even error and warning messages also need to be the same
If audit results are exactly the same on old and new servers, it indicates that migration is successful.