Why would an SSRS report produce different data when run from SQL Server Reporting Services than when run using the Preview tab?

I’ve got a report running the data I want – from the Preview tab, that is, or when run using F5 in VS 2010. But when I upload the report (.rdl file) to SQL Server Reporting Services, and run the updated report from there, it still shows the old bunch of (restricted) data.

Am I missing a step – does something need to be done other than uploading the .rdl file from the project? It did tell me the DataSource was wrong, but once I specified the right one, it ran without a hitch (except for the missing data).

  • How to Update or Insert a record using SQL Merge
  • Any options to connect from Azure SQL Server database to an Azure Data Warehouse instance
  • CodeIgniter MSSQL connection
  • How can I display a SQL Server XML column as hyperlink in SSRS?
  • Why does SQL Server use a non-clustered index over the clustered PK in a “select *” operation?
  • Selecting BLOB column from Oracle database to SQL Server database over linked server
  • I made sure that the updated report was truly being used by SQL Server Reporting Services by adding a bogus addition to a label, and sure enough, I do see that when I run the report, so the updated report is getting there.

    I also verified that both the design time and runtime reports (same file, but copied to a different location) use the same data source: the SharedDataSource reference in the project, which I see when I select View > Report Data > Data Sources is the same being used In SQL Server Reporting Services, where I set the data source by selecting the yellow right down-arrow on the report name, then “Manage” > “Data Sources” > “A shared data source” > Browse > Home > Data Sources > and selected the shared data source with the same name as the one seen in the project. Finally, I selected OK > Apply in SQL Server Reporting Services, but only a subset of the data is generated.

    Why would that be? What can I do to retrieve all the data?


    To clarify what happens, in answer to yelxe’s comment:

    When I delete the previous version of the report in SSRS and upload a “new” one (.rdl file – the new version of the report just deleted), and then click the report link to try to run it, I get, “The report server cannot process the report or shared dataset. The shared data source ‘CPSData’ for the report server or SharePoint site is not valid. Browse to the server or site and select a shared data source. (rsInvalidDataSourceReference)”

    So I click the yellow down-arrow to the right of the report and select Manage.

    From there, I select Data Sources > A shared data source > Browse > Home/Data Sources/CPSData (“CPSData” is the same Shared datasource selected in the project) > OK > Apply, and then run the report.

    This is where I do that:

    enter image description here

    It runs, but the data returned to the report is a subset of what I get when I run it from the IDE (VS 2010).

    UPDATE 2

    As for the data that is there when the report is run from SSRS, it is identical – IOW, the rows that are common to both the superset and the subset of data are identical; it’s just that many rows are missing from the subset. And there doesn’t appear to be anything “odd” about the data that exists in the superset – it’s not 0 or negative or anything special or striking.

    UPDATE 3

    To reply to Eric’s comment in more detail, the report’s “Subscriptions”, “Cache Refresh Options” and “Report History” pages shows “There are no items to show in this view. Click Help for more information about this page.

    The report’s Processing Options page shows

    enter image description here

    …and the report’s “Snapshot Options” page shows

    enter image description here

    UPDATE 4

    I noticed that the name of my Data Source was the same name as the name of my primary Dataset (they were both “CPSData”). The dataset name is just a label (it could be named “duckbilledplatypus” or whatever) but still I wondered if this confusing/misleading name for the dataset might have been problematic. So, I changed the name of the dataset to something more reasonable (I renamed it “VPM_V_RockBottom” as the Stored Procedure is named “sp_ViewPriceMatrix_Variance_RockBottom”).

    This made no difference, though. I replaced the new version of the report (.rdl file) in SSRS, re-ran the report, and still (don’t) see the missing data.

    Using the same parameters (date range and Unit name), this is what I see when run from my project’s Preview tab:

    enter image description here

    …and here it is when run in SSRS:

    enter image description here

    So the “Hass”* Avocados show 18 members when run from VS 2010 (as it should be), and only 3 when run from SSRS. In both cases/places, “Asparagus Standard 11/1#” show 18 members, as they should.

    Why would there be a difference, and how can I rectify this anomaly?

    • (sic – should be “Haas”, not “Hass” (the German word for “hate”))

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.