Unlocking Azure SSRS Potential with Microsoft Integration

Azure SSRS is a powerful tool for creating and managing reports, but its true potential is unlocked when integrated with other Microsoft services. With seamless integration, you can create a centralized reporting platform that leverages the strengths of Azure SSRS, Power BI, and other Microsoft tools.

By integrating Azure SSRS with Power BI, you can create interactive and dynamic reports that provide actionable insights to your organization. This integration allows you to create reports that can be easily shared and analyzed by stakeholders, enabling data-driven decision making.

The integration of Azure SSRS with Power BI also enables you to create reports that can be easily embedded into Power Apps and other Microsoft applications. This allows you to create a seamless user experience, where users can access reports and data directly from the application they are using.

This integration is made possible through the use of APIs and data connectors, which enable data to be easily shared and analyzed between different Microsoft services.

Azure SSRS Hosting offers several options to deploy and scale your Reporting Services solution. You can host the Web Document Viewer on multiple web application instances if you disable session affinity (ARR affinity).

To scale out a web application to multiple instances, use one of the alternative options described below. Microsoft Azure is a distributed system, and virtual machines are shared between different clients. Information stored on a virtual machine can be lost when a Load Balancer turns this machine off and switches to a new machine.

You can deploy SSRS on Azure Virtual Machine, just like on any (virtual) machine on premises. This approach gives you full freedom of choice among the supported versions of Reporting Services. You can even use the same version that you are using on premises.

If you're looking for a fully managed database service, you can host report server databases in Azure SQL Managed Instance. This approach offers high availability of databases out of the box, with a 99.99% availability SLA.

Here are the key benefits of hosting report server databases in Azure SQL Managed Instance:

To use Azure SQL Managed Instance, you can simply point your SSRS to the instance during the setup, or re-point the existing installation using SSRS Configuration Manager. You can also restore a backup of your report server database from on-premises SQL Server on Azure SQL Managed Instance.

If you're using an older version of SSRS, you can still host report server databases on Azure SQL Managed Instance. You'll need to configure the instance before installing the legacy version of SSRS on Azure Virtual Machine. Configuration steps are very simple and ensure that some of the commands issued during the installation process by legacy versions of SSRS are safely ignored by SQL Managed Instance.

Azure SSRS offers flexibility in deployment options, including deploying SSRS on Azure Virtual Machine, which provides a full range of supported Reporting Services versions.

You can reuse the same Azure Virtual Machine for SQL Server database engine to store report server databases, and running report server databases on another instance of SQL Server can improve performance.

Choosing a local instance is useful if you have sufficient system resources or if you want to save on software licenses.

To enable inter-process synchronization, you can pass the `StorageSynchronizationMode.InterProcess` parameter to the `UseCachedReportSourceBuilder` method. This is especially useful when your application is running on multiple machines, as it ensures that the report instance is available across all machines.

You can adjust the cache settings for your application using the `CacheCleanerSettings` class. For example, you can set the time to live for reports to 2 minutes and for documents to 1 minute.

The Cached Source Builder uses a persistent storage, such as Azure Cloud Storage, to store document pages while the document is generated. This helps prevent data loss when the Load Balancer routes a request to an application instance where the document is not generated or when an application pool is reloaded.

To implement the Cached Source Builder, you'll need to add the following NuGet packages to your project: `DevExpress.AspNetCore.Reporting`, `DevExpress.AspNetCore.Reporting.Azure`, `DevExpress.XtraReports.Web.Azure`, and `DevExpress.XtraReports.Web.WebDocumentViewer`.

Serialization is a crucial aspect of Azure SSRS Configuration. In an Azure environment, report definitions are serialized to XML, resulting in a REPX file saved in Blob Storage.

This process is necessary to maintain report state, especially when working with report parameter values or interactive features like drill-down, drill-through, and interactive sorting. If the Document Viewer's request is routed to another server instance, a report instance is required to ensure continuity.

A report instance is restored from an XML file, but be aware that only serialized settings are preserved – other settings may be lost. To mitigate this, consider implementing a custom WebDocumentViewerOperationLogger.

Here's a summary of the scenarios where a report instance is required:

In Azure SSRS Configuration, query text can include query variables or stored procedures with input parameters.

