Search code examples
reporting-servicesexpressionssrs-tablixreportbuilder3.0no-data

Using Microsoft Report Builder: When there is no data, how can I add "No Data Available" message?

Can someone please help?

I am using Microsoft Report Builder, and would like to add a message stating "No Data Available" when no data is coming into the table.

Every time I use the "NoRowsMessage" in the properties or use the =CountRows() > 0 expression in one of the rows, it seems to not work. I keep getting an empty row or empty two rows! And message does not show up at all. Its like empty data is coming through which makes no sense. And if I make sure there is real data comes through it looks fine. Can someone tell me what I am doing wrong?

enter image description here enter image description here

Updated Info:

declare @MeetingSessionID varchar(500)
set @MeetingSessionID = '160165815460010151522-101523-4111510-14142220'

select distinct vl.originalChatSessionID
      ,vl.applicationUserID
      ,vl.pharmaID
      ,vl.UserName
      --,isnull(ph.FirstName,'') as 'First Name'
      --,isnull(ph.MiddleName,'') as 'Middle Name'
      --,isnull(ph.LastName,'') as 'Last Name'
      --,phm.CompanyName
      ,isnull(dr.Title,'') as 'Title'
      ,isnull(dr.FirstName,'') as 'First Name'
      ,isnull(dr.MiddleName, '') as 'Middle Name'
      ,isnull(dr.LastName, '') as 'Last Name'
      ,isnull(dr.Name, '') as 'Name'
      , null as 'Specialty'
from iclickphrDxvideolog vl
    --inner join iclickphrDxpharmacy ph
    --  on ph.pharmacyProfileID = vl.applicationUserID
    --inner join iclickphrDxpharmacy_Main phm
    --  on phm.pharmacyID = ph.PharmacyID
    inner join iclickphrDxdoctor dr
        on dr.DoctorID = vl.pharmaID


where vl.originalChatSessionID = @MeetingSessionID

So for example i just want to see middle name in the table and thats it...but the column is empty...im getting two empty rows back...i want it to say "No Data Available". I know that the chatSessionID is what is bringing back two rows...but I need to have it there some how....to get data out of the other tables.

Solution

  • I'm not sure I fully understand the situation regarding the lack of an actual table but you can mock up some data in your dataset query and use that.

    A simple version might look something like (change datatypes to suit your real data)

    DECLARE @iclickphrDxvideolog TABLE([Video Name] Varchar(100), [Video length] int, originalCharSessionID varchar(500), applicationUserID int, pharmaID int, UserName varchar(256))
INSERT INTO @iclickphrDxvideolog VALUES
('My first Video', 10, '160165815460010151522-101523-4111510-14142220', 1234, 1, 'Bob'),
('My second Video', 20, '160165815460010151522-101523-4111510-14142220', 3456, 1, 'Dave')

declare @MeetingSessionID varchar(500)
set @MeetingSessionID = '160165815460010151522-101523-4111510-14142220'

select distinct vl.originalChatSessionID
      ,vl.applicationUserID
      ,vl.pharmaID
      ,vl.UserName
      ,isnull(dr.Title,'') as 'Title'
      ,isnull(dr.FirstName,'') as 'First Name'
      ,isnull(dr.MiddleName, '') as 'Middle Name'
      ,isnull(dr.LastName, '') as 'Last Name'
      ,isnull(dr.Name, '') as 'Name'
      , null as 'Specialty'
from @iclickphrDxvideolog vl -- <= swapped out for table variable for testing
    inner join iclickphrDxdoctor dr
        on dr.DoctorID = vl.pharmaID
where vl.originalChatSessionID = @MeetingSessionID

    If you test with a session id other than '160165815460010151522-101523-4111510-14142220', you should get 0 rows returned.