Show/Hide Toolbars

SSRS Subscription Manager Help File

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?