When setting up your datasets 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 dataset 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 miss leading, 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.
The solution is to trick the SSRS software by simplifying the SP. Basically perform a select on the specific fields you need with no additional logic or create a table with the exact fields you need with corresponding data types and select from that.
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.
The software should now pick up the fields.
Change the SP back to the way it was before and do not refresh the fields again in the SSRS software and the fields should continue to populate as you need them.