[SOLVED] SSRS Nested Report Not Coming Through if Dataset is Empty

SSRS Nested Report Not Coming Through if Dataset is Empty

I'm working with a pre-existing SSRS report that has a nested sub report.

The nested sub report doesn't always return data. (and this is correct). If the data set is empty nothing comes through no report or column headers no values of any kind no logos. Nothing. However, that is not what we want. If the data set is empty the report should return the headers/logo's and column headings with a string that states there is no data for this data range.

There are no filters/visibility settings preventing it from coming through. Please advise.

Solution

You can insert a "dummy" record if there's no data. Say your current query is:

SELECT ID
    , [Name]
    , [FavoriteFruit]
FROM yourTable
WHERE [SomeField] = 'hello'

You can check for values and return a dummy record like:

IF EXISTS(
    SELECT 1
    FROM yourTable
    WHERE [SomeField] = 'hello'
    )
    SELECT ID
        , [Name]
        , [FavoriteFruit]
    FROM yourTable
    WHERE [SomeField] = 'hello'
ELSE
    SELECT ID = 0
        , [Name] = 'No data'