Use a conditional value in a Reporting Services dataset for debugging

Question

In the SQL queries for my reporting services datasets, I'd like to be able to use the following pattern:

-- Used to enable/disable diagnostic statements
declare @DEBUG bit
set @DEBUG = 0

-- Get current date/time.
declare @now datetime; set @now = getdate()
if @DEBUG = 1 select @now as now

-- Create a table to contain the chart values for the last 30 days.
declare @reportValues table
(
    reportDate datetime,
    x int
)

-- Populate the table with the last 30 days.
declare @counter int; set @counter = 0
while @counter < 5
begin
    insert into @reportValues select dateadd( day, -@counter, @now ), @counter
    set @counter = @counter + 1
end
if @DEBUG = 1 select * from @reportValues

select sum(x) as total from @reportValues

The problem is, Reporting Services ignores the IF statements and "select @now as now" for the report's values, instead of the main query.

Is there a way to accomplish this pattern that gets around this RS limitation?

Update: If you go to the "SET FMTONLY" help page on the MSDN, someone has already noted this problem in a comment at the bottom of the page, and that turning off this option fixes this problem.

Answer 1

You would be better off writing your query in a stored procedure and calling the sproc from RS instead of having to rely on RS's interpretation of your text query. Then you should be able to pass in your @Debug parameter and check on it without issue.

Edit: Try this out instead and see if it recognizes the If statement -

-- Used to enable/disable diagnostic statements
declare @DEBUG bit
set @DEBUG = 0

-- Get current date/time.
declare @now datetime; set @now = getdate()
IF @DEBUG = 1 
BEGIN
    select @now as now
END
ELSE
BEGIN
    select * from ... -- Main query elided
END

Edit #2: Since the above did not seem to help, my next suggestion would be to attempt something like a SELECT CASE (This is assuming that you are only pulling one column and calling it "now"):

-- Used to enable/disable diagnostic statements
declare @DEBUG bit
set @DEBUG = 0

-- Get current date/time.
declare @now datetime; set @now = getdate()
SELECT
    CASE @Debug
        WHEN 1 THEN @now
        WHEN 0 THEN (Perform Your Select Inside These Parentheses)
    END as now

Answer 2

Does the column set vary with @debug?

If the database is SQL Server, you might try the fmtonly kludge:

declare @fmtonlyon bit
select @fmtonlyon = 0
if 1 = 0 select @fmtonlyon = 1
if @fmtonlyon = 1 set fmtonly off

/* query body */

if @fmtonlyon = 1 set fmtonly on

It forces full evaluation. Might or might not work in your situation.

Alternatively, replace the debug SELECT statements with PRINT.

Answer 3

I am still stuck...When I use SET FMTONLY OFF, it gives me an error the construct SET is not supported....any ideas?