SSRS subreport not refreshing

I wanted to display some static fields in a subreport, particularly when the DataSet did not return any records, via the parameter that was passed in.    Naively I thought this would work, and when it didn’t, I thought that it was a refresh problem. I tried everything to get my Preview tab to refresh with my latest changes.

This led to the important discovery, that if no data is returned from the data set of a subreport, it will not show anything. My solution was to edit the stored procedure in the DataSet to return a bunch of NULLs.

For Example:

DECLARE @FileNumber int
SET @FileNumber = 11087

from dbo.Table1
where FileNumber = @FileNumber

select FileNumber, Column2, Column3, Column4
from Table1
WHERE FileNumber = @FileNumber


select @FileNumber as FileNumber,
NULL as Column2,
NULL as Column3,
NULL as Column4


It works, and seems like the ideal thing to do under the circumstances.  I wouldn’t want to rely on this all the time, because it’s more inefficient and less readable.  Just use this for special situations.

This entry was posted in Business Intelligence and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s