ssrs 2012 data driven dubscription with no data

• wendy elizabeth

  SSCoach

  Points: 16915

  More actions

  October 24, 2016 at 4:36 pm

  #331226

  In an ssrs 2012 data driven subscription I have setup on a test area, the report is generating correctly. However part of the request is not to send out email messages if there is no data located.

  Here is the sql that is used to determine if rows are generated:

  SELECT calendar.number , school.name, CustomStudent.[personID] ,count(*) AS [DUP COUNT]

  FROM [test].[dbo].[CustomStudent] CustomStudent WITH (NOLOCK)

  JOIN test.DBO.CampusDictionary CampusDictionary WITH (NOLOCK)

  ON CustomStudent.attributeid = CampusDictionary.attributeID AND

  CustomStudent.value =CampusDictionary.code

  JOIN [test].[dbo].[SchoolYear] SchoolYear WITH (NOLOCK)

  ON SchoolYear.label=CampusDictionary.name

  JOIN test.DBO.Enrollment Enrollment WITH (NOLOCK) on Enrollment.personID = CustomStudent.personID

  JOIN test.dbo.Person Person WITH (NOLOCK) on Person.personID = Enrollment.personID

  JOIN test.dbo.[Identity] [Identity] WITH (NOLOCK) on [Identity].[identityID] = person.currentIdentityID

  JOIN test.DBO.Calendar Calendar WITH (NOLOCK) ON Calendar.calendarID = Enrollment.calendarID

  JOIN test.DBO.School School WITH (NOLOCK) ON School.schoolID =Calendar.schoolID

  JOIN Campustest.dbo.AtnLtrASCH AtnLtrASCH WITH (NOLOCK) on AtnLtrASCH.SCHOOLNUM=calendar.number

  LEFT JOIN test.DBO.Contact Contact WITH (NOLOCK) on Contact.personID=AtnLtrASCH.SPA_PERSONID

  WHERE CustomStudent.attributeID = 2775 -- CAIP School year value

  AND SchoolYear.active = 1

  AND Enrollment.active = 1

  AND Enrollment.serviceType = 'P'

  AND (Enrollment.enddate is NULL or Enrollment.enddate > getdate())

  GROUP BY calendar.number,school.name, CustomStudent.[personID]

  HAVING count(*) > 1

  Thus can you tell me what I need to do make certain no email messages are sent out in this situation?