This field is missing from the returned result set from the data source.

This warning

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 🙂

Advertisements

About Suresh Kumar D

Hardcore SQL Developer and aspiring Database Architect.
This entry was posted in SSRS. Bookmark the permalink.

3 Responses to This field is missing from the returned result set from the data source.

  1. Matt Sebasstian says:

    This worked for me!! Thank you very much!

    Matt

    • Alwang'a Harrison says:

      Hi
      This has not worked for me. Kindly look at my code ad advice accordingly

      ALTER PROCEDURE [dbo].[spRbssReturnsSummaryAnalysisList]
      @spParamLicenceCategoryID varchar(MAX) = null,/*This contains the report Category IDs*/
      @spParamlicenceTypeID varchar(MAX) = null,/*This contains the licence Type IDs*/
      @spParamcustomerID varchar(MAX) = null,/*This contains the customer IDs*/
      @spParamReportCategoryID varchar(MAX) = null,/*This contains the report Category IDs*/
      @spParamReportDueTypeID varchar(MAX) = null,/*This contains the report reporting periods*/
      @spParamYear varchar(MAX) = null,/*This contains the report year to be displayed*/
      @spParamReportID varchar(MAX) = null,/*This contains the report IDs*/
      @spParamReturnsColumnID varchar(MAX) = null,/*This contains the report columns to be displayed/Analysed*/
      @spParamReturnsStatusID varchar(MAX) = null,/*This contains the report Status*/
      @UserID varchar(100) = null /*This contains the logged in user ID*/
      AS

      DECLARE @CustomerID varchar(MAX);
      DECLARE @CustomerName varchar(250);
      DECLARE @ReportCategoryName varchar(150);
      DECLARE @ReportHeaderID varchar(50);
      DECLARE @ReportDate varchar(20);
      DECLARE @ReportCategoryID varchar(10);
      DECLARE @ReportID varchar(10);
      DECLARE @IsMonthly varchar(5);
      DECLARE @IsQuarterly varchar(5);
      DECLARE @IsHalfYearly varchar(5);
      DECLARE @IsYearly varchar(5);
      DECLARE @IsEveyThreeYears varchar(5);
      DECLARE @IsHalfDecade varchar(5);
      DECLARE @ReturnsColumnFlagFormulae varchar(MAX);
      DECLARE @ReturnsColumnFormulae varchar(MAX);
      DECLARE @ReturnsColumnName varchar(150);
      DECLARE @FetchedValue nvarchar(MAX);
      DECLARE @paramdec NVARCHAR(MAX);
      DECLARE @Query NVARCHAR(MAX);
      DECLARE @CheckExistingQuery varchar(MAX);
      DECLARE @OnlineReportsColumnName varchar(100);
      DECLARE @ReferenceID varchar(MAX);
      /*We fetch all the required entries a Custore is expected to have */

      DELETE FROM OnlineReports WHERE UserID = @UserID

      DECLARE Returns_cur CURSOR LOCAL FOR (
      SELECT
      Customer.No_ AS CustomerID
      ,Customer.Name AS CustomerName
      ,ReportNameCategory.ReportCategoryName
      ,ReportHeader.ReportHeaderID
      ,ReportHeader.ReportDate
      ,ReportHeader.ReportCategoryID
      ,ReportHeader.ReportID
      ,ReportDueTypes.IsMonthly
      ,ReportDueTypes.IsQuarterly
      ,ReportDueTypes.IsHalfYearly
      ,ReportDueTypes.IsYearly
      ,ReportDueTypes.IsEveyThreeYears
      ,ReportDueTypes.IsHalfDecade
      FROM ReportHeader
      JOIN [CMA LIVE$Customer] Customer ON Customer.No_ = ReportHeader.CustomerID
      JOIN ReportNameCategory ON ReportNameCategory.ReportCategoryID = ReportHeader.ReportCategoryID
      JOIN ReportStatus ON ReportStatus.ReportStatusID = ReportHeader.ReportStatusID
      JOIN ReportDueTypes ON ReportDueTypes.ReportDueTypeID = ReportNameCategory.ReportDueTypeID
      WHERE ReportHeader.ReportCategoryID IN (SELECT Val from dbo.fn_String_To_Table(@spParamLicenceCategoryID,’,’,1))
      AND ReportHeader.ReportStatusID IN (SELECT Val from dbo.fn_String_To_Table(@spParamReturnsStatusID,’,’,1))
      AND ReportHeader.LicenceCategoryID IN (SELECT Val from dbo.fn_String_To_Table(@spParamLicenceCategoryID,’,’,1))
      AND ReportHeader.LicenceTypeID IN (SELECT Val from dbo.fn_String_To_Table(@spParamlicenceTypeID,’,’,1))
      AND ReportHeader.CustomerID IN (SELECT Val from dbo.fn_String_To_Table(@spParamcustomerID,’,’,1))
      AND DATEPART(yyyy,ReportHeader.ReportDate) IN (SELECT Val from dbo.fn_String_To_Table(@spParamYear,’,’,1))
      )
      OPEN Returns_cur;
      FETCH NEXT FROM Returns_cur INTO @CustomerID,@CustomerName,@ReportCategoryName,@ReportHeaderID,@ReportDate,@ReportCategoryID,@ReportID,@IsMonthly,@IsQuarterly,@IsHalfYearly,@IsYearly,@IsEveyThreeYears,@IsHalfDecade;
      WHILE @@FETCH_STATUS = 0
      BEGIN

      /******************* BEGINING OF RETURNS SUMMARY COLUMNS CURSOR *******************
      *******************We now get the required Columns to be displayed****************/
      DECLARE ReturnsSummaryColums_cur CURSOR LOCAL FOR (
      SELECT
      ReturnsSummaryAnalysisColumn.ReturnsColumnFlagFormulae
      ,ReturnsSummaryAnalysisColumn.ReturnsColumnFormulae
      ,ReturnsSummaryAnalysisColumn.ReturnsColumnName
      ,ReturnsSummaryAnalysisColumn.OnlineReportsColumnName
      FROM ReturnsSummaryAnalysisColumn
      WHERE ReturnsSummaryAnalysisColumn.ReturnsColumnID IN (SELECT Val from dbo.fn_String_To_Table(@spParamReturnsColumnID,’,’,1))
      AND ReturnsSummaryAnalysisColumn.ReportID = @ReportID)
      OPEN ReturnsSummaryColums_cur;
      FETCH NEXT FROM ReturnsSummaryColums_cur INTO @ReturnsColumnFlagFormulae,@ReturnsColumnFormulae,@ReturnsColumnName,@OnlineReportsColumnName;
      WHILE @@FETCH_STATUS = 0
      BEGIN
      SELECT @ReturnsColumnFormulae = REPLACE(@ReturnsColumnFormulae,’@ReportHeaderID’,@ReportHeaderID)
      SET @Query = @ReturnsColumnFormulae;
      SET @paramdec = N’@FetchedValue nvarchar(50) output’
      EXECUTE sp_executesql @Query, @paramdec,@FetchedValue OUTPUT
      SELECT @FetchedValue

      /******************* BEGINING OF RETURNS SELECTED VALUES CURSOR *******************
      *******************We now insert the selected values in a given row****************/
      DECLARE existingReturnsSummary_cur CURSOR LOCAL FOR (
      SELECT ReferenceID FROM OnlineReports WHERE CustomerID = @CustomerID
      AND ReferenceID = @ReportHeaderID)
      OPEN existingReturnsSummary_cur;
      FETCH NEXT FROM existingReturnsSummary_cur INTO @ReferenceID;
      BEGIN
      IF(@ReferenceID = ” OR @ReferenceID IS NULL)
      BEGIN
      SET @CheckExistingQuery = ‘INSERT INTO OnlineReports
      (UserID
      , CustomerID
      , ReferenceID
      ,ValueOne
      ,ValueTwo
      ,ValueThree
      ,ValueFour
      ,ValueFive
      ,’+ @OnlineReportsColumnName +’)
      VALUES
      (”’+@UserID+”’
      ,”’+@CustomerID+”’
      ,”’+@ReportHeaderID+”’
      ,”’+@CustomerName+”’
      ,”’+@ReportDate+”’
      ,”’+@ReportCategoryID+”’
      ,”’+@ReportCategoryName+”’
      ,”’+@ReportID+”’
      ,”’+@FetchedValue+”’
      )’
      EXEC (@CheckExistingQuery);
      /*INSERT INTO OnlineReports (CustomerID,ValueTwo,ValueThree) VALUES(@CustomerID,@ReferenceID,’WHEN Null’)*/
      END
      ELSE
      BEGIN
      SET @CheckExistingQuery = ‘UPDATE OnlineReports SET ‘+ @OnlineReportsColumnName +’ = ‘+ @FetchedValue +’ WHERE CustomerID = ”’+@CustomerID +”’ AND ReferenceID = ‘+ @ReportHeaderID
      EXEC (@CheckExistingQuery);
      END
      SET @ReferenceID = ”;
      END
      CLOSE existingReturnsSummary_cur;
      DEALLOCATE existingReturnsSummary_cur;
      /******************* END OF RETURNS SELECTED VALUES CURSOR ******************/

      SET @ReturnsColumnFlagFormulae = ”;
      SET @ReturnsColumnFormulae = ”;
      SET @ReturnsColumnName = ”;
      SET @OnlineReportsColumnName = ”;
      FETCH NEXT FROM ReturnsSummaryColums_cur INTO @ReturnsColumnFlagFormulae,@ReturnsColumnFormulae,@ReturnsColumnName,@OnlineReportsColumnName;

      END
      CLOSE ReturnsSummaryColums_cur;
      DEALLOCATE ReturnsSummaryColums_cur;
      /******************* END OF RETURNS SUMMARY COLUMNS CURSOR ******************/
      SET @CustomerID = ”;
      SET @CustomerName = ”;
      SET @ReportCategoryName = ”;
      SET @ReportHeaderID = ”;
      SET @ReportDate = ”;
      SET @ReportCategoryID = ”;
      SET @ReportID = ”;
      SET @IsMonthly = ”;
      SET @IsQuarterly = ”;
      SET @IsHalfYearly = ”;
      SET @IsYearly = ”;
      SET @IsEveyThreeYears = ”;
      SET @IsHalfDecade = ”;
      FETCH NEXT FROM Returns_cur INTO @CustomerID,@CustomerName,@ReportCategoryName,@ReportHeaderID,@ReportDate,@ReportCategoryID,@ReportID,@IsMonthly,@IsQuarterly,@IsHalfYearly,@IsYearly,@IsEveyThreeYears,@IsHalfDecade;
      END
      CLOSE Returns_cur;
      DEALLOCATE Returns_cur;

      SELECT
      CustomerID
      ,ReferenceID
      ,ValueOne
      FROM OnlineReports

      The Data is successfully inserted into the OnlineReports Table but I can not Display it on my report

      Regards

      Harrison

  2. Hi!
    I receive the warning when I export the invoice with AIF:

    The dataset ‘SalesInvoiceDS’ contains a definition for the Field ‘GiroType’.
    This field is missing from the returned result set from the data source.

    I’ve seen that the field ‘GiroType’ depends on the configuration key ‘CustGiro’ that is disabled.
    Have you an idea?
    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s