The corresponding query parameters for the dataset and report parameters for the report are automatically generated. This happens as long as the query text doesn't include the DECLARE statement for each query variable.

You can create a report parameter by including it in the SQL query, like creating a report parameter named EmpID.

Each report parameter has a default data type of Text and an automatically created dataset to provide a drop-down list of available values.

Modernizing at your own pace is essential, especially in times of uncertainty. You can choose to slow down and operate more cost-effectively, or lean into new opportunities that didn't exist before.

Some organizations decide to lift and shift their solutions to the Microsoft cloud, instantly benefiting from cloud-only capabilities. This approach can be beneficial, but you may also want to consider migrating to cloud-native SaaS and PaaS solutions.

Lifting and shifting report server databases to the Microsoft cloud has two main options, which we'll explore further. These options pertain to where the report server databases are being hosted.

Typical Power BI reports are optimized for exploration and interactivity, allowing you to easily slice and dice data, discover relationships, and patterns. This makes it ideal for analytical reports.

Windows Authentication isn't supported in SQL Database, so you'll need to use SQL Server Authentication with a username and password.

This means you'll have to provide credentials every time you connect to the database, and they must be sufficient to access the database and perform any necessary actions.

You can choose to use a stored username and password, or configure the unattended execution account on the report server if you don't want to enter credentials every time.

To use a stored username and password, you can select the option to use credentials as Windows credentials to negotiate the double hop, or impersonate the authenticated user after connecting to the data source.

If you choose not to enter credentials, you'll need to have the unattended execution account configured on the report server.

Credentials are a crucial aspect of Azure SSRS security. Windows Authentication isn't supported, so you'll need to use SQL Server Authentication instead.

You'll need to provide a user name and password every time you connect to the database. This means you'll need to configure your credentials to have sufficient permissions to access the database objects you need.

The owner of the external data source must configure credentials that provide read-only access to the database objects. This ensures that you can access the data you need without compromising security.

There are a few ways to specify credentials in a report authoring client. You can either use a stored user name and password or choose not to provide credentials at all.

To use a stored user name and password, you can select options to use credentials as Windows credentials. This can help negotiate the double hop that occurs when the database that contains the report data is different than the report server.

To choose not to provide credentials, you'll need to have the unattended execution account configured on the report server. This requires setting up a specific account that can be used to run reports without requiring user authentication.

Here are the options for specifying credentials in a report authoring client:

Microsoft Entra ID plays a crucial role in Azure SSRS Security. You can use Azure SQL Database with Microsoft Entra ID. This is supported when you have ADALSQL installed on the report server.

Active Directory Authentication Library for SQL Server (ADALSQL) is a must-have. It's installed on the report server.

To set up Microsoft Entra ID properly, you'll need to configure ADFS to federate across on-premises Active Directory (AD) and Microsoft Entra ID. This involves setting up Active Directory Federation Services (ADFS).

Kerberos Constrained Delegation (KCD) is also required. It needs to be configured from the report server to the ADFS server.

To authenticate to Azure SQL Database, you'll need to configure the report/data source to authenticate as the user viewing the report. This is a key step in setting up Microsoft Entra ID.

To enable the Document Viewer to preserve its state after an application restarts, you need to create an Azure Storage account. Select the Standard general-purpose storage account, which includes the Blob Storage, Queue Storage, and Table Storage Azure services required by DevExpress Reporting components.

The Web Document Viewer stores service information in Table Storage and saves document files in Blob Storage. You can use a custom storage instead of Table and Blob storages, but you'll need to implement the IAzureBlobStorageService interface.

To create the custom storage, you'll need to substitute related services in a container. The Web Document Viewer creates and uses “dxxrreports“ and “dxxrdocuments“ tables in Table Storage, and the “dxxrcommonstorage“ blob container in Blob Storage.

Here are the Azure Storage account replication options you should consider:

Azure SSRS Queries are a crucial part of creating reports that fetch data from your Azure SQL Managed Instance. You can specify a query in three ways: by building it interactively, typing or pasting it, or importing an existing query from a file or report.

