In a previous posting, I discussed dataset execution order in Reporting Services. In this posting, I will provide a few tips for situations where you or your report users are wondering what is the actual query executed by Reporting Services.
While you can accurately answer this question using a profiler connected to the data source machines or in some cases by opening the dataset properties dialog, there are other options directly available from within reports. You will probably encounter the following main situations:
You can make this more sophisticated by only dynamically showing the query text in the report, e.g. based on a parameter or by toggling the visibility using another textbox:
More information about all built-in global collections accessible from RDL expressions is available in SQL Server Books On Line. Note that some of the server related properties don't have values in local preview, only when connected to a server in Report Builder 1.0 / 2.0 or running directly on a server.
The CommandText property value always represents the original command text as defined in the RDL dataset. The RewrittenCommandText property value is populated at runtime from data extensions that support the IDbCommandRewriter interface. This interface allows Reporting Services to read the CommandText back from the data extension after it has merged the original command text and parameter values. If a data extension doesn't support this interface, RewrittenCommandText will be null/Nothing. Currently the built-in report model data source supports this interface, although it could also be implemented by any custom data extension.