Troubleshooting Dynamic SSRS Queries

The Problem

In my daily work I have to work with a lot of SSRS (SQL Server Reporting Services) reports that have either stored procedures, queries, or dynamic queries to get all the results.

Troubleshooting dynamic SSRS queries can sometimes be difficult. That is especially the case when you’re dealing with multiple hundreds of lines of code all generated dynamically.

An example of such an error is:

Because the query is generated during execution using all kinds of parameters, you don’t know what the query looks like exactly. Debugging this query would be pretty difficult and tedious, but there is an easy way to get the compiled query.

How does a dynamic query look like

If you never used dynamic queries in SSRS you might not know what that looks like and when it’s used.

A dynamic query in SSRS is an expression in a data set. The expression is built upon execution and can include TSQL code, SSRS fields, etc.

The reason I use this in my reports is that I sometimes need to implement complicated filtering in my reports using the report parameters.

You can filter the data using the “Filters” tab in the data set but that would cause me to generate an extensive amount of data during execution which I’d like to avoid.

When you open a data set with a dynamic query it looks like this

You don’t see much of the query because you need to click the “fx” button on the right to get the following screen:

The “=” sign indicated the start of the expression. For readability reasons, I join every new line with the “&” sign.

I could have used one big line of code but this makes it easier to read.

During execution, I use several parameters to filter out values. That can be seen in the last two lines in the expression in the image above.

Using this method of filtering cuts down the amount of data I retrieve during execution.

The solution

How can I look at the query after it’s executed? The solution is to create a text box and enter the following expression:

This expression will display the query of the data set in the text box during execution. This doesn’t solve the problem yet because we’re still dealing with the problem that the query fails during execution.

How to display the query without executing it? The solution is really simple but it requires some understanding of how SSRS compiles the expression during execution.

The fact is that the query expression is compiled as a single line of code.

To display the code we can use the “–” to create a comment.

This will render the query but it will not execute our query:

There you have it, your compiled query. The next step would be to copy this code and paste it to SSMS or SQL OPS to further debug the query.