You can use the relational query designer to build a query interactively, selecting columns from tables or views, or specifying stored procedures or table-valued functions. You can also limit the number of rows of data to retrieve by specifying filter criteria. This designer provides a hierarchical view of tables, views, stored procedures, and other database items, organized by the database schema.

The text-based query designer supports two modes: Text Type, where you can type or paste Transact-SQL commands that select data from the data source, and Stored Procedure, where you can choose from a list of stored procedures. This designer provides full support for the Transact-SQL language, including grouping and aggregates.

To specify a stored procedure for a dataset query, you can set the Stored Procedure option in the Dataset Properties dialog box, choose from the drop-down list of stored procedures and table-valued functions, or select a stored procedure or table-valued function in the relational query designer. Only the first result set that is retrieved for a stored procedure is supported.

To create an image object in Azure SSRS Queries, you'll need to use the Image function. This function allows you to insert an image into your report.

You can specify the image source as a URL, a file path, or even a database query result. The Image function supports various image formats, including JPEG, PNG, and GIF.

The Image function also has a few optional parameters, such as the image size and a border. However, these parameters are not required.

For example, if you want to display an image stored in a database table, you can use the Image function with a SQL query as the image source. This allows you to dynamically retrieve the image based on the report data.

By using the Image function, you can add visual interest to your reports and make them more engaging for your audience.

If you're dealing with reports generated asynchronously and HttpContext.Session is not available in the report's event handlers, you can force synchronous document creation.

To do this, you'll need to create a custom WebDocumentViewerOperationLogger implementation that overrides the BuildStarting method. This method's code should call the XtraReport.CreateDocument method.

You can implement the CustomLogger class as shown in the example code, which overrides the BuildStarting method to call the CreateDocument method of the XtraReport class.

At application startup, you'll need to call the UseAzureEnvironment method with the parameter that specifies the Azure Storage connection string and register the CustomLogger class implemented in the previous step.

By following these steps, you can ensure synchronous document creation for your reports, even when they're generated asynchronously.

When working with PDF rendering in Azure SSRS, it's essential to know that using the Skia rendering engine is recommended for applications with XRPdfContent controls.

The Skia rendering engine is a powerful tool that can improve the performance and quality of your PDFs.

If your Reporting application includes XRPdfContent controls, configuring it to use the Skia rendering engine can make a big difference.

Queries are a crucial part of creating reports in Azure SSRS, and they allow you to specify which data to retrieve for a report dataset.

You can build a query interactively using the relational query designer, which displays a hierarchical view of tables, views, stored procedures, and other database items. This designer is available by default when creating a new query or opening an existing one.

There are three ways to specify a query in SSRS: build interactively, type or paste a query, or import an existing query from a file or report. The text-based query designer supports two modes: Text Type Transact-SQL commands that select data from the data source, and Stored Procedure, which allows you to choose from a list of stored procedures.

The graphical query designer provides built-in support for grouping and aggregates to help you write queries that retrieve only summary data. This designer supports the GROUP BY clause, DISTINCT keyword, and aggregates such as SUM and COUNT.

To write a query in the text-based query designer, you can type or paste Transact-SQL text directly. This mode provides full support for the Transact-SQL language, including grouping and aggregates.

You can also specify a stored procedure for a dataset query by setting the Stored Procedure option in the Dataset Properties dialog box, or by selecting a stored procedure or table-valued function in the relational query designer. This will prompt you for input parameter values when you run the query.

Here are the three ways to specify a query in SSRS:

Only the first result set that is retrieved for a stored procedure is supported, so if a stored procedure returns multiple result sets, only the first one is used.

SSRS paginated reports are optimized for printing or PDF generation, providing pixel-perfect layouts.

Typical use cases for paginated reports include operational reports like sales invoices or profit and loss statements.

You can migrate your existing paginated reports in the Report Definition Language (RDL) using the RDL Migration Tool written by Microsoft and available on GitHub.

Publishing paginated reports in Power BI requires a Premium subscription, and the upcoming Premium per User license model should be considered.

There are currently few limitations that need to be circumvented to migrate SSRS reports to Power BI.

To mitigate one of these limitations, you can enable suppression of error messages for recovery model change by running the following SQL script: sp_configure ‘suppress recovery model errors’, 1 ; GO RECONFIGURE; GO