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.
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.