Troubleshooting Report Viewing Errors in Azure DevOps: A Practical Guide

Table of Contents

Reporting is a critical function within Azure DevOps Server (formerly Team Foundation Server or TFS), allowing teams to gain valuable insights into project progress, build quality, and test results. These reports are typically served through SQL Server Reporting Services (SSRS), which queries data stored in the Azure DevOps/TFS data warehouse and Analysis Services cube. However, users occasionally encounter errors when attempting to view these reports, preventing access to essential project metrics and dashboards. This guide addresses specific errors related to incorrect data source configurations within SSRS, providing a clear path to resolution.

Understanding the reporting architecture in Azure DevOps Server is key to effective troubleshooting. Data from the operational Azure DevOps Server database is periodically processed and moved into a relational data warehouse database. From the warehouse, a multidimensional cube is built and processed in SQL Server Analysis Services (SSAS). SSRS then connects to both the relational warehouse database and the SSAS cube using defined data sources to render the reports. Incorrect configuration at any point in this pipeline can lead to report viewing failures.

Identifying Common Report Viewing Errors¶

When attempting to access a report through the Azure DevOps Server web portal or directly via the SSRS Report Manager URL, users might encounter error messages instead of the expected report visualization. Two common errors specifically point towards issues with the configured data sources in SSRS. These errors indicate that the reporting service is unable to properly execute the queries required to fetch the report data, often due to expecting one type of data source (like a relational database) but being configured for another (like an analytical database), or vice versa. Recognizing these specific error messages is the first step in diagnosing the problem.

The error messages displayed to the end-user are often generic, indicating a processing failure without specific technical details. However, accessing the full error details, either by enabling remote errors in SSRS or viewing the report directly on the report server console, reveals more diagnostic information. This detailed information is crucial for pinpointing the root cause of the report processing failure and distinguishing between various potential issues, such as connectivity problems, permissions errors, or, as in this case, incorrect data source types. The two errors discussed here are distinct and point to specific data source misconfigurations that prevent query execution.

Error Scenario 1: Relational Query Against an OLAP Source¶

One common error occurs when a report designed to query the relational data warehouse attempts to execute its SQL queries against a data source configured as an Analysis Services cube. The query engine expects standard SQL syntax but receives errors because the target is expecting Multidimensional Expressions (MDX). This mismatch prevents the report from retrieving the necessary data, leading to a processing error.

The generic error message a user might see is:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set ‘dsLastProcessedTime’. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

When remote errors are enabled or viewing the report on the server console, the detailed error provides a critical clue: Query (3, 10) Parser: The syntax for ‘=’ is incorrect. This specific syntax error, mentioning the parser, strongly suggests that a SQL query containing an equals sign (=) is being parsed by an MDX engine, which does not interpret this syntax in the same way. The dataset ‘dsLastProcessedTime’ is typically a simple query against the relational warehouse to check the last time the data was processed, which requires a standard SQL connection.

Error Scenario 2: OLAP Query Against a Relational Source¶

Conversely, the second common error arises when a report designed to query the SSAS cube using MDX attempts to execute these queries against a data source configured as a relational database. The relational database engine cannot parse MDX syntax, resulting in a query execution failure. Reports that use parameters like ‘IterationParam’ often query the Analysis Services cube, as dimensional data like iterations and areas are structured within the cube for analytical querying.

The user-facing error for this scenario is:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset ‘IterationParam’. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

Enabling remote errors or viewing on the console reveals the more descriptive message: Incorrect syntax near ‘Measures’. The term ‘Measures’ is fundamental to MDX queries used against an Analysis Services cube. Seeing a syntax error near this term indicates that an MDX query is being fed into a SQL engine, which does not understand the concept of MDX measures, confirming the data source type mismatch.

Root Cause: Incorrect Data Source Types in SSRS¶

The underlying reason for both of these specific errors is a misconfiguration of the data source types defined within SQL Server Reporting Services for the Azure DevOps Server reports. Azure DevOps Server reporting relies on two primary data sources in SSRS:

1. TfsReportDS: This data source is used for reports that query the relational Azure DevOps Server data warehouse database. Reports using this data source execute standard SQL queries. Consequently, this data source must be configured with a Data Source Type of Microsoft SQL Server.
2. TfsOlapReportDS: This data source is used for reports that query the Azure DevOps Server Analysis Services cube. Reports using this data source execute MDX queries. Therefore, this data source must be configured with a Data Source Type of Microsoft SQL Server Analysis Services.

The errors described occur when these configurations are swapped:

• ERROR 1 (Query (3, 10) Parser: The syntax for ‘=’ is incorrect) happens when TfsReportDS is incorrectly set to “Microsoft SQL Server Analysis Services”. The relational SQL query fails because the SSAS data source type expects MDX.
• ERROR 2 (Incorrect syntax near ‘Measures’) happens when TfsOlapReportDS is incorrectly set to “Microsoft SQL Server”. The MDX query fails because the SQL Server data source type expects SQL.

This misconfiguration prevents SSRS from correctly interpreting the queries sent by the reports, as each data source type prepares the query execution environment for a specific query language (SQL for Microsoft SQL Server, MDX for Microsoft SQL Server Analysis Services). Correcting these data source types is essential for the reports to connect to the appropriate data store and execute their queries successfully.

Resolution: Correcting SSRS Data Source Types¶

Fortunately, resolving these errors involves a straightforward corrective action within the SSRS Report Manager web portal. The process requires administrative access to the SSRS instance used by Azure DevOps Server. By navigating to the data source configurations and verifying/correcting the ‘Data Source Type’ setting for both TfsReportDS and TfsOlapReportDS, the reporting functionality can be restored.

Here are the detailed steps to correct the data source types:

1. Access the SSRS Web Portal: Open a web browser and navigate to the URL for the SQL Server Reporting Services Report Manager. The default URL is typically http://[YourServerName]/Reports (or https if configured for SSL). If you are unsure of the specific URL for your Azure DevOps Server deployment, consult your Azure DevOps Server administrator or check the Reporting Services Configuration Manager on the server hosting SSRS. The URL might differ based on installation specifics (e.g., using a specific port or virtual directory).

2. Locate the Azure DevOps Reports Folder: Once in the Report Manager, navigate through the folder structure to find the reports related to Azure DevOps Server. These are typically organized under a folder named “TFSReports”, though the exact path might vary depending on how reporting was configured or migrated. Within this main folder, you will usually find subfolders corresponding to Team Projects and a folder or section containing the shared Data Sources.

3. Configure TfsReportDS: Find and click on the shared data source named TfsReportDS. This will open the data source’s configuration page. On this page, locate the ‘Data Source Type’ setting. Ensure that the selected type is Microsoft SQL Server. This is crucial for allowing SSRS to send standard SQL queries to the Azure DevOps Server data warehouse database. Verify that the connection string and credentials are also correct, pointing to your Azure DevOps Server data warehouse database.

4. Configure TfsOlapReportDS: Return to the folder containing the shared data sources. Find and click on the shared data source named TfsOlapReportDS. This data source is intended for querying the Analysis Services cube. On its configuration page, ensure that the ‘Data Source Type’ is set to Microsoft SQL Server Analysis Services. This setting allows SSRS to communicate with the SSAS instance and execute MDX queries against the cube. Again, confirm the connection string points to your Analysis Services instance and the cube, and that the credentials are valid.

5. Apply Changes and Test: After verifying and correcting the Data Source Type for both TfsReportDS and TfsOlapReportDS, remember to save the changes. Important: You will likely need to re-enter the password for the credentials used by each data source before you can save the changes, even if the credentials themselves haven’t changed. This is a security measure in SSRS. Once saved, attempt to view the reports that were previously failing. The reports should now render correctly as SSRS can route the queries to the appropriate data sources with the correct expected query language.

Summary of Errors and Resolutions¶

Here is a quick reference table summarizing the two specific errors and their corresponding data source type misconfigurations and corrections:

Error Seen (Detailed)	Affected Data Source	Incorrect Type Configured	Correct Type Required	Query Language Mismatch
Query (3, 10) Parser: The syntax for ‘=’ is incorrect	TfsReportDS	Microsoft SQL Server Analysis Services	Microsoft SQL Server	SQL query sent to MDX engine
Incorrect syntax near ‘Measures’	TfsOlapReportDS	Microsoft SQL Server	Microsoft SQL Server Analysis Services	MDX query sent to SQL engine

