sqlsql-serverreporting-servicesreportingservices-2005

SSRS report not displaying data

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

Solution

  • When using temporary tables, SSRS fails to get the metadata. So there are basically 2 ways of letting SSRS know the column names:

    1. Add SET FMTONLY ON. This will retrieve the metadata but it won't display the data.

    2. Go to the DATA tab and click on the Generic query designer and click refresh fields. This will prompt a dialogue box for specifying the parameter value. When we run the query in query designer SSRS gets the schema and the data from the stored procedure. Now the data will be available in preview pane.