Rules

Secret ingredients to quality software

Edit
Info

Rules to Better Reporting Services

7 Rules

If you still need help, visit Enterprise Reporting and BI and book in a consultant.

  1. Do you know how to get maximum logging in Report Server?

    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:

    1. Connect to the ReportServer database in SQL Management Studio
    2. 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
  2. Do you know which reports are being used?

    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))

  3. Do you know the 5 user experiences of Reporting Services?

    The five user experiences of Reporting Services are:

    • Vanilla (Report Manager)
    • Website
    • Email
    • Windows
    • SharePoint

    ReportingServicesVanillaExample
    Figure: Vanilla user experience

    ReportingServicesWebsiteUExample
    Figure: Website user experience

    ReportingServicesEmailUExample
    Figure: Email user experience

    ReportingServicesWindowsUExample
    Figure: Windows user experience

    ReportingServicesSharePointReportingUI
    Figure: SharePoint user experience

  4. Do you know when to use Reporting Services?

    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...

    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

    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

  5. Do you know how to migrate Reporting Services reports?

    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

    detailsview
    Figure: Find reports creators by clicking "Details View" inside report folder

    • Send an email to report creater ask for permission to delete

    sent
    Figure: Send an email to ask permission

    receive
    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.

  6. Do you use Report Server Project?

    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).

    report server project1
    Figure: Bad example – C# project with reports opening as XML

    To open the reports in the right view you will need to:

    1. 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

    report server project2
    Figure: Checking Microsoft Reporting Services Projects is installed

    * In the micros Microsoft Download and install [SQL Server Data Tools (SSDT) for Visual Studio website](https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017) you will find all the instructions to install the tool via Marketplace or SSDT standalone installer.
    1. Create the project selecting Business Intelligence | Reporting Services | Report Server Project

    report server project3

    1. Add existing reports and create your new DataSource (based in the information on your Report Portal)

    report server project4
    Figure: Good Example – Report Server project with reports opening in the design/preview view

  7. Do you verify that Report Server authentication settings allow a wide range of web browsers?

    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:
    ::: greybox  <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.

We open source. This page is on GitHub