Correcting these settings ensures that TfsReportDS correctly interacts with the relational data warehouse using SQL and TfsOlapReportDS correctly interacts with the SSAS cube using MDX, resolving the query execution failures.

Beyond Data Source Types: Additional Troubleshooting Steps¶

While incorrect data source types are the specific cause of the errors discussed, other issues can also prevent Azure DevOps reports from rendering correctly. If correcting the data source types does not resolve your reporting problems, consider these additional troubleshooting steps:

• Verify Service Account Permissions: Ensure the service accounts used by the SSRS, SSAS, and Azure DevOps services have the necessary permissions to access the data warehouse and Analysis Services databases. These accounts need read access to the relevant databases and the SSAS cube.
• Check Connectivity: Verify network connectivity between the server hosting SSRS and the servers hosting the SQL Server database engine (for the warehouse) and SQL Server Analysis Services. Firewall rules might block necessary ports (default 1433 for SQL, 2383 for SSAS default instance, or dynamic ports).
• Review SSRS, SSAS, and SQL Server Logs: Detailed error information is often recorded in the logs of SQL Server Reporting Services, SQL Server Analysis Services, and the SQL Server Database Engine. Reviewing these logs can provide further clues about connection failures, permission issues, or query timeouts.
• Validate Data Sources in SSRS: Within the SSRS Report Manager, you can click the “Test Connection” button on the data source configuration pages. This helps verify that SSRS can connect to the specified server and database/cube using the configured credentials.
• Check Data Warehouse and Cube Processing Status: Ensure that the Azure DevOps Server data warehouse and Analysis Services cube are processing correctly and are up-to-date. If processing is failing or has stopped, reports will show stale or incomplete data, or may fail if dependent on recently processed data. The processing status can be checked using the Azure DevOps Server Administration Console.
• Review Azure DevOps Server Configuration: In the Azure DevOps Server Administration Console, verify that the Reporting Services and Analysis Services settings are correctly configured and pointing to the right instances and databases.

Troubleshooting reporting issues in Azure DevOps Server involves understanding the interplay between SQL Server, SSAS, and SSRS. While the specific data source type misconfigurations discussed here lead to distinct query parsing errors, a systematic approach covering connectivity, permissions, and service health is crucial for resolving other potential reporting problems.

Diagram of Data Flow in Azure DevOps Reporting¶

Understanding how data flows from Azure DevOps Server to the reporting layer helps visualize the components involved. Data moves from the operational database to the data warehouse and then to the OLAP cube. SSRS queries both the warehouse and the cube.

mermaid graph LR A[Azure DevOps Server Operational DB] -- ETL Process --> B(TFS Data Warehouse DB) B -- Cube Processing --> C(TFS Analysis Services Cube) D(SQL Server Reporting Services - SSRS) --> E[TfsReportDS] D --> F[TfsOlapReportDS] E -- Queries SQL --> B F -- Queries MDX --> C User([User Web Browser]) -- Views Reports --> D

This diagram illustrates why SSRS needs two different data sources configured with the correct types – one to query the relational database (Warehouse DB) and one to query the multidimensional database (SSAS Cube).

Further Learning¶

For those interested in diving deeper into Azure DevOps Server reporting or SQL Server Reporting Services, there are many resources available. Microsoft Learn provides extensive documentation on configuring and managing SSRS and understanding the Azure DevOps Server data model. Community forums and blogs also offer valuable insights and troubleshooting tips.

Navigating and resolving report viewing errors is a necessary skill for administrators and power users of Azure DevOps Server. By understanding the specific errors caused by incorrect data source types in SSRS and knowing how to correct them via the Report Manager, you can quickly restore reporting functionality. Remember to also consider other potential causes like permissions or connectivity if the data source types are already correctly configured.

Have you encountered these specific errors, or other challenging report viewing issues in Azure DevOps Server or TFS? Share your experiences and troubleshooting tips in the comments below!