How to set the data driven dataset

 

 

Until the dataset is defined the column Dataset Field has no values populated.

 

 

       empty values in the Dataset Field columns

                 

 

 

 

 

 

 

Setting Data Driven Dataset

 

The Data Driven Dataset button is only enabled if Data Driven Subscription is selected.

 

 

 

 

 

 

On clicking the Data Driven Dataset button the Dataset windows opens (see screenshot below). you need to set the data source and query

 

 

After setting the data source and query

 

1.Press Validate Query button. If the query is successful the Query Status textbox shows OK. If you do not see OK, you need to fix the query/data source.

2.Press Set Dataset button to set the dataset. The Dataset Fields columns are now be populated (see first screenshot on this page).

 

 

Note: it is recommended to use a shared data source rather than a custom data source. The reason is less maintenance is required. If the account password changes you will need to reset all custom data source data driven subscriptions. Where are with a shared data source there will only one item to change.

 

 

 

 

 

 

Setting Shared data source

 

The data source item is shown differently depending if SSRS is native or integrated. The option to copy the text to the clipboard is for information only.

 

 

 SSRS native mode.

 

 

 

 

 SSRS integrated Mode.

 

 

 

 

 

 After pressing the ellipsis button a new window opens up to select the shared data source.

 

 

 

 

 

 

 

 

 

 Custom data source example

 This is the same window for SSRS native and integrated

 

 

 

Note in the above example

 

you can see connection string Datasource=.\s16;.....

 

Best practice is to select the sever name  change .\s16 to   <server name>\s16 in this example

Additionally the query has select *..... it is recommended not to use select * as column orders can change,

why select all columns if only a few columns are needed for the data driven subscription?