Report Builder With Dynamic SQL

Problem: You wish to create a report using Report Builder which utilises a text type dataset with dynamic SQL

Declare your sql variable in the query text
Write TSQL which populates the variable using dynamic sql and execute the variable

Manually create a parameter and map this parameter within the dataset properties.

You wont be able to run this query within the query designer and it wont recognise any fields therefore you will need to manually add each field to the dataset.
DECLARE @sql VARCHAR(MAX) = ‘SELECT ‘ + cast(@i as varchar(10)) + ‘ as field1’
EXEC (@sql)

Create a parameter called @i
Map @i to @i

Add a new query field called field1 to the dataset



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: