CODEWITHSUNDEEP
sql-serverreporting-servicesnullssrs-2008subreport
Elizabeth
Elizabeth

Reputation: 765

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.

Upvotes: 4

Views: 1008

Answers (3)

bergerb
bergerb

Reputation: 116

Had that happen also with a Sub-Sub-Report. Once there are 0 Lines in the Dataset, the whole Sub-Sub-Report is not rendered. I love SSRS for those easy to find errors... I've just added the following to the original query

[..]Original SQL-Query[..]
UNION ALL
SELECT null,null,null,null,...

Upvotes: 0

Dhana
Dhana

Reputation: 1658

Check for Row Exists 

    IF NOT EXISTS(SELECT 1 FROM table)
     BEGIN
          SELECT Column from table
     END        
  ELSE
     BEGIN
          SELECT 'No records' as Column from table
     END

Add a condition for your result Table from SSRS report

Tablix Properties > Visibility > Show or Hide Based on Condition

Add a TextBox with text 'No records found' and add a condition to show based on your Dataset result.

Upvotes: 1

Russell Fox
Russell Fox

Reputation: 5435

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'

Upvotes: 1

Related Questions