SSRS report not displaying data

Phil Murray picture Phil Murray · Jul 6, 2012 · Viewed 69.1k times · Source

I have just created an SQL Server 2005 SSRS report and the data is not being displayed in the Preview pane.

The dataset is correctly populated from a stored procedure against a String parameter. I can execute it in the Data pane. When running the report in the Preview pane the correct number of rows are displayed but the contents of the cells do not contain any data

Missing Data

The source dataset is based on a Stored Procedure with a passed in String parameter in SQL Server 2005 that return the contents of a temp table. The dataset then maps the fields to locals. I can execute this correctly in teh Data view.

Stored Procedure

ALTER PROCEDURE spWebReportStage25BuildReview
    @BuildNumber as nvarchar(50)

Temp table schema

CREATE TABLE #tmpModelZones
(
    BuildID bigint NOT NULL,
    BuildNo nvarchar(50) NOT NULL,
    ModelID int NOT NULL,
    ModelName nvarchar(50) NOT NULL,
    ZoneID int NOT NULL,
    ZoneName nvarchar(50) NOT NULL,
    SortOrder int NOT NULL,
    Created bit DEFAULT 0 NOT NULL,
    Closed bit DEFAULT 0 NOT NULL,
    PRIMARY KEY (BuildID, ZoneID)
)

SSRS Dataset

enter image description here

SSRS Dataset mapping

enter image description here

SSRS Dataset Parameter enter image description here

Executing Dataset with parameter

enter image description here

There are no additional grouping, filters or aggregation on the displayed table. It is simply a flat table

Answer

Jeroen picture Jeroen · Jul 6, 2012

Never seen this before. However, SSRS can be a bit "off" at times, so here's a list of things to try. I'm afraid most of these are of the type "Have you tried turning it off and on again?".

  • Delete the .data files associated with the report.
  • Duplicate/back-up the report, and try to add the dataset in a fresh, basic tablix to see if that does show data.
  • Check the hidden property. Don't forget that (for some stupid reason) it's not (like in any other sane product) a Visible Y/N field, but a Hidden Y/N field.
  • Double check font color and size, etc.
  • Run the report on your reportserver (as opposed to the preview) to check if that does work.
  • Use some temporary text boxes to show the actual values of your parameters, to check if they are exactly the same as when you test-run the data set.
  • Check the code-behind (xml in the rdl) for unexpected filters, hidden property, expressions, etc. in the tablix. Even if you can't quite "read" the RDL, with syntax highlighting you should be able to skim it and extract a lot of info on this.
  • Check the ExecutionLog2 and other logging stuff to see how many rows are being returned in report runs.

In addition, it would help if you update/edit your question with some more info:

  • What kind of groupings does the tablix have?
  • What are the filters on the tablix, row groups, and column groups?
  • What is the general structure of the dataset, and it's results?
  • How are the parameters structured and used?