Return text if tablix results are empty

Question

I have a tablix that is linked to DataSet1.

DataSet1 uses the following TSQL code

select ir.SourceRef as Account_Ref,
       rab.BalanceFromDate,
       rab.ClosingBalance Current_Balance,
       ra.Account_ID as rserial,
       ra.Current_Balance as Current_Balance


from db1..RentAccountBalance rab
left join db1..ImportReference ir on ir.EntityID = rab.AccountId and ir.EntityType='XXXX.XXX.X.XX'
left join db2..RentAccounts ra on convert(varchar(50),ra.Account_ID) = ir.SourceRef

where ir.SourceRef = '12857'
order by rab.AccountBalanceId

As I know that there is no ir.SourceRef that is equal to 12857, the result set is blank. Therefore, my tablix comes back just blank.Is there a way that if no results are returned that a text of say "All Accounts are OK." be displayed by the report instead?

Hope that's clear?

Thanks

Possible duplicate of [How do I display 'No data available.' when there are no rows to show on the report?](http://stackoverflow.com/questions/10146944/how-do-i-display-no-data-available-when-there-are-no-rows-to-show-on-the-repo) — Oceans, Oct 23 '15 at 12:19

score 0 · Answer 1 · answered Oct 23 '15 at 08:35

0

You can try expression like:

=IIF(IsNothing(Fields!FieldName.Value, "Accounts are OK", Fields!FieldName.Value))

Or if you want to check if there is no data at all, you can try to throw an error in TSQL in following:

--add this line to the end of query:
IF @@ROWCOUNT = 0 RAISERROR('Accounts are OK', 16, 1)

answered Oct 23 '15 at 08:35

Stanislovas Kalašnikovas

9,203
4
32
59

Hi, Thanks for the response, but where would I I place the expression in SSRS? – ikilledbill Oct 23 '15 at 08:51

score 0 · Answer 2 · answered Oct 23 '15 at 08:59

If you use a Stored Procedure you can then insert your Select statement data into a table variable before returning it. From this you can perform a check on its contents before it is returned to the report.

For example if you populate a table of data you wish to return as follows

INSERT INTO @ReturnTable (Account_Ref, ...)
SELECT  ir.SourceRef, ...

You can then query it's contents by using a command such as

IF (SELECT COUNT(*) FROM @ReturnTable) = 0
BEGIN
    INSERT INTO @ReturnTable (Account_Ref, ...)
    SELECT 'All Accounts are OK', ...
END

You can then perform a check within the report to see if the Account_Ref is 'All Accounts are OK', and if so display the report appropriately. You can even set the entire report's contents inside a rectangle with the visibility set to the result of

=iif(First(Fields!Account_Ref.Value) = "All Accounts are OK", false, true)

You can layer another object (an information message perhaps) on top of this with the inverse of this visibility set.

Thanks for the response. This seems a bit complex to me as I have never used Stored Procedures. — ikilledbill, Oct 23 '15 at 09:47

Return text if tablix results are empty

2 Answers2