What DataSet Query Text Is Executed?

  • Article

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: 

  • Parameterized or regular datasets
    In order to see the underlying query command text in the report, add a textbox with the following RDL expression, utilizing the built-in DataSets collection and the CommandText property.  This can be particularly useful when you use an expression-based query command text, as the CommandText property will contain the evaluated expression result. 
       =DataSets!DataSetName.CommandText

    If you use report parameters bound to query parameters in your dataset, you can display those values by accessing the built-in Parameters collection.  For example, assuming P1 is a multi-valued report parameter, we utilize the Join function to show the current parameter values as comma-separated list:
       ="P1's values: " & Join(Parameters!P1.Value, ", ")
     

  • Report Model based datasets 
    Using Report Builder 2.0 with a shared data source on your report server that points to an existing report model is a very powerful combination.  It enables users to create quite complex queries easily that would be rather difficult to write as actual SQL or MDX queries.  Similar to above, you can use the RewrittenCommandText property to get the model-based query with parameter values rewritten as literals:
       =DataSets!DataSetName.RewrittenCommandText

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.