Rules to Better Reporting Services - 17 Rules
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.
WITH RankedReports AS (SELECT ReportID, TimeStart, UserName, RANK() OVER (PARTITION BY ReportID ORDER BY TimeStart DESC) AS iRank FROM dbo.ExecutionLog t1 JOIN dbo.Catalog t2 ON t1.ReportID = t2.ItemID ) SELECT t2.Name AS ReportName, MAX(t1.TimeStart) LastAccessed, --t1.UserName, t2.Path, SUBSTRING(t2.Path, 0, CHARINDEX('/', t2.Path, 2)) ParentFolder, t1.ReportID FROM RankedReports t1 JOIN dbo.Catalog t2 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))
Like any solution, Reporting Services has its pros and cons. From our experience, we have discovered these things about Reporting Services:
Cons
- 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...
- Not able to work natively with modern .NET.
Pros
- 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.
Nowadays, better technologies are used to handle reports. Examples include
- Power BI: cloud-based solution that provides visually appealing reports and dashboards.
- HTML based reports: HTML frameworks have evolved significantly, making it quick and easy to make great reports.
For a more detailed comparison between reporting solutions, take a look at our Guidelines for Report Solutions - Web Clients.
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:
Step 1: Find the reports that don't need to be migrated
- 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
-
Send an email to report creater ask for permission to delete
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.
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).
To open the reports in the right view you will need to:
- Be sure that you VS has the tool/extensions Microsoft Reporting Services Projects installed, go to Tools | Extensions and Updates | Online, and search for services
- In SQL Server Data Tools (SSDT) for Visual Studio website you will find all the instructions to install the tool via Marketplace or SSDT standalone installer.
- Create the project selecting Business Intelligence | Reporting Services | Report Server Project
- Add existing reports and create your new DataSource (based in the information on your Report Portal)
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.
The configuration file for the Report Server is named RSReportServer.config and the default location is:
C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\
You should make a backup of the configuration before editing it so you can rollback if you make a mistake.
We normally change the AuthenticationTypes node from:
<AuthenticationTypes> <RSWindowsNegotiate /> </AuthenticationTypes>
to:
<AuthenticationTypes> <RSWindowsNegotiate /> <RSWindowsKerberos /> <RSWindowsNTLM /> </AuthenticationTypes>
Check out the different Authentication Types in the Report Server documentation and select the types that suit your needs.
More details on configuring Windows authentication on the report server can be found here: Configure Windows Authentication on the Report Server.
Here are the steps to subscribe a report:
- Open IE, go to the folder view which contains the report you're going to subscribe.
- Click the report you're going to subscribe and select "Subscribe...".
- Configuring the subscriber's email address, report render type and schedule.
SQL Server 2008 R2 Reporting Services comes with some great samples that will help get you started. Unfortunately, they aren't installed by default.
These samples include:
- Report Project based on the Adventure Works 2008 R2 database
- Report Builder Model based on the Adventure Works 2008 R2 database
Upon installing the samples successfully, you should see 3 folders (shown below) in your Report Manager.
For more information, visit the following links:
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.
-
Change the report's page width to 28cm (or 11in) and top and bottom margins to 0.5cm.
-
Change the Body width to 25.4cm (or 10in)
You can see the 0.5cm margin looks much better than 1cm, and you have more space to organize your content, especailly for a landscape print view.
- Resize report items (tables and charts) to fit the page. The easiest way to do this is to select (Ctrl+click) all report items that should span the whole width of the page, and set their Width property to 25.4cm (or 10in).
Tip: Export your report to PDF and do a print preview, so you don't have to print a lot of testing pages to find out the best page settings.
Tip: Remove top and bottom paddings in header and footer text can also give you more space.
Note: Reporting Services reports accept both inches and cm, so you can use either.
We have a program called SSW Code Auditor to check for this rule.
-
You should always include a useful and informative footer at the bottom of your reports. Include:
- Date and Time Printed and User who printed it - see warning below (e.g. Printed by SSW2000\JatinValabjee on 3/1/2006 3:16:30 PM)
- Execution Time (e.g. Execution time: 1 minute, 10 seconds)
- Page x of y (e.g. Page 3 of 10)
- Link to company website + slogan (e.g. <www.ssw.com.au> This opens in a New Window - Writing software people understand)
Use these handy report expressions to show the above information.
Footer Item Expression Sample Output Date and Time printed / User ID ="Printed by " + User!UserID + " on " + Globals!ExecutionTime.ToString() Printed by SSW2000\JatinValabjee on 3/1/2006 3:16:30 PM Execution Time ="Execution Time: " + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds", ( IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", "")) ) Execution time: 1 minute, 10 seconds Page x of y ="Page " + Globals!PageNumber.ToString() + " of " + Globals!TotalPages.ToString() Page 3 of 10 Tip: Copy and Paste this XML into the
for the recommended footer of all your *.rdl files. <PageFooter> Paste here </PageFooter>
Warning: Adding the User who printed it stops all data-driven subscriptionsWhen you try to add the User your data-driven subscriptions will fail with the following error:
'The '/GroupHealth' report has user profile dependencies and cannot be run unattended. (rsHasUserProfileDependencies)'.
A quick workaround is to add a user function to fallback the error to a nice message, like: "SYSTEM",
Public Function UserName() Try Return Report.User!UserID Catch Return "System" End Try End Function
Use above function to replace your reference to Report.User!UserID will allow the subscription to work correctly.
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.
With the underline effect on your hyperlink items, it will be easy for users to find the navigation on your reports.
We have a program called SSW Code Auditor to check for this rule.
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.How evident are the error messages on the 1st report below?
Reporting Services allows you to set the 'NoRows' property of a table control to warn your user when there is no data available. This is similar to handle the 'NoData event' in Access report but there is no advanced control on this message, not even a Color property - this has not been improved in RS2005, see our Better Software Suggestions page.
Here's how to add a custom "NoData" textbox with a red icon to your report:
- Add a rectangle to the top of your report, above any report items in the body. Set its BorderColor to Red.
- Drop a textbox into the rectangle and give it the value No records were found matching your criteria. Please modify your parameters and try again.
-
Add an Image control next to it. Use this error icon. This opens in a New Window as the Image (add it to your Images folder in your solution and reference it like Images/fatalerrorinfo.gif). Your report will now look similar to the one below.
-
In the Hidden property of the Rectangle, add an expression to show/hide it depending on whether any rows were returned. Use the following expression, substituting the bold for your own values (e.g. checking if the sum of all orders is < 0)
--Expression to set the visibility of the error message controls = iif( Sum(Fields!SaleTotal.Value, "MyDataSet")>0, True, False)
- Group all other report items into a rectangle - you want to be able to show and hide them dynamically.
-
In the Hidden property of this Rectangle, add an expression to show/hide it depending on whether any rows were returned. Switch the True and False values around, so that it shows if it does have records, and hides if it does not have records (the opposite behaviour to the error box). So, in the example above, the expression would be:
--Expression to set the visibility of the main report items = iif( Sum(Fields!SaleTotal.Value, "MyDataSet")>0, **False**, **True**)
A professional report should have consistent name.
A professional report should have the feedback information, then users can give suggestions directly to the designers.
See how AI is used in Power BI to provide feedback in Reports in this rule
You can add the version number to the name of the reporting server by:
- Going to Site Settings
- Add 2005 or 2008 to the name
Things in page header repeat on every page. To avoid duplicate and save paper when printing, we put content as less as possible in page header.