SQLTeam.com | Weblogs | Forums

Ssrs data driven subscription with no data


#1

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?