Friday, October 26, 2007

Displaying the query SQL in your Reports

I was recently asked by a co-worker the following question:
"Is there a programmatic way to pull the dataset SQL and display it on a [Reporting Services 2005] report?"

With a little bit more background information I was able to help her out. Some important facts that needed to be pointed out:


  • what flavor or DBMS are you using (hopefully SQL Server 2005)?

  • Are you allowed to create and use stored procedures as the backend of your reports?


With those facts straight, we can approach this problem with the following solution (I am sure there are other, perhaps more elegant, ways to solve this, so add your comments).

UPDATED:
If Stored Procedures are not being used, then you can simply include a textbox in your report body with the following expression:
=Datasets!mydatasetname.CommandText

If you'd like, you can set the visibility of this textbox based on another textbox's toggle property, and set the text property for that 2nd textbox as "view report query" with a blue color so it looks like a link or action of some sort.

If you use stored procedures, then read on:

We must adopt a few "standards" regarding reporting and SQL, when approching the problem with this solution.

First, you will want to use Stored Procedures (sproc) for all your report datasets. This allows you to do some extra things with your sql code that you can't do in the report dataset designer.

Second, you will want to use dynamic sql, since you're wanting to output the sql statement as part of your query results (e.g., in a column of your dataset).

SQL Server contains a stored procedure called "sp_executesql" that allows you to execute sql statements much in the same way as using EXEC(@sql). Except, it adds a layer of protection against SQL Injection Attacks by introducing parameterized queries. So in fact what you'd have for your stored procedure is something like the sproc I am including at the bottom of this post.

Notice that we have 2 ways of getting the sql statement: as a rpt developer, you may choose to display the "DebugSql" column in the report, and as SQL developer, you can choose to execute your sproc with the @debug parameter set to 1, so it only prints the sql that would execute. This offers some flexibility for all.

Either way, in the report dataset designer, you can just specify query type of Text (not StoredProcedure, it gets kinda buggy when dealing with dynamic sql). Then for the query text you'll enter something like this:


EXEC dbo.GetMyReportData @param1, @param2

(no need to include the @debug parameter, since it has a default).

There's an excellent whitepaper titled "The Curse and Blessings of Dynamic SQL" by Erland Sommarskog (http://www.sommarskog.se/dynamic_sql.html), where he discusses all the possible ways of doing dynamic sql, including some best practices (like the sp_executesql sproc). I would highly recommend taking a look at this if you need more information on the topic or would like to understand more about the sp_executesql procedure (it's also documented in the SQL Server Books Online at http://msdn2.microsoft.com/en-us/library/ms188001.aspx).

Here is the sample stored procedure SQL, enjoy!


IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID('dbo.GetMyReportData')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE dbo.GetMyReportData
END
GO

CREATE PROCEDURE dbo.GetMyReportData
@param1 NVARCHAR(25)
, @param2 NVARCHAR(25)
, @debug BIT = 0
AS
BEGIN

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
SELECT
col1 AS Column1
, col2 AS Column2
, col3 AS Column3
, @N_debugSql AS DebugSql
FROM
myTable WITH(NOLOCK)
WHERE
col1 = @N_param1
AND col2 = @N_param2'

DECLARE @parameterList NVARCHAR(MAX)
SET @parameterList = N'
@N_param1 NVARCHAR(25)
, @N_param2 NVARCHAR(25)
, @N_debugSql NVARCHAR(MAX)'

IF @debug = 0
BEGIN
EXECUTE sp_executesql @sql, @parameterList
, @N_param1 = @param1
, @N_param2 = @param2
, @N_debugSql = @sql
END
ELSE
BEGIN
PRINT (@sql)
END

END

No comments: