Report Builder With Dynamic SQL

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

Solution:
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.
Example
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

Advertisements

Tags:

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s


%d bloggers like this: