The dataset ‘dataset1’ contains a definition for the Field ‘column1’. This field is missing from the returned result set from the data source.
is very common error and reason behind it is really simple. The report object is referring to a field which is not available in the dataset. Most of the time the developer might be referring to a wrong field name due to spelling mistake.
So when one of my colleague told me that he is facing this error, I asked him to check whether the column name in the dataset and the report object is matching. He came back saying it does match. Then I asked him to check whether the dataset query is returning the correct column. “Yes” was the reply.
I could not understand how this was possible. So I asked him to send me the RDL. I ran the report in BIDS and I too faced the same issue. I checked the field name in the dataset columns, dataset query and the report object and everything was matching.
This dataset was populated by a mix of static T-SQL statements and dynamic T-SQL statements. Thought there might be some condition in which the field was not populated. So I copied the dataset statements and ran it in SSMS passing the same set of parameters used in BIDS. Everything looked perfect. It was fetching the referred column.
Then I traced the report run (from BIDS) thru Profiler. Took the command and ran it in SSMS. Still it brought the required field. But BIDS was throwing the same warning.
After spending more than couple of hours, I could figure out the cause. When tracing thru Profiler, I observed that the time taken to execute the query was considerably less compared to running it in SSMS. That gave me a hint that some part of the query might not be running.
I reviewed the dataset commands once again and found that above the block where the required field was populated, there was a single row comment detailing the purpose of the block. I just removed the single row comment (whole line) the warning went off.
My understanding is that for same reason the BIDS was considering the comment line and the below block as one single row and did not execute the block which resulted in the field being missed 🙂