When setting up your data sets in an SSRS report If you are using a complicated stored procedure, i.e. a SP which relies on dynamic SQL, temp tables or finishes with an IF statement, chances are the SSRS report will not be able to figure out what the SP returns. When this happens you will not be able to populate the data set with data.
This happens because the execution plan of the SSRS software isn’t smart enough and won’t be able to determine what fields the SP creates and therefore will not be able to create a means of storing the data on the report end.
Subsequently you’ll see the, often misleading, table below popup.
The solution to stop this from happening is quite simple, but considering how expensive this software is it’s a solution that shouldn’t have to be employed.
Use this dumb SP to populate the dataset in the SSRS report.
In the “Choose a data source and create a query” window as below, click refresh fields.