locked
SSRS Report Missing Data RRS feed

  • Question

  • Hi,

    I have a query which returns data in the relevant columns as expected in query analyzer. However, if I run the same query in a dataset in a SSRS report, two of the columns are not populated. I have literally performed a copy and paste from query analyzer but does not return all the data. I have tried creating a brand new report, therefore confirming there are no filters or hidden columns etc. Has anyone else seen this before? I am running BIDS for SQL 2008 R2.

    Thanks



    • Edited by Stegzy_M Friday, January 27, 2012 12:17 PM
    Friday, January 27, 2012 11:59 AM

Answers

  • I have finally resolved the problem. I looked at the ANSI settings in Management Studio and hard coded the same options in the query as follows:

    SET CONCAT_NULL_YIELDS_NULL OFF
    SET  QUOTED_IDENTIFIER ON
    SET ANSI_NULL_DFLT_ON ON
    SET ANSI_PADDING ON
    SET ANSI_WARNINGS ON
    SET ANSI_NULLS ON
    SET ARITHABORT ON

    By hard coding these settings the query was able to render all of the data in the report. 

    Stuart

    • Marked as answer by Stegzy_M Friday, February 10, 2012 5:12 PM
    Friday, February 10, 2012 5:12 PM

All replies

  • Does your query actually read from a view.  If so, might it be doing some filtering.  For example:

    CREATE VIEW dbo.Employees WITH VIEW_METADATA
    AS
    SELECT FirstName, 	       
           LastName,
           DepartmentName,
           CASE WHEN IS_MEMBER('PrivateRole')= 1 THEN SSNumber 
                ELSE NULL
           END AS SSNumber,
           CASE WHEN IS_MEMBER('PrivateRole')= 1 THEN Birthdate 
                ELSE NULL
           END AS Birthdate
      FROM dbo.Employee 
    


    If so that may be why the columns are unpopulated.  Of course, there are other ways the underlying view could affect the results, including a LEFT OUTER JOIN with filtered rows.

    RLF

     

     

    • Marked as answer by Maggie Luo Monday, February 6, 2012 11:32 AM
    • Unmarked as answer by Stegzy_M Monday, February 6, 2012 11:35 AM
    Friday, January 27, 2012 9:38 PM
  • Hi Russell,

    No the query does not read from a view. If I run the same query in query analyzer it returnas all the relevant data. If I run the same query in SSRS it misses data from some of the columns..

    Thanks

    Monday, February 6, 2012 11:38 AM
  • Does the table you are reading from have column level security? 

    If it does and your SSRS report did not have the rights to those columns I would have expected error 230 "The SELECT permission was denied on the column ...."

    Are you running in a different security context when running the SSRS report and when running the query in SSMS?  

    RLF

    Monday, February 6, 2012 1:47 PM
  • Hi Russell,

    No I am running under the same security context as SSMS.

    Thanks

    Monday, February 6, 2012 2:30 PM
  • Just to be on the safe side, make your query into a stored procedure.

    Stored procedure examples:

    http://www.sqlusa.com/bestpractices2005/storedprocedure/

    http://www.sqlusa.com/bestpractices2008/stored-procedure-parameters/

    Make sure you are running it on the same database. 


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Monday, February 6, 2012 5:25 PM
    Answerer
  • Thanks, but I've already tried that.
    Tuesday, February 7, 2012 1:00 PM
  • I have finally resolved the problem. I looked at the ANSI settings in Management Studio and hard coded the same options in the query as follows:

    SET CONCAT_NULL_YIELDS_NULL OFF
    SET  QUOTED_IDENTIFIER ON
    SET ANSI_NULL_DFLT_ON ON
    SET ANSI_PADDING ON
    SET ANSI_WARNINGS ON
    SET ANSI_NULLS ON
    SET ARITHABORT ON

    By hard coding these settings the query was able to render all of the data in the report. 

    Stuart

    • Marked as answer by Stegzy_M Friday, February 10, 2012 5:12 PM
    Friday, February 10, 2012 5:12 PM
  • Ah, almost certainly this was the SET CONCAT_NULL_YIELDS_NULL OFF, since that is definitely not the recommended setting. 

    The SQL Server 2005 documentation warned against using this and for 2008 R2 says:

    In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

    Just a heads up for the future.  It is better to handle this through COALESCE or ISNULL.

    FWIW,
    RLF

    Friday, February 10, 2012 7:33 PM