none
ghost fields in ssrs 2008 report

    Question

  • I have a dataset in ssrs 2008 report that gets its fields from a SQL stored procedure. This stored procedure retrieves data from a relational database. I removed one field named 'Roster Shift' from the stored procedure output query as this field get dropped from the database table itself. This field was never used in the report but was a part of the dataset fields there. To my surprise, the report still runs fine although I have not refreshed the dataset fields after this change and 'Roster Shift' is still showing under the dataset. To my understanding, it is sort of a ghost field under the dataset as the stored procedure dose not pass it to the report.

    Can anybody please explain this behaviour of SSRS as such fields can cause confusion during maintenance of such reports at a later stage. Is it fine to leave the ghost fields if the report seems to run fine?

     
    • Edited by MKG2011 Thursday, November 29, 2012 4:33 PM
    Thursday, November 29, 2012 4:33 PM

Answers

  • Hi,

    Nice title :)

    This behavior is be design and your report will continue to work. Reporting Services will have that field in the data set, but in the report, it would be all blank. As a good practice, I would recommend that you remove this field by going to the data set properties and deleting the field as shown below.


    Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

    Thursday, November 29, 2012 6:45 PM

All replies

  • Hi,

    Nice title :)

    This behavior is be design and your report will continue to work. Reporting Services will have that field in the data set, but in the report, it would be all blank. As a good practice, I would recommend that you remove this field by going to the data set properties and deleting the field as shown below.


    Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

    Thursday, November 29, 2012 6:45 PM
  • Hi Mkg2011,

    From your description, it is unusual that you can execute the stored procedure in Query Designer but the data fields cannot be retrieved when click the Refresh Fields button. I also did a test with a similar stored procedure and it works fine. Please make sure to click the OK button after clicked the Refresh Fields button in the Dataset Properties window, and then check the dataset in the Report Data pane again.

    If the issue still exists, please try to set FMTONLY, when you define a data source SSRS calls your procedure with
    SET FMTONLY ON;
    Basically, this allows it to get the list of fields, without running the entire procedure. But this causes problems when we use dynamic sql, and we have to update the field list manually.

    However if you put
    SET FMTONLY OFF;
    at the top of your procedure, you effective override what SSRS is doing - the full procedure is executed and the field list is returned just fine. Here has thread that is similar to yours, please see:
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/a7d74cd6-e8fa-4fa7-9c5d-7d786db3bda4

    Hope this helps.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Friday, November 30, 2012 2:30 AM
  • Hi Mkg2011

    From your description, it is unusual that you can execute the stored procedure in Query Designer but the data fields cannot be retrieved when click the Refresh Fields button. I also did a test with a similar stored procedure and it works fine. Please make sure to click the OK button after clicked the Refresh Fields button in the Dataset Properties window, and then check the dataset in the Report Data pane again.

    Charlie,

    Please see the original poster's description below-  the data set was never refreshed, so this behavior is normal. 

     I have not refreshed the dataset fields after this change and 'Roster Shift' is still showing under the dataset. To my understanding, it is sort of a ghost field under the dataset as the stored procedure dose not pass it to the report. 



    Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

    Friday, November 30, 2012 3:31 AM
  • Hi Mkg2011

    From your description, it is unusual that you can execute the stored procedure in Query Designer but the data fields cannot be retrieved when click the Refresh Fields button. I also did a test with a similar stored procedure and it works fine. Please make sure to click the OK button after clicked the Refresh Fields button in the Dataset Properties window, and then check the dataset in the Report Data pane again.

    Charlie,

    Please see the original poster's description below-  the data set was never refreshed, so this behavior is normal. 

     I have not refreshed the dataset fields after this change and 'Roster Shift' is still showing under the dataset. To my understanding, it is sort of a ghost field under the dataset as the stored procedure dose not pass it to the report. 



    Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com


    Hi Shahfaisal,

    Thank you for point it out.

    Regards,
    Charlie Liao


    Charlie Liao
    TechNet Community Support

    Friday, November 30, 2012 4:38 AM
  • thanks Shahfaisal! this is very well explained. :)
    Tuesday, December 04, 2012 6:44 PM