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’s 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 how 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 how that looks like and when it’s used.

A dynamic query in SSRS is actually an expression in a data set. The expression is build upon execution and can include TSQL code, SSRS fields etc.
The reason I use this in my reports is because 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 to extensive amount of data during execution which I’d like to avoid.

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

You don’t see much of the query because you needto 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 on the last two lines in the expression 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 you it requires some understanding 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 out 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.