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

IF EXISTS(
SELECT 'x'
from dbo.Table1
where FileNumber = @FileNumber
)
BEGIN

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

END
ELSE
BEGIN

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

END

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.

Advertisements
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:

WordPress.com Logo

You are commenting using your WordPress.com 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